Обсуждение: 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