Thread: 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@sigaev.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@sigaev.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@fetter.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@nasby.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@fetter.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-statementtransaction
-- Bruce Momjian bruce@momjian.us EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Bruce Momjian <bruce@momjian.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@momjian.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@momjian.us EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +