On Tue, May 5, 2009 at 11:10 AM, Tom Lane <t...@sss.pgh.pa.us> wrote: > Robert Haas <robertmh...@gmail.com> writes: >> On Tue, May 5, 2009 at 8:56 AM, Andrew Dunstan <and...@dunslane.net> wrote: >>> We have debated CREATE ... IF NOT EXISTS in the past, and there is no >>> consensus on what it should do, so we don't have it for any command. That is >>> quite a different case from what's being asked for, and the two should not >>> be conflated. > >> I must be missing something, because the semantics of CREATE ... IF >> NOT EXISTS seem pretty well-defined to me, > > Please go read the prior threads (I think searching for "CINE" might > help, because we pretty shortly started abbreviating it like that).
OK - done, and thanks for the search tip. I still stand by my original comment. I think there is no semantic question about what CREATE IF NOT EXISTS ought to do. It ought to create the object if it doesn't exist. Otherwise, it ought to do nothing. That leads to two questions, the first of which Andrew asked in an email earlier today, and the second of which you asked in the previous discussion of this issue: 1. Why should it do nothing if the object already exists (as opposed to any other alternative)? Answer: Because that's what "CREATE IF NOT EXISTS" means when interpreted as English. If you wanted it to take some action when the object already exists, you'd have to call the command something like "CREATE IF NOT EXISTS OTHERWISE MUTILATE". Actually, we pretty much already have this in the form of "CREATE OR REPLACE", but "CREATE OR REPLACE" is only suitable for objects whose state can be fully defined by the command which creates them. This is true for views and functions, but false for tables and sequences, which contain user data. 2. What good is this anyway? Answer: It's good for schema management. Typically, you have a development system and N>0 production systems. Periodically, you do releases from develepment to production. When you release to a machine X, you want to upgrade that machine from whatever version of the schema it has now to the one appropriate to the version of the application you are releasing. So suppose you have a table caled foo that didn't exist in version 1 of the software. In version 2 it was added with columns id and name. In version 3 of the software a date column called bar was added. You are releasing version 3. So you write the following SQL script: CREATE TABLE IF NOT EXISTS foo (id serial, name varchar not null, primary key (id)); ALTER TABLE foo ADD COLUMN IF NOT EXISTS bar date; Observe that after running this script on EITHER a V1 or a V2 database, you now have the V3 schema. Without CINE, you have to either write separate upgrade scripts for V1->V3 and V2->V3, or write a PL/pgsql function that scrutinizes the system catalogs and figures out what needs to be done, or have some sort of bookkeeping system to keep track of which DDL bits have previously been executed, or something other alternative that will definitely be more complicated than the above. Obviously, there are more complex cases that CINE can't handle, but this is actually enough for a pretty good percentage of them in my experience. You typically add a table, then as releases go by you add more columns, then possibly at some point you decide that whole table was a stupid idea and you rip it out (which is already well-handled via DROP IF EXISTS). Typically when adding a column to an existing table you either allow nulls or set a default, either of which will work fine with this syntax. If you need to do something more complicated (like compute the initial values of bar based on the contents of some other table), well, then you're back to where you always are today. It seems to me that the right thing to do is to support CREATE OR REPLACE for as many object types as possible. But that won't be possible for things like tables unless we can make PostgreSQL AI-complete, so for those I think we ought to support CINE to cater to the design pattern above. That is of course only my opinion, but I gather from some of the comments made earlier today that I'm not the only one who wrestles with this problem. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
