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-statement transaction
--
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 transactionThat 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. +