I have PL/PGSQL functions that require temporary tables for storing
intermediate query output.
I used to create a temporary table with a unique name in each function
call, but that led to "out of shared memory" errors and bloating of
system catalogs, because temp tables are only actually dropped at the
end of a transaction, as Tom pointed out.
So I tried creating non-temporary tables specifically for use in
functions, but the result was that concurrency was not possible as
each transaction using the function would retain a lock on the table.
I then considered creating temporary tables once per session or transaction.
Considering that a connection pool might be used, I had to make my
temp tables "ON COMMIT DROP".
I still have the following worries:
- not very friendly (must call a 'init' function at beginning of each
transaction)
- must wrap all queries in EXECUTE statements
- ON COMMIT DROP may not be portable to other DBMS (not too important here)
- may also lead to system catalog bloating over time.
Overall I find it rather troublesome to use temporary storage tables
in functions. Maybe I am missing something. Any tips?
