Re: [BUGS] "could not open relation with OID XXX" when using recreated index in sql function

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