BUG #15452: 10.5 and reproducible "ERROR: could not read block 0 in file..." when creating functional index

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: nbogdanov(at)lanit(dot)ru
Subject: BUG #15452: 10.5 and reproducible "ERROR: could not read block 0 in file..." when creating functional index
Date: 2018-10-22 19:41:30
Message-ID: 15452-ec5381239111a6da@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 15452
Logged by: Nikita Bogdanov
Email address: nbogdanov(at)lanit(dot)ru
PostgreSQL version: 10.5
Operating system: CentOS release 6.6 (Final)
Description:

Original bug in 9.4
https://www.postgresql.org/message-id/87tx0dc80x.fsf%40news-spur.riddles.org.uk
Reproduces on PostgreSQL 10.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC)
4.4.7 20120313 (Red Hat 4.4.7-23), 64-bit

Minimal test case:
-- 1) table must have nonzero live rows
-- 2) table must have nonzero indexes
-- 3) explicit direction not to return any rows doesn't matter
-- script
drop table if exists ftst;
create table ftst (id integer);
drop function if exists tst_actual_v(int);
-- sql
create or replace function tst_actual_v(i integer) returns int
language sql immutable
as $f$
select id from ftst where 1 = 0 limit 0;
$f$;
-- plpgsql
/*
create or replace function tst_actual_v(i integer) returns int
language plpgsql immutable
as $f$
declare res int;
begin
select id into res from ftst where 1 = 0 limit 0;
return res;
end;
$f$;
*/
insert into ftst select 1;
create index ftst_id on ftst (id);
create index ftst_trouble on ftst (tst_actual_v(id));
-- ERROR: could not read block 0 in file
"pg_tblspc/16437/PG_10_201707211/16512/30253417": read only 0 of 8192
bytes

Consequencies
1) creates zero-size files in the filesystem on every call which stay there
forever
2) vacuum full or reindex cannot be made

Expected behaviour
1) zero-size files deleted on error
2) no need to use workarounds

Workarounds
1) create index concurrently
2) delete all indexes and than create this first

What do the docs say
https://www.postgresql.org/docs/10/static/sql-createindex.html
"Results of functions you are using to create index on must depend only on
their arguments."
In this case function is immutable, because it returns nothing regardless of
input values

P.S.
Vacuumdb stops on this error and there is no key for it to skip errors, so
one need to write custom scripts to vacuum DB with error skipping

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Amit Langote 2018-10-23 02:00:56 Re: BUG #15448: server process (PID 22656) was terminated by exception 0xC0000005
Previous Message Alvaro Herrera 2018-10-22 19:12:08 Re: BUG #15451: Error adding foriegn key constraint