Re: [HACKERS] create temp table .. on commit delete rows
Tom Lane wrote:
> Bruce Momjian <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED]
EnterpriseDBhttp://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] create temp table .. on commit delete rows
Bruce Momjian <[EMAIL PROTECTED]> 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
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] create temp table .. on commit delete rows
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 [EMAIL PROTECTED]
EnterpriseDBhttp://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [HACKERS] create temp table .. on commit delete rows
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 <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] create temp table .. on commit delete rows
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[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] create temp table .. on commit delete rows
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 <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] create temp table .. on commit delete rows
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: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] create temp table .. on commit delete rows
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 ---(end of broadcast)--- TIP 1: 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
[HACKERS] 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: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
