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(at)fetter(dot)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(at)fetter(dot)org> wrote:
>
> The following bug has been logged online:
>
> Bug reference: 2051
> Logged by: David Fetter
> Email address: david(at)fetter(dot)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(at)fetter(dot)org> wrote:
> >
> > The following bug has been logged online:
> >
> > Bug reference: 2051
> > Logged by: David Fetter
> > Email address: david(at)fetter(dot)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(at)fetter(dot)org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778

Remember to vote!

David Fetter <david(at)fetter(dot)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(at)fetter(dot)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;

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(at)fetter(dot)org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778

Remember to vote!

David Fetter <david(at)fetter(dot)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(at)fetter(dot)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(at)fetter(dot)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(at)fetter(dot)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(at)fetter(dot)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(at)fetter(dot)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
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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(at)fetter(dot)org http://fetter.org/
phone: +1 415 235 3778

Remember to vote!