Re: [HACKERS] [PATCHES] CREATE TEMP TABLE .... ON COMMIT
Gavin, how are you doing with this. As I remember, the only remaining issue was where to store the 'drop on commit' information in the backend. If that is all there is, we can come up with a solution. --- Gavin Sherry wrote: On Fri, 9 Aug 2002, Tom Lane wrote: Gavin Sherry [EMAIL PROTECTED] writes: template1=# create temp table a (a int) on commit drop; ERROR: You must be inside a transaction to use ON COMMIT Surely that's only for ON COMMIT DROP, if you intend to offer the others? I should have provided details of this in the email. SQL99 details the baviour as follows: If TEMPORARY is specified and ON COMMIT is omitted, then ON COMMIT DELETE ROWS is implicit This might give users a bit of a surprise so the effective behaviour is ON COMMIT PRESERVE ROWS. As for your question (and, perhaps, SQL99) I don't seen how it makes any sense to specify ON COMMIT outside of a transaction block. regards, tom lane Gavin ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] CREATE TEMP TABLE .... ON COMMIT
Bruce, I have a working patch for this I just need to test it further. It occured to me that there was a bug with the previous implementation in as much as it didn't handle situations where the user dropped the temp table in a transaction block. As such, I have added a flag to the structure marking it dead. This also needs to be undone at ABORT :-). (I knew there was a reason for storing the ON COMMIT flag in the system catalogs). Once I have tested further I will send off. If I come across a problem or cannot get to this by Wednesday (tomorrow) I will send them off to Neil Conway so that they can make it in. (Unfortunately, I am v. busy atm). Gavin On Tue, 27 Aug 2002, Bruce Momjian wrote: Gavin, how are you doing with this. As I remember, the only remaining issue was where to store the 'drop on commit' information in the backend. If that is all there is, we can come up with a solution. --- Gavin Sherry wrote: On Fri, 9 Aug 2002, Tom Lane wrote: Gavin Sherry [EMAIL PROTECTED] writes: template1=# create temp table a (a int) on commit drop; ERROR: You must be inside a transaction to use ON COMMIT Surely that's only for ON COMMIT DROP, if you intend to offer the others? I should have provided details of this in the email. SQL99 details the baviour as follows: If TEMPORARY is specified and ON COMMIT is omitted, then ON COMMIT DELETE ROWS is implicit This might give users a bit of a surprise so the effective behaviour is ON COMMIT PRESERVE ROWS. As for your question (and, perhaps, SQL99) I don't seen how it makes any sense to specify ON COMMIT outside of a transaction block. regards, tom lane Gavin ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] CREATE TEMP TABLE .... ON COMMIT
What about Gavin's CREATE OR REPLACE stuff? Chris -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Gavin Sherry Sent: Tuesday, 27 August 2002 1:21 PM To: Bruce Momjian Cc: Tom Lane; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: [HACKERS] [PATCHES] CREATE TEMP TABLE ON COMMIT Bruce, I have a working patch for this I just need to test it further. It occured to me that there was a bug with the previous implementation in as much as it didn't handle situations where the user dropped the temp table in a transaction block. As such, I have added a flag to the structure marking it dead. This also needs to be undone at ABORT :-). (I knew there was a reason for storing the ON COMMIT flag in the system catalogs). Once I have tested further I will send off. If I come across a problem or cannot get to this by Wednesday (tomorrow) I will send them off to Neil Conway so that they can make it in. (Unfortunately, I am v. busy atm). Gavin On Tue, 27 Aug 2002, Bruce Momjian wrote: Gavin, how are you doing with this. As I remember, the only remaining issue was where to store the 'drop on commit' information in the backend. If that is all there is, we can come up with a solution. -- - Gavin Sherry wrote: On Fri, 9 Aug 2002, Tom Lane wrote: Gavin Sherry [EMAIL PROTECTED] writes: template1=# create temp table a (a int) on commit drop; ERROR: You must be inside a transaction to use ON COMMIT Surely that's only for ON COMMIT DROP, if you intend to offer the others? I should have provided details of this in the email. SQL99 details the baviour as follows: If TEMPORARY is specified and ON COMMIT is omitted, then ON COMMIT DELETE ROWS is implicit This might give users a bit of a surprise so the effective behaviour is ON COMMIT PRESERVE ROWS. As for your question (and, perhaps, SQL99) I don't seen how it makes any sense to specify ON COMMIT outside of a transaction block. regards, tom lane Gavin ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [PATCHES] CREATE TEMP TABLE .... ON COMMIT
Gavin, was this addressed? --- Tom Lane wrote: Gavin Sherry [EMAIL PROTECTED] writes: As for your question (and, perhaps, SQL99) I don't seen how it makes any sense to specify ON COMMIT outside of a transaction block. Surely it does. CREATE TEMP TABLE foo(...) ON COMMIT DELETE ROWS; BEGIN; insert some rows in foo; process rows in foo; COMMIT; -- foo is now empty again BEGIN; insert some rows in foo; process rows in foo; COMMIT; -- foo is now empty again repeat until application quit... What am I missing? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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] [PATCHES] CREATE TEMP TABLE .... ON COMMIT
Bruce, I intend on addressing this by completely rewriting the patch. When I spoke to Tom and yourself about merging it with 7.3 at OSCON I argued that storing the ON COMMIT data in a global linked list was better (strictly for performance reasons). Given that I've incorrectly implemented DELETE ROWS, I think I'll bite the bullet and store the ON COMMIT data in the system catalogues per SQL99. Thoughts? As for when the patch will arrive: as I said in a previous email, I am quite busy at the moment. I would like to get this into 7.3, along with all the other patches or features I've put my hand up for. What will be the effective cut off for patches of this nature given 7.3 beta at the end of the month. Gavin On Wed, 14 Aug 2002, Bruce Momjian wrote: Gavin, was this addressed? --- Tom Lane wrote: Gavin Sherry [EMAIL PROTECTED] writes: As for your question (and, perhaps, SQL99) I don't seen how it makes any sense to specify ON COMMIT outside of a transaction block. Surely it does. CREATE TEMP TABLE foo(...) ON COMMIT DELETE ROWS; BEGIN; insert some rows in foo; process rows in foo; COMMIT; -- foo is now empty again BEGIN; insert some rows in foo; process rows in foo; COMMIT; -- foo is now empty again repeat until application quit... What am I missing? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html ---(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] [PATCHES] CREATE TEMP TABLE .... ON COMMIT
Gavin Sherry wrote: Bruce, I intend on addressing this by completely rewriting the patch. When I spoke to Tom and yourself about merging it with 7.3 at OSCON I argued that storing the ON COMMIT data in a global linked list was better (strictly for performance reasons). Given that I've incorrectly implemented DELETE ROWS, I think I'll bite the bullet and store the ON COMMIT data in the system catalogues per SQL99. Thoughts? As for when the patch will arrive: as I said in a previous email, I am quite busy at the moment. I would like to get this into 7.3, along with all the other patches or features I've put my hand up for. What will be the effective cut off for patches of this nature given 7.3 beta at the end of the month. The cutoff will be the start of beta, which should be September 1. I am workikng through my email backlog, and my next step tomorrow is to focus on the open issues to decide how or if they will be done for 7.3 so we can focus our energy. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] [PATCHES] CREATE TEMP TABLE .... ON COMMIT
Gavin Sherry [EMAIL PROTECTED] writes: ... I think I'll bite the bullet and store the ON COMMIT data in the system catalogues per SQL99. Thoughts? Seems like the very hard way, considering that there's no reason at all for the ON COMMIT status to survive a given backend run. I'd certainly vote against adding pg_class columns for it, if that's what you had in mind. I don't much like reintroducing the backend-local list of temp tables that existed in earlier releases, but maybe that's the best way to handle this feature. Anyone see a better way? ... I would like to get this into 7.3, along with all the other patches or features I've put my hand up for. What will be the effective cut off for patches of this nature given 7.3 beta at the end of the month. End of the month of course ... but I will say that the standards are going to rise as we get closer to the end. Patches submitted in the last week or so had better be right the first time. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [PATCHES] CREATE TEMP TABLE .... ON COMMIT
Tom Lane wrote: Gavin Sherry [EMAIL PROTECTED] writes: ... I think I'll bite the bullet and store the ON COMMIT data in the system catalogues per SQL99. Thoughts? Seems like the very hard way, considering that there's no reason at all for the ON COMMIT status to survive a given backend run. I'd certainly vote against adding pg_class columns for it, if that's what you had in mind. I don't much like reintroducing the backend-local list of temp tables that existed in earlier releases, but maybe that's the best way to handle this feature. Anyone see a better way? I never did like that backend-local list of temp rels. It was too hard to make it obey the table semantics. I guess if you listed them by oid you could get it to work. Seems we should be able to put this info somewhere in the system tables. Could we throw it into pg_description? We have paired down those system tables so far there isn't a place to stash random stuff. ... I would like to get this into 7.3, along with all the other patches or features I've put my hand up for. What will be the effective cut off for patches of this nature given 7.3 beta at the end of the month. End of the month of course ... but I will say that the standards are going to rise as we get closer to the end. Patches submitted in the last week or so had better be right the first time. Yep, we are not going to reject the stuff, but we are going to look at it _really_ well. :-) -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] CREATE TEMP TABLE .... ON COMMIT
Gavin Sherry [EMAIL PROTECTED] writes: template1=# create temp table a (a int) on commit drop; ERROR: You must be inside a transaction to use ON COMMIT Surely that's only for ON COMMIT DROP, if you intend to offer the others? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [PATCHES] CREATE TEMP TABLE .... ON COMMIT
On Fri, 9 Aug 2002, Tom Lane wrote: Gavin Sherry [EMAIL PROTECTED] writes: template1=# create temp table a (a int) on commit drop; ERROR: You must be inside a transaction to use ON COMMIT Surely that's only for ON COMMIT DROP, if you intend to offer the others? I should have provided details of this in the email. SQL99 details the baviour as follows: If TEMPORARY is specified and ON COMMIT is omitted, then ON COMMIT DELETE ROWS is implicit This might give users a bit of a surprise so the effective behaviour is ON COMMIT PRESERVE ROWS. As for your question (and, perhaps, SQL99) I don't seen how it makes any sense to specify ON COMMIT outside of a transaction block. regards, tom lane Gavin ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] [PATCHES] CREATE TEMP TABLE .... ON COMMIT
Gavin Sherry [EMAIL PROTECTED] writes: As for your question (and, perhaps, SQL99) I don't seen how it makes any sense to specify ON COMMIT outside of a transaction block. Surely it does. CREATE TEMP TABLE foo(...) ON COMMIT DELETE ROWS; BEGIN; insert some rows in foo; process rows in foo; COMMIT; -- foo is now empty again BEGIN; insert some rows in foo; process rows in foo; COMMIT; -- foo is now empty again repeat until application quit... What am I missing? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
