I know you guys love subject lines like this, but I have a humble
request. Would it be possible to have either a GUC setting or a grammar
change to allow TEMPORARY tables to be dropped at transaction commit? I
know the standard defines the lifetimes of temporary tables to be that
of the session. However, I have CORBA middleware which generates a
transient session object per client. The object connects to the database
at instantiation time and services requests by CORBA's remote method
invocation. Often, the methods invoked on the object cause the object to
create temporary tables. Each method invocation is a single transaction.
But the lifetime of a user's session can be quite long. Worse, CORBA
doesn't permit the application to detect when the client "disconnects" -
the object (and therefore the database connection) remains unless told
explicitly to die. I currently have an evictor pattern remove objects
upon which no method invocation has taken place over a given time. But
in the meantime, dozens of temporary tables have built up. The idea kind
of falls along the same lines as the SET discussion previously. As a
test, it took me about 8 lines of code to implement the change. Of
course, it was a hack, but it worked nicely.
Would a patch to the grammar be accepted? Along the lines of:
CREATE TEMPORARY TABLE
...
ON COMMIT DROP;
pseudo-compatible with the SQL-standard of:
ON COMMIT { DELETE | PRESERVE } ROWS;
so one day PostgreSQL's grammar would look like:
...
ON COMMIT { DROP | { DELETE | PRESERVE } ROWS };
I suppose I could just change the code to query the catalogue for those
temporary tables created during the transaction and issue DROP TABLEs by
hand. But I thought it might be an idea of value to others.
Mike Mascari
mascarm@mascari.com