Dropping Postgres schema inside plpgsql function after RETURN QUERY EXECUTE on a table within that schema

Question

I have a plpgsql function that creates a series of UNLOGGED tables in a schema. The function needs to drop this temporary schema after returning results from one of these tables

        RETURN QUERY EXECUTE 'SELECT * FROM scratch_schema.table'

        EXECUTE 'DROP SCHEMA IF EXISTS ' || scratch_schema || ' CASCADE';

        EXCEPTION WHEN others THEN
            EXECUTE 'DROP SCHEMA IF EXISTS ' || scratch_schema || ' CASCADE';
            RAISE;

        RETURN;

However when the function attempts to drop the schema I get the following message:

[2021-01-19 15:34:04] [XX000] ERROR: could not open relation with OID 124466

From what I can discern, Postgres is dropping the schema before the data is read from table. This doesn't seem like it should be possible, but I was able to confirm that if I remove the DROP SCHEMA ... line the function is able to execute and return correctly. Is there any mechanism to ensure the schema is properly dropped only after the results are sent?

Please provide a complete (minimal) function and your version of Postgres. — Erwin Brandstetter
– Erwin Brandstetter, Commented Jan 19, 2021 at 23:09

Erwin Brandstetter · Accepted Answer · 2021-01-19 22:53:11Z

1

Since you are dropping the tables anyway, I don't see the need for unlogged tables.

Use TEMPORARY tables instead of UNLOGGED. Those are dropped at the end of the session automatically. Or you can create them with ON COMMIT DROP to have them dropped at the end of the transaction. Cheaper, too.

answered Jan 19, 2021 at 22:53

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

THX1138 Over a year ago

I initially tried to use temporary tables, however I have some performance critical functions that require parallelism and INSERT operations into temporary tables disable parallel execution plans which result in massive performance loss.

Erwin Brandstetter Over a year ago

@THX1138: Hmm. Reading from a temp table cannot be parallelized. But there is no such restriction for the query behind an INSERT into a temp table that I know of.

Collectives™ on Stack Overflow

Dropping Postgres schema inside plpgsql function after RETURN QUERY EXECUTE on a table within that schema

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related