Add column if not exists (CINE)
in [PgSql]
|
Prev: [HACKERS] road.thepath no longer in pg_stats?
Next: providing tokenized version of parsed SQL script (was: nodeToString format and exporting the SQL parser)
From: Heikki Linnakangas on 28 Apr 2010 14:38 Robert Haas wrote: > On Wed, Apr 28, 2010 at 12:07 PM, Heikki Linnakangas > <heikki.linnakangas(a)enterprisedb.com> wrote: >> Robert Haas wrote: >>> On Wed, Apr 28, 2010 at 11:20 AM, Tom Lane <tgl(a)sss.pgh.pa.us> wrote: >>>> Robert Haas <robertmhaas(a)gmail.com> writes: >>>>> I don't believe you are fairly stating the consensus from previous >>>>> discussion and I believe that you are actually in the minority on this >>>>> one. I agree that we probably don't need to support this for object >>>>> types for which CREATE OR REPLACE is available or can be made >>>>> available, but that isn't feasible for all object types - tables and >>>>> columns being the obvious examples. >>>> What's obvious about it? In particular, I should think that ADD OR >>>> REPLACE COLUMN would usefully be defined as "ADD if no such column, >>>> else ALTER COLUMN as necessary to match this spec". Dropping the >>>> ALTER part of that has no benefit except to lazy implementors; it >>>> certainly is not more useful to users if they can't be sure of the >>>> column properties after issuing the command. >>> Actually, that's a good idea. But how will you handle tables? >> What do you mean? > > Well, how would you define CREATE OR REPLACE TABLE? It the table doesn't exist, create it. If it exists with the same name and same columns and constraints and all, do nothing. Otherwise throw an error. Maybe it should also check that the existing table is empty. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Tom Lane on 28 Apr 2010 14:57 Heikki Linnakangas <heikki.linnakangas(a)enterprisedb.com> writes: > Robert Haas wrote: >> Well, how would you define CREATE OR REPLACE TABLE? > It the table doesn't exist, create it. If it exists with the same name > and same columns and constraints and all, do nothing. Otherwise throw an > error. > Maybe it should also check that the existing table is empty. The last bit doesn't seem to make sense. If you want an empty table, you can do DROP IF EXISTS and then CREATE. ISTM that the use-cases where you don't want to do that are cases where you don't want to lose existing data. For either CINE or COR, there are a number of issues that are being hand-waved away here: is it OK to change ownership and/or permissions? What about foreign key constraints relating this table to others? For that matter it's not real clear that indexes, check constraints, etc should be allowed to survive. If they are allowed to survive then CINE TABLE is just the tip of the iceberg: to do anything useful you'd also need CINE for ADD CONSTRAINT, CREATE INDEX, ADD FOREIGN KEY, etc. And the more of those you add, the more problematic it gets to allow existing objects that don't quite match what the command says. Any of these commands are headache-y for something as complicated as a table. I'm not at all impressed by the argument that mysql does it, because they are *notorious* for being willing to ship half-baked solutions. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Robert Haas on 28 Apr 2010 20:46 On Wed, Apr 28, 2010 at 2:57 PM, Tom Lane <tgl(a)sss.pgh.pa.us> wrote: > Heikki Linnakangas <heikki.linnakangas(a)enterprisedb.com> writes: >> Robert Haas wrote: >>> Well, how would you define CREATE OR REPLACE TABLE? > >> It the table doesn't exist, create it. If it exists with the same name >> and same columns and constraints and all, do nothing. Otherwise throw an >> error. > >> Maybe it should also check that the existing table is empty. > > The last bit doesn't seem to make sense. If you want an empty table, > you can do DROP IF EXISTS and then CREATE. ISTM that the use-cases > where you don't want to do that are cases where you don't want to lose > existing data. Right. > For either CINE or COR, there are a number of issues that are being > hand-waved away here: is it OK to change ownership and/or permissions? > What about foreign key constraints relating this table to others? > For that matter it's not real clear that indexes, check constraints, > etc should be allowed to survive. If they are allowed to survive then > CINE TABLE is just the tip of the iceberg: to do anything useful you'd > also need CINE for ADD CONSTRAINT, CREATE INDEX, ADD FOREIGN KEY, etc. > And the more of those you add, the more problematic it gets to allow > existing objects that don't quite match what the command says. > > Any of these commands are headache-y for something as complicated > as a table. I'm not at all impressed by the argument that mysql > does it, because they are *notorious* for being willing to ship > half-baked solutions. We can artificially make this problem as complicated as we wish, but the people who are asking for this feature (including me) will, I believe, be quite happy with a solution that throws, say, a NOTICE instead of an ERROR when the object already exists, and then returns without doing anything further. There are very few, if any, definitional issues here, except by people who are brainstorming crazy alternative behaviors whose actual usefulness I very much doubt. CREATE OR REPLACE is indeed much more complicated. In fact, for tables, I maintain that you'll need to link with -ldwim to make it work properly. ....Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Tom Lane on 28 Apr 2010 21:15 Robert Haas <robertmhaas(a)gmail.com> writes: > We can artificially make this problem as complicated as we wish, but > the people who are asking for this feature (including me) will, I > believe, be quite happy with a solution that throws, say, a NOTICE > instead of an ERROR when the object already exists, and then returns > without doing anything further. There are very few, if any, > definitional issues here, except by people who are brainstorming crazy > alternative behaviors whose actual usefulness I very much doubt. > CREATE OR REPLACE is indeed much more complicated. In fact, for > tables, I maintain that you'll need to link with -ldwim to make it > work properly. This may in fact be an appropriate way to handle the case for tables, given the complexity of their definitions. However, the original point of the thread was about what to do for columns. I still say that COR rather than CINE semantics would be appropriate for columns. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Bruce Momjian on 30 Apr 2010 09:42
Tom Lane wrote: > Robert Haas <robertmhaas(a)gmail.com> writes: > > We can artificially make this problem as complicated as we wish, but > > the people who are asking for this feature (including me) will, I > > believe, be quite happy with a solution that throws, say, a NOTICE > > instead of an ERROR when the object already exists, and then returns > > without doing anything further. There are very few, if any, > > definitional issues here, except by people who are brainstorming crazy > > alternative behaviors whose actual usefulness I very much doubt. > > > CREATE OR REPLACE is indeed much more complicated. In fact, for > > tables, I maintain that you'll need to link with -ldwim to make it > > work properly. > > This may in fact be an appropriate way to handle the case for tables, > given the complexity of their definitions. However, the original > point of the thread was about what to do for columns. I still say > that COR rather than CINE semantics would be appropriate for columns. I have added this TODO item: Allow CREATE TABLE to optionally create a table if it does not already exist, without throwing an error The fact that tables contain data makes this more complex than other CREATE OR REPLACE operations. * http://archives.postgresql.org/pgsql-hackers/2010-04/msg01300.php -- Bruce Momjian <bruce(a)momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |