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: Robert Haas on 27 Apr 2010 20:18 On Tue, Apr 27, 2010 at 6:45 PM, Kjell Rune Skaaraas <kjella79(a)yahoo.no> wrote: > Hello, > > I've been reading the earlier threads at: > http://archives.postgresql.org/pgsql-hackers/2009-05/thrd7.php#00252 > http://archives.postgresql.org/pgsql-hackers/2005-10/thrd4.php#00632 > and I'm not sure I have anything that substantially new to add but: > > 1. I can't see there's an unambiguity about what the syntax would do. It is IF NOT EXISTS, not IF NOT LIKE. Anyone who shoots themselves in the foot by calling a CINE and thinking that a preexisting differently defined column is magically converted deserves it. Either it should act exactly like the non-CINE command, or do nothing at all as if the statement wasn't there. > > 2. The use case is pretty clear to me - flexible scripts that'll bring all earlier database versions to the latest schema. I've been experimenting in 9.0 alpha with calling DROP CONSTRAINT IF EXISTS then ADD CONSTRAINT with named constants for a CINE effect. which as a side effect will correct any updated constraints too - and it works great. Unfortunately DROP COLUMN IF EXISTS then ADD COLUMN has the side effect of deleting all the data, so that's hardly usable. > > I saw some indications that this might be a minority opinion, well I would like to cast a vote FOR this functionality. The workarounds are ugly, the solution simple and while I agree it's possible to misuse it, my opinion is that you shouldn't become a surgeon if you can't handle a scalpel. In this case I get the feeling I'm reading instructions on how to do surgery with a butter knife because we don't dare hand out anything sharper. I've already said my piece on this, but I couldn't agree more. Well said, and your use case is exactly the one I want it for. ....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: Takahiro Itagaki on 28 Apr 2010 00:02 Kjell Rune Skaaraas <kjella79(a)yahoo.no> wrote: > I've been reading the earlier threads at: > http://archives.postgresql.org/pgsql-hackers/2009-05/thrd7.php#00252 > http://archives.postgresql.org/pgsql-hackers/2005-10/thrd4.php#00632 > and I'm not sure I have anything that substantially new to add but: > > I saw some indications that this might be a minority opinion, > well I would like to cast a vote FOR this functionality. +1 for CINE, just because MySQL supports it. But before developing, we need to decide how to handle an added object that has the same name but has different definitions. Also, developers should consider not only ADD COLUMN but also other CREATE or ADD commands. The patch will be large, including documentation adjustments in many places -- it would be hard work. Regards, --- Takahiro Itagaki NTT Open Source Software Center -- 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: Andrew Dunstan on 28 Apr 2010 08:23 Takahiro Itagaki wrote: > Kjell Rune Skaaraas <kjella79(a)yahoo.no> wrote: > > >> I've been reading the earlier threads at: >> http://archives.postgresql.org/pgsql-hackers/2009-05/thrd7.php#00252 >> http://archives.postgresql.org/pgsql-hackers/2005-10/thrd4.php#00632 >> and I'm not sure I have anything that substantially new to add but: >> >> I saw some indications that this might be a minority opinion, >> well I would like to cast a vote FOR this functionality. >> > > +1 for CINE, just because MySQL supports it. > MySQL compatibility has never been our aim. We should adopt ideas from other projects because they are good, not just because they are there. That doesn't mean I don't think this is a good idea. > But before developing, we need to decide how to handle an added object > that has the same name but has different definitions. > The OP explicitly stated that in his opinion nothing should be done in such cases. That's a defensible position, in the case of objects such as tables that must be unique by name (e.g. tables). But what would we do about objects where the name could be overloaded? Since we would presumably want to do this for all (or almost all) of our CREATE/ADD commands, we'd need a policy on those. > Also, developers should consider not only ADD COLUMN but also other > CREATE or ADD commands. The patch will be large, including documentation > adjustments in many places -- it would be hard work. > > > I can speak with some experience on this at least. :-) I don't see that it would be a heck of a lot bigger than the DROP IF EXISTS cases, which after the first few had been done were not hard, merely tedious to do :-) cheers andrew -- 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 09:58 Andrew Dunstan <andrew(a)dunslane.net> writes: > Takahiro Itagaki wrote: >> But before developing, we need to decide how to handle an added object >> that has the same name but has different definitions. > The OP explicitly stated that in his opinion nothing should be done in > such cases. That's a defensible position, in the case of objects such as > tables that must be unique by name (e.g. tables). But what would we do > about objects where the name could be overloaded? Even if it's defensible, the consensus position so far has been that it's a bad design. Every time we've looked at this, we have concluded that CREATE OR REPLACE semantics are considerably safer to use, because there is no question what the state of the object is afterwards. That argument is just as valid for a column as for anything larger. AFAICS, the only excuse CINE has for living is that (people think) it would take less work to implement. 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: "Ross J. Reedstrom" on 28 Apr 2010 10:17
On Tue, Apr 27, 2010 at 08:18:13PM -0400, Robert Haas wrote: > On Tue, Apr 27, 2010 at 6:45 PM, Kjell Rune Skaaraas <kjella79(a)yahoo.no> wrote: [snip] > > I saw some indications that this might be a minority opinion, well I would like to cast a vote FOR this functionality. The workarounds are ugly, the solution simple and while I agree it's possible to misuse it, my opinion is that you shouldn't become a surgeon if you can't handle a scalpel. In this case I get the feeling I'm reading instructions on how to do surgery with a butter knife because we don't dare hand out anything sharper. > > I've already said my piece on this, but I couldn't agree more. Well > said, and your use case is exactly the one I want it for. > +1 (Scribbles down the phrase "instructions on how to do surgery with a butter knife because we don't dare hand out anything sharper" for future repurposing) Ross -- Ross Reedstrom, Ph.D. reedstrm(a)rice.edu Systems Engineer & Admin, Research Scientist phone: 713-348-6166 The Connexions Project http://cnx.org fax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |