Thread: Possible feature

I was wondering if anyone on this list would either approve of, or
disapprove of, an extension to CREATE TEMPORARY TABLE. It would look
like this:

CREATE TEMPORARY TABLE
...
ON COMMIT DROP;

The ON COMMIT DROP would cause the temporary table to automatically be
dropped when the transaction in which it was created has committed. This
might aid those using middleware that would otherwise build up temporary
tables until the session ended to automatically drop them on transcation
commit instead. A few others on another list have suggested that they
might approve the idea, but I have to convince the maintainers to accept
a patch. SQL92 does not have an ON COMMIT DROP. However, they do have an
ON COMMIT { DELETE | PRESERVE } ROWS, so it would be a non-standard
feature.

What do people think?

Mike Mascari
mascarm@mascari.com

Mike Mascari dijo:

> I was wondering if anyone on this list would either approve of, or
> disapprove of, an extension to CREATE TEMPORARY TABLE. It would look
> like this:
>
> CREATE TEMPORARY TABLE
> ...
> ON COMMIT DROP;

I think it can prove useful sometimes.  It doesn't prevent the usual
behaviour, so why not?

(maybe it even appears in SQL2003 or sth)

--
Alvaro Herrera (<alvherre[a]atentus.com>)
"Hay dos momentos en la vida de un hombre en los que no debería
especular: cuando puede permitírselo y cuando no puede" (Mark Twain)

terry@greatgulfhomes.com wrote:
>
> I think that is a very good idea, and helps to keep housekeeping efforts
> to a minimum.
>
> One question, what would you do if there was no commit, just a rollback?
>
> Terry Fielder

The temporary table would get dropped as it does now.

Mike Mascari
mascarm@mascari.com