Re: ALTER TABLE ... IF EXISTS feature? - Mailing list pgsql-hackers

On Fri, Nov 5, 2010 at 7:49 PM, Daniel Farina <drfarina@acm.org> wrote:
> On Fri, Nov 5, 2010 at 4:20 PM, Robert Haas <robertmhaas@gmail.com> wrote:
>> Can you give us a self-contained example of the problem you're talking about?
>
> Sure. Consider the following:
>
> CREATE TABLE t1 (
>    id integer PRIMARY KEY
> );
>
> CREATE TABLE t2 (
>    id integer PRIMARY KEY,
>    fk integer
> );
>
> ALTER TABLE ONLY t2
>    ADD CONSTRAINT t2_constr FOREIGN KEY (fk) REFERENCES t1(id);
>
> Try something like this:
>
> createdb foo
> psql -1f this_ddl.sql foo
> pg_dump --clean foo > cleaning_backup.sql
> # db wipe
> dropdb foo
> createdb foo
> psql -1f cleaning_backup.sql foo
>
> The last command will return non-zero and abort the xact early on,
> because of the following stanza in pg_dump --clean's output:
>
> ALTER TABLE ONLY public.t2 DROP CONSTRAINT t2_constr;
> ALTER TABLE ONLY public.t2 DROP CONSTRAINT t2_pkey;
> ALTER TABLE ONLY public.t1 DROP CONSTRAINT t1_pkey;
> DROP TABLE public.t2;
> DROP TABLE public.t1;
>
> Since there's no public.t1/t2, it's not possible to ALTER them.
>
> I'm not entirely sure why the DROPs CONSTRAINT on pkeys are being
> done, as they only introduce an internal (or is it auto?) style
> self-dependency. It is more obvious why foreign keys are dropped,
> which is to break up the dependencies so that tables can be dropped
> without CASCADE.

If we're going to try to fix this, we probably ought to try to make
sure that we are fixing it fairly completely.  How confident are you
that this is the only problem?

With respect to the syntax itself, I have mixed feelings.  On the one
hand, I'm a big fan of CREATE IF NOT EXISTS and DROP IF EXISTS
precisely because I believe they handle many common cases that people
want in real life without much hullabaloo.  But, there's clearly some
limit to what can reasonably be done this way.  At some point, what
you really want is some kind of meta-language where you can write
things like:

IF EXISTS TABLE t1 THEN  ALTER TABLE t1 DROP CONSTRAINT IF EXISTS t1_constr;
END IF;

...or possibly something much more complicated, like checking whether
a table foo has a column called bar and if so doing nothing but if not
but a column called baz exists then renaming it to bar and otherwise
adding a column called bar.

Since we now have PL/pgsql by default, we could possibly fix pg_dump
--clean by emitting a DO block, although the syntax for checking
existence of a table is none too pretty, and it would make pg_dump
--clean rely for correctness on plpgsql being installed, which might
be none too desirable.  It would actually be sort of spiffy to be able
to have some of the PL/pgsql control constructs available in straight
SQL, but I'm not expecting that to happen any time in the forseeable
future.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company