Search Postgresql Archives

Re: could not read block 0 in file : read only 0 of 8192 bytes when doing nasty on immutable index function

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux