public inbox for [email protected]  

help / color / mirror / Atom feed

From: Achilleas Mantzios - cloud <[email protected]>
To: [email protected]
Subject: Re: ERROR: could not read block 0 in file when creating an index out of a function
Date: Wed, 12 Mar 2025 15:56:39 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAKoxK+5ek4R-6kxzJ1TALf_Dxe-dM-xjTLkWp=9_BTP7Gx8Jrg@mail.gmail.com>
References: <CAKoxK+7G+=nEmJdV9k0r_FNNJ5ZCo2we0H8uNNzuCjnbh462mg@mail.gmail.com>
	<CAKNkYnyPs8sNwb7Xq3sDSYRgNyHgQNibJjcyrkv596uCOLGXxQ@mail.gmail.com>
	<CAKoxK+5ek4R-6kxzJ1TALf_Dxe-dM-xjTLkWp=9_BTP7Gx8Jrg@mail.gmail.com>


On 3/12/25 14:31, Luca Ferrari wrote:
> On Wed, Mar 12, 2025 at 12:54 PM Artur Zakirov<[email protected]> wrote:
>> I can reproduce this with the table `t` on PG 15.10.
> I didn't mention I'm running 16.6, but I'm pretty sure it is
> reproducible on other versions too.
>
>> In your case `base/357283/365810` file is a new index file. For some
>> reason Postgres tries to read the new index. I suppose this is because
>> during reading the table `t` within the function `f_t` it tries to
>> access the new index.
> Yeah, even if it is not clear to me why it is trying to read the index
> that is under creation (i.e., not usable yet).
>
>
>> According to the documentation, IMMUTABLE functions should not only
>> modify the database, but also return the same results given the same
>> arguments forever, which might not be true when you query a table
>> within such a function. Such a function should be defined as STABLE or
>> VOLATILE.
> As I stated, this example is controversial, and as the documentation
> states, the IMMUTABLE set of functions should not perform database
> lookups, as in my example.
> However, the error message is quite obscure to me, and reminds me a
> disk corruption rather a stability/function/lookup problem.

Test on 17.0:

It seems inconsistent to me :

creating a fresh ttt (no PK no constraint) :

amantzio@[local]/test=# CREATE INDEX IF NOT EXISTS idx_t ON ttt( f_ttt( 
pk ) );
CREATE INDEX
amantzio@[local]/test=# drop index idx_t ;
DROP INDEX
amantzio@[local]/test=# ALTER TABLE ttt ALTER pk SET NOT NULL ;
ALTER TABLE
amantzio@[local]/test=# CREATE INDEX IF NOT EXISTS idx_t ON ttt( f_ttt( 
pk ) );
ERROR:  could not read blocks 0..0 in file "base/17753/596558047": read 
only 0 of 8192 bytes
CONTEXT:  SQL statement "SELECT pk
                  FROM public.ttt
WHERE pk = i"
PL/pgSQL function f_ttt(integer) line 5 at SQL statement
amantzio@[local]/test=# ALTER TABLE ttt ALTER pk DROP NOT NULL ;
ALTER TABLE
amantzio@[local]/test=# CREATE INDEX IF NOT EXISTS idx_t ON ttt( f_ttt( 
pk ) );
ERROR:  could not read blocks 0..0 in file "base/17753/596558048": read 
only 0 of 8192 bytes
CONTEXT:  SQL statement "SELECT pk
                  FROM public.ttt
WHERE pk = i"
PL/pgSQL function f_ttt(integer) line 5 at SQL statement
amantzio@[local]/test=#
\q

So yep you are definitely right as far as the error message is concerned 
plus the inconsistent pattern shown above.

>
> Luca
>
>

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: ERROR: could not read block 0 in file when creating an index out of a function
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox