Re: create temp table .. on commit delete rows

1)
# create temp table a ( a int ) without oids on commit delete rows;
# insert into a values(1);
# begin;
# insert into a values(2);
# commit;
# select * from a;
a
---
(0 rows)

2)
# insert into a values(1);
# begin;
# insert into a values(2);
# rollback;
# select * from a;
a
---
(0 rows)

It seems to me that 1) is good, but 2) makes some strange, unpredictable result...

--
Teodor Sigaev E-mail: teodor(at)sigaev(dot)ru
WWW: http://www.sigaev.ru/

Teodor Sigaev wrote:
> 2)
> # insert into a values(1);

You're running in auto-commit, mode. An implicit commit happens after
this statement. Which clears the table.

> # begin;
> # insert into a values(2);
> # rollback;
> # select * from a;
> a
> ---
> (0 rows)
>
> It seems to me that 1) is good, but 2) makes some strange, unpredictable
> result...

Looks right to me.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

> You're running in auto-commit, mode. An implicit commit happens after
> this statement. Which clears the table.
> Looks right to me.

Oops, I see

--
Teodor Sigaev E-mail: teodor(at)sigaev(dot)ru
WWW: http://www.sigaev.ru/

On Thu, Oct 12, 2006 at 03:51:39PM +0400, Teodor Sigaev wrote:
> >You're running in auto-commit, mode. An implicit commit happens
> >after this statement. Which clears the table. Looks right to me.
>
> Oops, I see

Should something notice and raise a warning when people create a TEMP
table and have AUTOCOMMIT on?

Cheers,
D
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!

On Thu, Oct 12, 2006 at 12:01:12PM -0700, David Fetter wrote:
> On Thu, Oct 12, 2006 at 03:51:39PM +0400, Teodor Sigaev wrote:
> > >You're running in auto-commit, mode. An implicit commit happens
> > >after this statement. Which clears the table. Looks right to me.
> >
> > Oops, I see
>
> Should something notice and raise a warning when people create a TEMP
> table and have AUTOCOMMIT on?

Maybe if ON COMMIT is set to DELETE ROWS or DROP...
--
Jim Nasby jim(at)nasby(dot)net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

On Thu, Oct 12, 2006 at 02:07:28PM -0500, Jim C. Nasby wrote:
> On Thu, Oct 12, 2006 at 12:01:12PM -0700, David Fetter wrote:
> > On Thu, Oct 12, 2006 at 03:51:39PM +0400, Teodor Sigaev wrote:
> > > >You're running in auto-commit, mode. An implicit commit
> > > >happens after this statement. Which clears the table. Looks
> > > >right to me.
> > >
> > > Oops, I see
> >
> > Should something notice and raise a warning when people create a
> > TEMP table and have AUTOCOMMIT on?
>
> Maybe if ON COMMIT is set to DELETE ROWS or DROP...

Sounds good :)

Cheers,
D
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!

David Fetter wrote:
> On Thu, Oct 12, 2006 at 02:07:28PM -0500, Jim C. Nasby wrote:
> > On Thu, Oct 12, 2006 at 12:01:12PM -0700, David Fetter wrote:
> > > On Thu, Oct 12, 2006 at 03:51:39PM +0400, Teodor Sigaev wrote:
> > > > >You're running in auto-commit, mode. An implicit commit
> > > > >happens after this statement. Which clears the table. Looks
> > > > >right to me.
> > > >
> > > > Oops, I see
> > >
> > > Should something notice and raise a warning when people create a
> > > TEMP table and have AUTOCOMMIT on?
> >
> > Maybe if ON COMMIT is set to DELETE ROWS or DROP...
>
> Sounds good :)

Added to TODO:

o Issue a notice if CREATE TABLE ... ON COMMIT { DELETE ROWS |
DROP } is issued outside a multi-statement transaction

--
Bruce Momjian bruce(at)momjian(dot)us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian <bruce(at)momjian(dot)us> writes:
> David Fetter wrote:
>>> Should something notice and raise a warning when people create a
>>> TEMP table and have AUTOCOMMIT on?

> Added to TODO:
> o Issue a notice if CREATE TABLE ... ON COMMIT { DELETE ROWS |
> DROP } is issued outside a multi-statement transaction

That is *not* what was suggested, and it doesn't seem very useful. The
problem really comes when one uses a temp table in autocommit mode, not
at creation time.

The problem with the original suggestion is that the backend can't do it
because AUTOCOMMIT is a notion that exists only in the client-side code.
And the client can't do it very well because it'd have to parse SQL
commands, and even with that it wouldn't see CREATE TEMP TABLE commands
issued inside functions.

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <bruce(at)momjian(dot)us> writes:
> > David Fetter wrote:
> >>> Should something notice and raise a warning when people create a
> >>> TEMP table and have AUTOCOMMIT on?
>
> > Added to TODO:
> > o Issue a notice if CREATE TABLE ... ON COMMIT { DELETE ROWS |
> > DROP } is issued outside a multi-statement transaction
>
> That is *not* what was suggested, and it doesn't seem very useful. The
> problem really comes when one uses a temp table in autocommit mode, not
> at creation time.
>
> The problem with the original suggestion is that the backend can't do it
> because AUTOCOMMIT is a notion that exists only in the client-side code.
> And the client can't do it very well because it'd have to parse SQL
> commands, and even with that it wouldn't see CREATE TEMP TABLE commands
> issued inside functions.

Ewe. Yea, I will just remove it. We can't issue a warning easily.

--
Bruce Momjian bruce(at)momjian(dot)us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +