Re: [HACKERS] Non-standard feature request
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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Non-standard feature request
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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Non-standard feature request
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 temprel.diff.gz Description: GNU Zip compressed data ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Non-standard feature request
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 temprel2.diff.gz Description: GNU Zip compressed data ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Non-standard feature request
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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Non-standard feature request
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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Non-standard feature request
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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Non-standard feature request
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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Non-standard feature request
Tom Lane wrote: Mike Mascari [EMAIL PROTECTED] 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 [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Non-standard feature request
Bruce Momjian wrote: Tom Lane wrote: Mike Mascari [EMAIL PROTECTED] 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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Non-standard feature request
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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Non-standard feature request
Mike Mascari [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])