CREATE TEMP TABLE .... ON COMMIT
Attached is a revised version of my previous
ON COMMIT DROP
patch. This patch implements:
ON COMMIT { DROP | PRESERVE ROWS | DELETE ROWS }
The latter two are SQL99.
Sample usage:
---
template1=# begin;
BEGIN
template1=# create temp table a (a int) on commit drop;
CREATE
template1=# create temp table b (a int) on commit preserve rows;
CREATE
template1=# create temp table c (a int) on commit delete rows;
CREATE
template1=# insert into a values(1);
INSERT 24793 1
template1=# insert into b values(1);
INSERT 24794 1
template1=# insert into c values(1);
INSERT 24795 1
template1=# commit;
COMMIT
template1=# select * from a;
ERROR: Relation "a" does not exist
template1=# select * from b;
a
---
1
(1 row)
template1=# select * from c;
a
---
(0 rows)
template1=# create temp table a (a int) on commit drop;
ERROR: You must be inside a transaction to use ON COMMIT
---
Gavin
Gavin Sherry <swm@linuxworld.com.au> 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
On Fri, 9 Aug 2002, Tom Lane wrote:
Gavin Sherry <swm@linuxworld.com.au> writes:
template1=# create temp table a (a int) on commit drop;
ERROR: You must be inside a transaction to use ON COMMITSurely 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
Gavin Sherry <swm@linuxworld.com.au> 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
Gavin, was this addressed?
---------------------------------------------------------------------------
Tom Lane wrote:
Gavin Sherry <swm@linuxworld.com.au> 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 againBEGIN;
insert some rows in foo;
process rows in foo;
COMMIT; -- foo is now empty againrepeat until application quit...
What am I missing?
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073Gavin 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
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073Import Notes
Reply to msg id not found: Pine.LNX.4.21.0208141528430.7611-100000@linuxworld.com.au | Resolved by subject fallback
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:
Show quoted text
Gavin, was this addressed?
---------------------------------------------------------------------------
Tom Lane wrote:
Gavin Sherry <swm@linuxworld.com.au> 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 againBEGIN;
insert some rows in foo;
process rows in foo;
COMMIT; -- foo is now empty againrepeat until application quit...
What am I missing?
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
Gavin Sherry <swm@linuxworld.com.au> 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
Tom Lane wrote:
Gavin Sherry <swm@linuxworld.com.au> 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
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073Gavin, 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 <swm@linuxworld.com.au> writes:
template1=# create temp table a (a int) on commit drop;
ERROR: You must be inside a transaction to use ON COMMITSurely 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 implicitThis 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?
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073Bruce,
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:
Show quoted text
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 <swm@linuxworld.com.au> writes:
template1=# create temp table a (a int) on commit drop;
ERROR: You must be inside a transaction to use ON COMMITSurely 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 implicitThis 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?
What about Gavin's CREATE OR REPLACE stuff?
Chris
Show quoted text
-----Original Message-----
From: pgsql-hackers-owner@postgresql.org
[mailto:pgsql-hackers-owner@postgresql.org]On Behalf Of Gavin Sherry
Sent: Tuesday, 27 August 2002 1:21 PM
To: Bruce Momjian
Cc: Tom Lane; pgsql-hackers@postgresql.org; pgsql-patches@postgresql.org
Subject: Re: [HACKERS] [PATCHES] CREATE TEMP TABLE .... ON COMMITBruce,
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 <swm@linuxworld.com.au> writes:
template1=# create temp table a (a int) on commit drop;
ERROR: You must be inside a transaction to use ON COMMITSurely 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 implicitThis 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?
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
Revised patch is attached.
Passes all regression tests relating to temp tables. (I am getting
regression test issues with privileges.sql, rules.sql, conversion.sql).
Gavin
On Tue, 27 Aug 2002, Bruce Momjian wrote:
Show quoted text
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 <swm@linuxworld.com.au> writes:
template1=# create temp table a (a int) on commit drop;
ERROR: You must be inside a transaction to use ON COMMITSurely 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 implicitThis 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?
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:
Revised patch is attached.
Passes all regression tests relating to temp tables. (I am getting
regression test issues with privileges.sql, rules.sql, conversion.sql).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 <swm@linuxworld.com.au> writes:
template1=# create temp table a (a int) on commit drop;
ERROR: You must be inside a transaction to use ON COMMITSurely 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 implicitThis 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?
Content-Description:
[ Attachment, skipping... ]
Content-Description:
[ Attachment, skipping... ]
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073