BUG #2051: CREATE TEMP TABLE AS SELECT doesn't play nice with ON COMMIT DROP
The following bug has been logged online:
Bug reference: 2051
Logged by: David Fetter
Email address: david@fetter.org
PostgreSQL version: 8.0x
Operating system: Linux
Description: CREATE TEMP TABLE AS SELECT doesn't play nice with ON
COMMIT DROP
Details:
CREATE TEMP TABLE foo
AS SELECT a,b,c
FROM bar
ON COMMIT DROP;
causes a syntax error. So does
CREATE TEMP TABLE foo
ON COMMIT DROP
AS SELECT a,b,c
FROM bar
;
On 11/17/05, David Fetter <david@fetter.org> wrote:
The following bug has been logged online:
Bug reference: 2051
Logged by: David Fetter
Email address: david@fetter.org
PostgreSQL version: 8.0x
Operating system: Linux
Description: CREATE TEMP TABLE AS SELECT doesn't play nice with ON
COMMIT DROP
Details:CREATE TEMP TABLE foo
AS SELECT a,b,c
FROM bar
ON COMMIT DROP;causes a syntax error. So does
CREATE TEMP TABLE foo
ON COMMIT DROP
AS SELECT a,b,c
FROM bar
;
That is because there isn't an ON COMMIT clause for CREATE TABLE AS
see compatibility section in:
http://www.postgresql.org/docs/8.1/static/sql-createtableas.html
--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
On Thu, Nov 17, 2005 at 03:26:21PM -0500, Jaime Casanova wrote:
On 11/17/05, David Fetter <david@fetter.org> wrote:
The following bug has been logged online:
Bug reference: 2051
Logged by: David Fetter
Email address: david@fetter.org
PostgreSQL version: 8.0x
Operating system: Linux
Description: CREATE TEMP TABLE AS SELECT doesn't play nice with ON
COMMIT DROP
Details:CREATE TEMP TABLE foo
AS SELECT a,b,c
FROM bar
ON COMMIT DROP;causes a syntax error. So does
CREATE TEMP TABLE foo
ON COMMIT DROP
AS SELECT a,b,c
FROM bar
;That is because there isn't an ON COMMIT clause for CREATE TABLE AS
see compatibility section in:
http://www.postgresql.org/docs/8.1/static/sql-createtableas.html
This is still a bug, or at least a big gotcha.
Cheers,
D
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778
Remember to vote!
David Fetter <david@fetter.org> writes:
This is still a bug,
No, it's a feature request, and a rather low-priority one considering
you can already do
CREATE TEMP TABLE foo ... ON COMMIT DROP;
INSERT INTO foo SELECT ...
regards, tom lane
On Thu, Nov 17, 2005 at 04:37:14PM -0500, Tom Lane wrote:
David Fetter <david@fetter.org> writes:
This is still a bug,
No, it's a feature request, and a rather low-priority one considering
you can already doCREATE TEMP TABLE foo ... ON COMMIT DROP;
It's that first little elipsis mark that's the problem. Is there
something really clever I've been missing on how to do a dynamic table
creation?
CREATE TABLE foo (LIKE SELECT ...);
or some such?
Cheers,
D
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778
Remember to vote!
David Fetter <david@fetter.org> writes:
On Thu, Nov 17, 2005 at 04:37:14PM -0500, Tom Lane wrote:
CREATE TEMP TABLE foo ... ON COMMIT DROP;
It's that first little elipsis mark that's the problem. Is there
something really clever I've been missing on how to do a dynamic table
creation?
Oh, you're worried about what to do if you don't know the output column
set of the query? OK, that is a bit harder, but I think it's still a
corner case. How much are you really going to get done with the table
if you don't know what columns it has?
regards, tom lane
On Thu, Nov 17, 2005 at 05:32:43PM -0500, Tom Lane wrote:
David Fetter <david@fetter.org> writes:
On Thu, Nov 17, 2005 at 04:37:14PM -0500, Tom Lane wrote:
CREATE TEMP TABLE foo ... ON COMMIT DROP;
It's that first little elipsis mark that's the problem. Is there
something really clever I've been missing on how to do a dynamic
table creation?Oh, you're worried about what to do if you don't know the output
column set of the query? OK, that is a bit harder, but I think it's
still a corner case. How much are you really going to get done with
the table if you don't know what columns it has?
My use case is when I have a system of audit tables that look like
this:
CREATE TABLE foo (
...
);
CREATE TABLE foo_audit (
foo_audit_id BIGSERIAL PRIMARY KEY,
foo_actor TEXT,
foo_timestamp TIMESTAMP,
foo_action char(1) CHECK foo_action IN('D','I','U'),
old_foo foo,
new_foo foo
);
with appropriate TRIGGERs, etc. to make that happen. It nice feature
of being partitionable via constraint exclusion.
This is in aid of a system for making it possible to ALTER foo while
preserving the data in foo_audit.
Cheers,
D
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778
Remember to vote!
On Thu, 2005-11-17 at 13:07 -0800, David Fetter wrote:
This is still a bug, or at least a big gotcha.
It's not a bug, merely an unimplemented feature. If no one beats me to
it I'll take a look at doing this for 8.2.
-Neil
On Thu, Nov 17, 2005 at 07:11:42PM -0500, Neil Conway wrote:
On Thu, 2005-11-17 at 13:07 -0800, David Fetter wrote:
This is still a bug, or at least a big gotcha.
It's not a bug, merely an unimplemented feature. If no one beats me
to it I'll take a look at doing this for 8.2.
Fantastic :)
Cheers,
D
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778
Remember to vote!
TODO has:
o Add ON COMMIT capability to CREATE TABLE AS ... SELECT
---------------------------------------------------------------------------
David Fetter wrote:
On Thu, Nov 17, 2005 at 05:32:43PM -0500, Tom Lane wrote:
David Fetter <david@fetter.org> writes:
On Thu, Nov 17, 2005 at 04:37:14PM -0500, Tom Lane wrote:
CREATE TEMP TABLE foo ... ON COMMIT DROP;
It's that first little elipsis mark that's the problem. Is there
something really clever I've been missing on how to do a dynamic
table creation?Oh, you're worried about what to do if you don't know the output
column set of the query? OK, that is a bit harder, but I think it's
still a corner case. How much are you really going to get done with
the table if you don't know what columns it has?My use case is when I have a system of audit tables that look like
this:CREATE TABLE foo (
...
);CREATE TABLE foo_audit (
foo_audit_id BIGSERIAL PRIMARY KEY,
foo_actor TEXT,
foo_timestamp TIMESTAMP,
foo_action char(1) CHECK foo_action IN('D','I','U'),
old_foo foo,
new_foo foo
);with appropriate TRIGGERs, etc. to make that happen. It nice feature
of being partitionable via constraint exclusion.This is in aid of a system for making it possible to ALTER foo while
preserving the data in foo_audit.Cheers,
D
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778Remember to vote!
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073On Tue, Nov 22, 2005 at 06:20:37PM -0500, Bruce Momjian wrote:
TODO has:
o Add ON COMMIT capability to CREATE TABLE AS ... SELECT
Great :)
Cheers,
D
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 415 235 3778
Remember to vote!