Skip site navigation (1) Skip section navigation (2)

WIP: CREATE TABLE AS / WITH DATA

From: Neil Conway <neilc(at)samurai(dot)com>
To: pgsql-patches <pgsql-patches(at)postgresql(dot)org>
Cc: Gavin Sherry <swm(at)alcove(dot)com(dot)au>
Subject: WIP: CREATE TABLE AS / WITH DATA
Date: 2004-09-22 08:48:12
Message-ID: 1095842892.22485.46.camel@localhost.localdomain (view raw or flat)
Thread:
Lists: pgsql-patches
This patch makes CREATE TABLE AS conform more closely to SQL:2003 by
adding support for the WITH [ NO ] DATA clause (per section 11.3). The
standard says that this clause is mandatory, but I think it should be
optional in PG (partly for backward compatibility, and partly because I
think that is sane behavior). I also added regression tests and updated
the docs. Gavin Sherry helped me solve a parser problem (elaborated on
below) -- much thanks, Gavin.

This is 8.1 material so this patch won't be applied any time soon, but I
thought I'd post it here to get some feedback. Issues/notes on the
patch:

(1) The standard specifies that WITH [ NO ] DATA should follow the
subquery in CREATE TABLE AS. This causes problems for bison: if we make
the modifications to the grammar naively, it results in about 16
shift/reduce conflicts because the "WITH" keyword can appear in various
places in SelectStmt (e.g. WITH TIME ZONE). Gavin fixed that by doing
some manual lookahead in parser.c, like was previously being done for
UNION JOIN. Is this the best solution?

(2) I've modified the parser to transform the SELECT query to have a
"LIMIT 0" clause if WITH NO DATA is specified. This is ugly and
inefficient, but it was the cleanest way I could see to implement it --
we represent CREATE TABLE AS internally as a SelectStmt, and it seemed
cleaner to do this than to add more fields to SelectStmt. Is there a
cleaner way to do this? Perhaps the better solution is to make CREATE
TABLE AS its own Node (as I think has been raised in the past).

(3) A related question is: if the subquery's SelectStmt already has a
LIMIT, should I pfree() the limitCount field before overwriting it? My
guess was that parse trees are sometimes constructed in sufficiently
long-lived memory contexts that the pfree() is wise, but I'm not sure if
that's correct.

(Speaking of which, do we have any docs about when it's necessary to
pfree() each allocation and when it's not? IMHO this can sometimes be
difficult to know...)

(4) I haven't implemented support for WITH [ NO ] DATA in CREATE TABLE
AS / EXECUTE (which is depressingly implemented completely separately
from CREATE TABLE AS / SELECT). I think if we restructure CREATE TABLE
AS to be its own Node, we can clean this up as well.

(5) I called the DATA keyword "DATA_P" because I guessed Win32 might
have issues with it otherwise (see OBJECT_P and so on). I'll double
check whether that's necessary before committing anything -- if anyone
happens to know, please speak up.

Comments?

-Neil

P.S. I'm beginning to think that rather than applying this patch as-is
when we branch for 8.1, it might be a better idea to just bite the
bullet and restructure CREATE TABLE AS as suggested above. Thoughts?

Attachment: create-table-as-with-data-6.patch
Description: text/x-patch (11.3 KB)

Responses

pgsql-patches by date

Next:From: Dennis BjorklundDate: 2004-09-22 09:32:19
Subject: Re: 7.4 vs 7.3 ( hash join issue )
Previous:From: Gaetano MendolaDate: 2004-09-22 08:22:05
Subject: Re: 7.4 vs 7.3 ( hash join issue )

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group