Обсуждение: [BUGS] "could not open relation with OID XXX" when using recreated index insql function
[BUGS] "could not open relation with OID XXX" when using recreated index insql function
От
Krystian Szladewski
Дата:
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.
Best Regards,
Krystian Szladewski
Krystian Szladewski <krystian.szladewski@adspert.de> writes: > 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; > $$; Yeah, this is unsurprising per the NOTE here: https://www.postgresql.org/docs/current/static/xfunc-sql.html Note: The entire body of a SQL function is parsed before any of it is executed. While a SQL function can containcommands that alter the system catalogs (e.g., CREATE TABLE), the effects of such commands will not bevisible during parse analysis of later commands in the function. Thus, for example, CREATE TABLE foo (...);INSERT INTO foo VALUES(...); will not work as desired if packaged up into a single SQL function, since foo won'texist yet when the INSERT command is parsed. It's recommended to use PL/PgSQL instead of a SQL functionin this type of situation. (I see that this NOTE only talks about parse analysis, but actually the planning is done in one fell swoop as well, which is what leads to your problem.) At some point somebody will probably rewrite the SQL function executor to fix this, and get rid of some of its other unpleasant properties like not using the plancache; but it hasn't been high priority. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs