Обсуждение: BUG #2051: CREATE TEMP TABLE AS SELECT doesn't play nice with ON COMMIT DROP

Поиск
Список
Период
Сортировка

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

От
"David Fetter"
Дата:
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
;

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

От
Jaime Casanova
Дата:
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 ;)

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

От
David Fetter
Дата:
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!

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

От
Tom Lane
Дата:
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

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

От
David Fetter
Дата:
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 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!

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

От
Tom Lane
Дата:
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

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

От
David Fetter
Дата:
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!

Re: BUG #2051: CREATE TEMP TABLE AS SELECT doesn't play

От
Neil Conway
Дата:
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

Re: BUG #2051: CREATE TEMP TABLE AS SELECT doesn't play

От
David Fetter
Дата:
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!

Re: BUG #2051: CREATE TEMP TABLE AS SELECT doesn't play nice

От
Bruce Momjian
Дата:
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 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@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 19073

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

От
David Fetter
Дата:
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!