Re: [HACKERS] Non-standard feature request

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(at)mascari(dot)com

Mike Mascari <mascarm(at)mascari(dot)com> writes:
> ... Would it be possible to have either a GUC setting or a grammar
> change to allow TEMPORARY tables to be dropped at transaction commit?

This seems like a not unreasonable idea; but the lack of other responses
suggests that the market for such a feature isn't there. Perhaps you
should try to drum up some interest on pgsql-general and/or pgsql-sql.

regards, tom lane

On Thu, 13 Jun 2002, Mike Mascari wrote:

>
> 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 think this is a pretty useful feature. Shouldn't require too much
work. A new relkind or a bool in TempTable and a little code in
AtEOXact_temp_relations() to heap_drop_with_catalog() the registered temp
table.

Anyone else keen for this feature?

Gavin

Tom Lane wrote:
> Mike Mascari <mascarm(at)mascari(dot)com> writes:
> > ... Would it be possible to have either a GUC setting or a grammar
> > change to allow TEMPORARY tables to be dropped at transaction commit?
>
> This seems like a not unreasonable idea; but the lack of other responses
> suggests that the market for such a feature isn't there. Perhaps you
> should try to drum up some interest on pgsql-general and/or pgsql-sql.

I was wondering if it made sense to remove temp tables on transaction
finish if the temp table was created in the transaction? That wouldn't
require any syntax change. Seems non-standard though, and I can imagine
a few cases where you wouldn't want it.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

Bruce Momjian wrote:
>
> Tom Lane wrote:
> > Mike Mascari <mascarm(at)mascari(dot)com> writes:
> > > ... Would it be possible to have either a GUC setting or a grammar
> > > change to allow TEMPORARY tables to be dropped at transaction commit?
> >
> > This seems like a not unreasonable idea; but the lack of other responses
> > suggests that the market for such a feature isn't there. Perhaps you
> > should try to drum up some interest on pgsql-general and/or pgsql-sql.
>
> I was wondering if it made sense to remove temp tables on transaction
> finish if the temp table was created in the transaction? That wouldn't
> require any syntax change. Seems non-standard though, and I can imagine
> a few cases where you wouldn't want it.

That is what I want to do, except by extending the grammar. I must admit
to actually being surprised that a TEMP table created inside a
transaction lived after the transaction completed. That's when I looked
at the standard and saw that PostgreSQL's implementation was correct. I
would think for most people session-long temp tables are more the
exception than the rule. But I guess SQL92 doesn't think so. Regardless,
a couple of other people have shown some interest in the idea. I'll post
it to general as well as Tom suggests...

Mike Mascari
mascarm(at)mascari(dot)com

On Fri, 14 Jun 2002, Mike Mascari wrote:

> That is what I want to do, except by extending the grammar. I must admit
> to actually being surprised that a TEMP table created inside a
> transaction lived after the transaction completed. That's when I looked
> at the standard and saw that PostgreSQL's implementation was correct. I
> would think for most people session-long temp tables are more the
> exception than the rule. But I guess SQL92 doesn't think so. Regardless,
> a couple of other people have shown some interest in the idea. I'll post
> it to general as well as Tom suggests...
>
Actually, we needed to use temp tables that live beyond the transaction,
because there are no session variables in postgres. So I did an
implementation that used temp tables instead.

Having the temp table not live for the life of the session would be a big
problem for me.

-rocco

Rocco Altier wrote:
>
> On Fri, 14 Jun 2002, Mike Mascari wrote:
>
> > That is what I want to do, except by extending the grammar. I must admit
> > to actually being surprised that a TEMP table created inside a
> > transaction lived after the transaction completed. That's when I looked
> > at the standard and saw that PostgreSQL's implementation was correct. I
> > would think for most people session-long temp tables are more the
> > exception than the rule. But I guess SQL92 doesn't think so. Regardless,
> > a couple of other people have shown some interest in the idea. I'll post
> > it to general as well as Tom suggests...
> >
> Actually, we needed to use temp tables that live beyond the transaction,
> because there are no session variables in postgres. So I did an
> implementation that used temp tables instead.
>
> Having the temp table not live for the life of the session would be a big
> problem for me.

Sure, which is why I'm proposing to extend the grammar. Only if you
created the temporary table with

CREATE TEMPORARY TABLE
...
ON COMMIT DROP;

would it drop the temporary table at transaction commit. It should be
100% compatible with existing code.

Mike Mascari
mascarm(at)mascari(dot)com

On Fri, 14 Jun 2002, Gavin Sherry wrote:

> On Thu, 13 Jun 2002, Mike Mascari wrote:
>
> >
> > 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 think this is a pretty useful feature. Shouldn't require too much
> work. A new relkind or a bool in TempTable and a little code in
> AtEOXact_temp_relations() to heap_drop_with_catalog() the registered temp
> table.
>
> Anyone else keen for this feature?

Attached is a patch implementing this. The patch is against 7.2.1
source. The grammar introduced is of the form:

CREATE TEMP TABLE ... ON COMMIT DROP;

Is this a desirable feature? Seems pretty useful to me.

Gavin

Slight bug in the previous patch. Logically (and according to SQL99's
treatment of ON COMMIT), it can be specified only for CREATE TEMP
TABLE. The patch throws an error if only CREATE TABLE has been specified.

Gavin

On Fri, 28 Jun 2002, Gavin Sherry wrote:

> On Fri, 14 Jun 2002, Gavin Sherry wrote:
>
> > On Thu, 13 Jun 2002, Mike Mascari wrote:
> >
> > >
> > > 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 think this is a pretty useful feature. Shouldn't require too much
> > work. A new relkind or a bool in TempTable and a little code in
> > AtEOXact_temp_relations() to heap_drop_with_catalog() the registered temp
> > table.
> >
> > Anyone else keen for this feature?
>
> Attached is a patch implementing this. The patch is against 7.2.1
> source. The grammar introduced is of the form:
>
> CREATE TEMP TABLE ... ON COMMIT DROP;
>
> Is this a desirable feature? Seems pretty useful to me.
>
> Gavin
>
>

Gavin Sherry wrote:
>
> Slight bug in the previous patch. Logically (and according to SQL99's
> treatment of ON COMMIT), it can be specified only for CREATE TEMP
> TABLE. The patch throws an error if only CREATE TABLE has been specified.

...

> >
> > Attached is a patch implementing this. The patch is against 7.2.1
> > source. The grammar introduced is of the form:
> >
> > CREATE TEMP TABLE ... ON COMMIT DROP;
> >
> > Is this a desirable feature? Seems pretty useful to me.
> >

Great! I'm give this a try.

Mike Mascari
mascarm(at)mascari(dot)com

> > Anyone else keen for this feature?
>
> Attached is a patch implementing this. The patch is against 7.2.1
> source. The grammar introduced is of the form:
>
> CREATE TEMP TABLE ... ON COMMIT DROP;
>
> Is this a desirable feature? Seems pretty useful to me.

It's useful, there's a patch - what more do we want!!!

Chris

Your patch has been added to the PostgreSQL unapplied patches list at:

http://candle.pha.pa.us/cgi-bin/pgpatches

I will try to apply it within the next 48 hours.

---------------------------------------------------------------------------

Gavin Sherry wrote:
> Slight bug in the previous patch. Logically (and according to SQL99's
> treatment of ON COMMIT), it can be specified only for CREATE TEMP
> TABLE. The patch throws an error if only CREATE TABLE has been specified.
>
> Gavin
>
> On Fri, 28 Jun 2002, Gavin Sherry wrote:
>
> > On Fri, 14 Jun 2002, Gavin Sherry wrote:
> >
> > > On Thu, 13 Jun 2002, Mike Mascari wrote:
> > >
> > > >
> > > > 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 think this is a pretty useful feature. Shouldn't require too much
> > > work. A new relkind or a bool in TempTable and a little code in
> > > AtEOXact_temp_relations() to heap_drop_with_catalog() the registered temp
> > > table.
> > >
> > > Anyone else keen for this feature?
> >
> > Attached is a patch implementing this. The patch is against 7.2.1
> > source. The grammar introduced is of the form:
> >
> > CREATE TEMP TABLE ... ON COMMIT DROP;
> >
> > Is this a desirable feature? Seems pretty useful to me.
> >
> > Gavin
> >
> >

Content-Description:

[ Attachment, skipping... ]

>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

Gavin, I will need a doc patch for this too. Thanks.

---------------------------------------------------------------------------

Gavin Sherry wrote:
> Slight bug in the previous patch. Logically (and according to SQL99's
> treatment of ON COMMIT), it can be specified only for CREATE TEMP
> TABLE. The patch throws an error if only CREATE TABLE has been specified.
>
> Gavin
>
> On Fri, 28 Jun 2002, Gavin Sherry wrote:
>
> > On Fri, 14 Jun 2002, Gavin Sherry wrote:
> >
> > > On Thu, 13 Jun 2002, Mike Mascari wrote:
> > >
> > > >
> > > > 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 think this is a pretty useful feature. Shouldn't require too much
> > > work. A new relkind or a bool in TempTable and a little code in
> > > AtEOXact_temp_relations() to heap_drop_with_catalog() the registered temp
> > > table.
> > >
> > > Anyone else keen for this feature?
> >
> > Attached is a patch implementing this. The patch is against 7.2.1
> > source. The grammar introduced is of the form:
> >
> > CREATE TEMP TABLE ... ON COMMIT DROP;
> >
> > Is this a desirable feature? Seems pretty useful to me.
> >
> > Gavin
> >
> >

Content-Description:

[ Attachment, skipping... ]

>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

Gavin, this is not even close to the CVS code. Would you regenerate
based on CVS. I could do it, but you will probably make a more reliable
patch.

---------------------------------------------------------------------------

Gavin Sherry wrote:
> Slight bug in the previous patch. Logically (and according to SQL99's
> treatment of ON COMMIT), it can be specified only for CREATE TEMP
> TABLE. The patch throws an error if only CREATE TABLE has been specified.
>
> Gavin
>
> On Fri, 28 Jun 2002, Gavin Sherry wrote:
>
> > On Fri, 14 Jun 2002, Gavin Sherry wrote:
> >
> > > On Thu, 13 Jun 2002, Mike Mascari wrote:
> > >
> > > >
> > > > 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 think this is a pretty useful feature. Shouldn't require too much
> > > work. A new relkind or a bool in TempTable and a little code in
> > > AtEOXact_temp_relations() to heap_drop_with_catalog() the registered temp
> > > table.
> > >
> > > Anyone else keen for this feature?
> >
> > Attached is a patch implementing this. The patch is against 7.2.1
> > source. The grammar introduced is of the form:
> >
> > CREATE TEMP TABLE ... ON COMMIT DROP;
> >
> > Is this a desirable feature? Seems pretty useful to me.
> >
> > Gavin
> >
> >

Content-Description:

[ Attachment, skipping... ]

>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

Hi Bruce,

I have been away on a long overdue holiday. Will get you this patch once I
catch up on email and pending 'day job' stuff.

Gavin

On Thu, 4 Jul 2002, Bruce Momjian wrote:

>
> Gavin, I will need a doc patch for this too. Thanks.
>
> ---------------------------------------------------------------------------
>
> Gavin Sherry wrote:
> > Slight bug in the previous patch. Logically (and according to SQL99's
> > treatment of ON COMMIT), it can be specified only for CREATE TEMP
> > TABLE. The patch throws an error if only CREATE TABLE has been specified.
> >
> > Gavin
> >
> > On Fri, 28 Jun 2002, Gavin Sherry wrote:
> >
> > > On Fri, 14 Jun 2002, Gavin Sherry wrote:
> > >
> > > > On Thu, 13 Jun 2002, Mike Mascari wrote:
> > > >
> > > > >
> > > > > 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 think this is a pretty useful feature. Shouldn't require too much
> > > > work. A new relkind or a bool in TempTable and a little code in
> > > > AtEOXact_temp_relations() to heap_drop_with_catalog() the registered temp
> > > > table.
> > > >
> > > > Anyone else keen for this feature?
> > >
> > > Attached is a patch implementing this. The patch is against 7.2.1
> > > source. The grammar introduced is of the form:
> > >
> > > CREATE TEMP TABLE ... ON COMMIT DROP;
> > >
> > > Is this a desirable feature? Seems pretty useful to me.
> > >
> > > Gavin
> > >
> > >
>
> Content-Description:
>
> [ Attachment, skipping... ]
>
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> > message can get through to the mailing list cleanly
>
>