Обсуждение: could not read block 0 in file : read only 0 of 8192 bytes when doingnasty on immutable index function
I've found this strange (to me) behavior when doing nasty things with indexes and immutable functions: create table t( pk serial, t text ); insert into t( t ) values( 'hello' ), ('world'); create or replace function f_fake( i int ) returns text as $body$ declare v_t text; begin select t into strict v_t from t where pk = i limit 1; return v_t; exception when no_data_found then return 'a'; end $body$ language plpgsql immutable; Of course, f_fake is not immutable. When on 10.4 or 11 beta 1 I try to create an index on this nasty crappy function: create index idx_fake on t ( f_fake( pk ) ); ERROR: could not read block 0 in file "base/16392/16444": read only 0 of 8192 bytes CONTEXT: SQL statement "select t from t where pk = i limit 1" PL/pgSQL function f_fake(integer) line 5 at SQL statement that is somehow correct (because the function cannot be used to build an index), but then it goes worst: elect * from t; ERROR: could not open relation with OID 16444 If I then disconnect and reconnect I'm able to issue the select and get back the results. But if I issue a reindex I got the same error and the table "becames unreadable" for the whole session. On 10.3 the table is never locked for the session, that is I can create the index, I can query the table and get the results, but I cannot reindex. However, even after a reindex, it does allow me to select data from the table. So my question is: why this behavior in later PostgreSQL?
On Wed, Jun 27, 2018 at 11:35 AM, Luca Ferrari <fluca1978@gmail.com> wrote: > If I then disconnect and reconnect I'm able to issue the select and > get back the results. But if I issue a reindex I got the same error > and the table "becames unreadable" for the whole session. > On 10.3 the table is never locked for the session, that is I can > create the index, I can query the table and get the results, but I > cannot reindex. However, even after a reindex, it does allow me to > select data from the table. > > So my question is: why this behavior in later PostgreSQL? It might have something to do with the changes to parallel CREATE INDEX. It changed how we tracked whether or not an index could be used because it was currently undergoing reindexing. This is supposed to make no difference at all, but there was one bug that could cause us to consider an index irrevocably unusable. Do you find that the issue goes away if you set max_parallel_maintenance_workers=0 on v11/master? -- Peter Geoghegan
Hi, On 2018-06-27 20:35:16 +0200, Luca Ferrari wrote: > I've found this strange (to me) behavior when doing nasty things with > indexes and immutable functions: > > create table t( pk serial, t text ); > insert into t( t ) values( 'hello' ), ('world'); > create or replace function f_fake( i int ) > returns text > as $body$ > declare > v_t text; > begin > select t into strict v_t > from t where pk = i limit 1; > return v_t; > exception > when no_data_found then return 'a'; > end > $body$ > language plpgsql immutable; > > Of course, f_fake is not immutable. > When on 10.4 or 11 beta 1 I try to create an index on this nasty > crappy function: > > create index idx_fake on t ( f_fake( pk ) ); > > ERROR: could not read block 0 in file "base/16392/16444": read only 0 > of 8192 bytes > CONTEXT: SQL statement "select t from t where pk = > i limit 1" > PL/pgSQL function f_fake(integer) line 5 at SQL statement > that is somehow correct (because the function cannot be used to build > an index), but then it goes worst: > > elect * from t; > ERROR: could not open relation with OID 16444 That certainly isn't behaviour I'd expect. Doing nasty stuff inside an immutable function will have bad consequences, but the permanent failure shouldn't be there. But I also can't reproduce it either on 10.4, 10-current, master. Did you build from source? Packages? Any extensions? Is there anything missing from the above instruction to reproduce this? Greetings, Andres Freund
On Wed, Jun 27, 2018 at 10:44 PM Andres Freund <andres@anarazel.de> wrote: > But I also can't reproduce it either on 10.4, 10-current, master. Did > you build from source? Packages? Any extensions? Is there anything > missing from the above instruction to reproduce this? Somehow today I cannot reproduce it by myself, I must have missed something since I cannot get locked out from the table. However I've tested that on 10.3 (one I've at the moment): 1) create table, insert, create index, reindex causes the problem but do not locks the further select 2) create table, create index, insert, reindex does not show the problem (i.e., no comlain at all) while on the following version both 1 and 2 shows the reading problem once the reindex is issued (but allows further selects): testdb=> select version(); version --------------------------------------------------------------------------------------------------------------------------------------------------------- PostgreSQL 11beta1 on x86_64-unknown-freebsd11.1, compiled by FreeBSD clang version 4.0.0 (tags/RELEASE_400/final 297347) (based on LLVM 4.0.0), 64-bit It seems not to depend on max_parallel_maintance_workers. Sorry, I cannot provide more help at the moment. I will try to reproduce it again. Luca
Got it: it happens if you drop and recreate the index. It shows up either setting max_parallel_maintanance_workers to zero or a greater value. testdb=> create table t( pk serial, t text ); CREATE TABLE testdb=> insert into t( t ) values( 'hello' ), ('world'); INSERT 0 2 testdb=> create or replace function f_fake( i int ) returns text as $body$ declare v_t text; begin select t into strict v_t from t where pk = i limit 1; return v_t; exception when no_data_found then return 'a'; end $body$ language plpgsql immutable; CREATE FUNCTION testdb=> create index idx_fake on t ( f_fake( pk ) ); CREATE INDEX testdb=> drop index idx_fake; DROP INDEX testdb=> create index idx_fake on t ( f_fake( pk ) ); 2018-06-28 10:23:18.275 CEST [892] ERROR: could not read block 0 in file "base/16392/16538": read only 0 of 8192 bytes 2018-06-28 10:23:18.275 CEST [892] CONTEXT: SQL statement "select t from t where pk = i limit 1" PL/pgSQL function f_fake(integer) line 5 at SQL statement 2018-06-28 10:23:18.275 CEST [892] STATEMENT: create index idx_fake on t ( f_fake( pk ) ); ERROR: could not read block 0 in file "base/16392/16538": read only 0 of 8192 bytes CONTEXT: SQL statement "select t from t where pk = i limit 1" PL/pgSQL function f_fake(integer) line 5 at SQL statement testdb=> select * from t; 2018-06-28 10:23:23.642 CEST [892] ERROR: could not open relation with OID 16538 2018-06-28 10:23:23.642 CEST [892] STATEMENT: select * from t; ERROR: could not open relation with OID 16538 This has been tested on testdb=> select version(); version --------------------------------------------------------------------------------------------------------------------------------------------------------- PostgreSQL 11beta1 on x86_64-unknown-freebsd11.1, compiled by FreeBSD clang version 4.0.0 (tags/RELEASE_400/final 297347) (based on LLVM 4.0.0), 64-bit testdb=> show max_parallel_maintenance_workers ; max_parallel_maintenance_workers ---------------------------------- 2