Re: [HACKERS] 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(at)linuxworld(dot)com(dot)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(at)linuxworld(dot)com(dot)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?

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(at)linuxworld(dot)com(dot)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(at)linuxworld(dot)com(dot)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
>
> ---------------------------(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
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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 <swm(at)linuxworld(dot)com(dot)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
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/users-lounge/docs/faq.html
> >
>
>

Gavin Sherry <swm(at)linuxworld(dot)com(dot)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(at)linuxworld(dot)com(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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(at)linuxworld(dot)com(dot)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?
>
> 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
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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 <swm(at)linuxworld(dot)com(dot)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?
> >
> > 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
> >
>
>

What about Gavin's CREATE OR REPLACE stuff?

Chris

> -----Original Message-----
> From: pgsql-hackers-owner(at)postgresql(dot)org
> [mailto:pgsql-hackers-owner(at)postgresql(dot)org]On Behalf Of Gavin Sherry
> Sent: Tuesday, 27 August 2002 1:21 PM
> To: Bruce Momjian
> Cc: Tom Lane; pgsql-hackers(at)postgresql(dot)org; pgsql-patches(at)postgresql(dot)org
> 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 <swm(at)linuxworld(dot)com(dot)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?
> > >
> > > 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
>

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

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

Content-Description:

[ Attachment, skipping... ]

Content-Description:

[ Attachment, skipping... ]

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073