Re: ALTER TABLE ... IF EXISTS feature?
От | Robert Haas |
---|---|
Тема | Re: ALTER TABLE ... IF EXISTS feature? |
Дата | |
Msg-id | AANLkTi=oJZnqkSHy-YPsyhDzAhQjZVny3DT9q0zHYiux@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: ALTER TABLE ... IF EXISTS feature? (Daniel Farina <drfarina@acm.org>) |
Ответы |
Re: ALTER TABLE ... IF EXISTS feature?
(Daniel Farina <drfarina@acm.org>)
Re: ALTER TABLE ... IF EXISTS feature? (Bruce Momjian <bruce@momjian.us>) |
Список | 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
В списке pgsql-hackers по дате отправления: