The following bug has been logged on the website:
Bug reference: 15452
Logged by: Nikita Bogdanov
Email address: nbogdanov@lanit.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