Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tsMZk-001BEU-U2 for pgsql-general@arkaria.postgresql.org; Wed, 12 Mar 2025 13:56:49 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tsMZj-005deB-4C for pgsql-general@arkaria.postgresql.org; Wed, 12 Mar 2025 13:56:47 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tsMZi-005dXN-Hu for pgsql-general@lists.postgresql.org; Wed, 12 Mar 2025 13:56:46 +0000 Received: from cloud.gatewaynet.com ([185.90.37.94]) by makus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1tsMZe-002QtF-0Z for pgsql-general@lists.postgresql.org; Wed, 12 Mar 2025 13:56:44 +0000 Content-Type: multipart/alternative; boundary="------------3YJOlwaHkXcZZDq9pujldyAr" Message-ID: <6485cde9-aff7-468a-8d5f-f57918f46ebc@cloud.gatewaynet.com> Date: Wed, 12 Mar 2025 15:56:39 +0200 MIME-Version: 1.0 Subject: Re: ERROR: could not read block 0 in file when creating an index out of a function To: pgsql-general@lists.postgresql.org References: Content-Language: en-US From: Achilleas Mantzios - cloud In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------3YJOlwaHkXcZZDq9pujldyAr Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 3/12/25 14:31, Luca Ferrari wrote: > On Wed, Mar 12, 2025 at 12:54 PM Artur Zakirov 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 > > --------------3YJOlwaHkXcZZDq9pujldyAr Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit


On 3/12/25 14:31, Luca Ferrari wrote:
On Wed, Mar 12, 2025 at 12:54 PM Artur Zakirov <zaartur@gmail.com> 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
--------------3YJOlwaHkXcZZDq9pujldyAr--