Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

select version();

version

PostgreSQL 16.0 (Debian 16.0-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit

SELECT 1

-- Test index expression includes parallel unsafe function
CREATE FUNCTION para_unsafe_f() RETURNS int IMMUTABLE PARALLEL UNSAFE
AS $$
BEGIN   
    RETURN 0;
EXCEPTION WHEN OTHERS THEN
    RETURN 1;
END$$ LANGUAGE plpgsql;

CREATE FUNCTION

CREATE TABLE btree_para_bld(i int);
ALTER TABLE btree_para_bld SET (parallel_workers = 4);
SET max_parallel_maintenance_workers TO 4;
CREATE INDEX ON btree_para_bld(i) WHERE i > para_unsafe_f();

CREATE TABLE

ALTER TABLE

SET

ERROR:  cannot start subtransactions during a parallel operation
CONTEXT:  PL/pgSQL function para_unsafe_f() line 2 during statement block entry
parallel worker