PostgreSQL Bugs - BUG #2051: CREATE TEMP TABLE AS SELECT doesn't play nice with ON COMMIT DROP

Author

BUG #2051: CREATE TEMP TABLE AS SELECT doesn't play nice with ON COMMIT DROP

David Fetter

2005-11-17, 8:25 pm

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
;

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Jaime Casanova

2005-11-17, 8:25 pm

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/...atetableas.html

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

David Fetter

2005-11-17, 8:25 pm

On Thu, Nov 17, 2005 at 03:26:21PM -0500, Jaime Casanova wrote:
> On 11/17/05, David Fetter <david@fetter.org> wrote:
>
> 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/...atetableas.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!

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Tom Lane

2005-11-17, 8:25 pm

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

David Fetter

2005-11-17, 8:25 pm

On Thu, Nov 17, 2005 at 04:37:14PM -0500, Tom Lane wrote:
> David Fetter <david@fetter.org> writes:
>
> No, it's a feature request, and a rather low-priority one considering
> you can already do
>
> 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?

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!

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Tom Lane

2005-11-17, 8:25 pm

David Fetter <david@fetter.org> writes:
> On Thu, Nov 17, 2005 at 04:37:14PM -0500, Tom Lane wrote:
[color=darkred]
> 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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

David Fetter

2005-11-17, 8:25 pm

On Thu, Nov 17, 2005 at 05:32:43PM -0500, Tom Lane wrote:
> David Fetter <david@fetter.org> writes:
>
>
> 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!

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

David Fetter

2005-11-22, 8:24 pm

On 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!

---------------------------(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

Sponsored Links