Welcome to ZaiZheLe Developer Zone-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.7k views
in Technique[技术] by (71.8m points)

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

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?


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to ZaiZheLe Developer Zone-Open, Learning and Share
...