Re: RAM-only temporary tables

Tom Lane wrote:
> Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:
>> Tom Lane wrote:
>>> Heikki, would it be reasonable to fix things so that a nonexistent FSM
>>> fork is semantically the same as an empty one, and not create FSM until
>>> there's actually something to put in it?
>
>> Possibly, but I'd like to understand what exactly the problem is. I
>> tried running this:
>
>> CREATE TEMPORARY TABLE footemp (id int4);
>> DROP TABLE footemp;
>
>> with pgbench -f, but can't see any meaningful difference between 8.3 and
>> CVS HEAD. Both can do about 300 tpm, or 700-800 with fsync=off.
>
> 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.

I'll hunt that down, and try benchmarking the "thousands of temp tables
in one transaction" case again after that...

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