Hi guys,
I think I found a bug in sql (not plpgsql) functions.
This is the error I’m getting:
ERROR: XX000: could not open relation with OID 13053550
CONTEXT: SQL function "fail" statement 3
LOCATION: relation_open, heapam.c:1130
It happens when I try to recreate an index and use it afterwards (both within the function).
My postgres version:
PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.2 20140120 (Red Hat 4.8.2-16), 64-bit
Here is some sql to reproduce it:
BEGIN;
CREATE TABLE test_1 (
test_id BIGINT PRIMARY KEY,
other_id BIGINT NOT NULL
);
CREATE INDEX test_1_other_idx ON test_1(other_id);
— Make sure query planner uses the index scan
SET enable_seqscan=off;
CREATE OR REPLACE FUNCTION fail() RETURNS bigint
LANGUAGE sql AS
$$
-- Re-create the index
DROP INDEX IF EXISTS test_1_other_idx;
CREATE INDEX test_1_other_idx ON test_1(other_id);
-- Fail!
SELECT test_id FROM test_1 WHERE other_id = 1000;
$$;
SELECT fail();
ROLLBACK;
If you modify this function to use plpgsql, it won’t fail anymore:
CREATE OR REPLACE FUNCTION ok() RETURNS bigint
LANGUAGE plpgsql AS
$$
BEGIN
-- Re-create the index
DROP INDEX IF EXISTS test_1_other_idx;
CREATE INDEX test_1_other_idx ON test_1(other_id);
RETURN (SELECT test_id FROM test_1 WHERE other_id = 1);
END
$$;
SELECT ok();
It also works fine when you paste contents of fail() function into psql session.