Re: RAM-only temporary tables

Heikki Linnakangas wrote:
> Tom Lane wrote:
>> Try several thousand temp tables within one transaction.
>
> I ran into an interesting problem while doing that. I created a SQL
> script with 10000 CREATE TEMPORARY TABLE statements. After testing with
> that a few times, I got this:
>
> WARNING: out of shared memory
> ERROR: out of shared memory
> HINT: You might need to increase max_locks_per_transaction.
>
> Not that surprising, but when I then just tried to run a single CREATE
> TEMPORARY TABLE in a new psql session, I got the same error. I then
> stopped and started postmaster, and I still get the same error! Testing
> with trace_locks=on, looks like the table creation takes a lock on all
> the temp tables that are already gone, and runs out of memory doing that.

What's happening is that there is simply so many temporary tables in
pg_class that when the new backend tries to clear them with
RemoveTempRelations, it runs out of lock space.

One idea would be to modify RemoveTempRelations to not acquire locks on
the temp objects, but that's pretty ugly and I'm not sure how it would
interact with concurrent DROP TYPE CASCADE or similar. Any better ideas?

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com