2012/9/21 David E. Wheeler <da...@justatheory.com> > On Sep 21, 2012, at 10:59 AM, Tom Lane <t...@sss.pgh.pa.us> wrote: > > > I don't believe this has been thought through nearly carefully enough. > > If CREATE SCHEMA created a schema and nothing more, then the proposed > > implementation would probably be fine. But per spec, CREATE SCHEMA > > can specify not only creating the schema but a whole bunch of objects > > within the schema. As coded, if the schema exists then creation of > > the specified sub-objects is just skipped, regardless of whether they > > exist or not. I doubt that this is really sane behavior. Would the > > principle of least astonishment dictate that the IF NOT EXISTS option > > apply implicitly to each sub-object as well? (If so, we'd have to > > extend everything that can appear in OptSchemaEltList; most of those > > commands don't have IF NOT EXISTS options today.) > > I had no idea about that functionality. Seems very strange. > > I completely forgot this functionality. The example above is from our docs [1]:
CREATE SCHEMA hollywood CREATE TABLE films (title text, release date, awards text[]) CREATE VIEW winners AS SELECT title, release FROM films WHERE awards IS NOT NULL; The "CREATE SCHEMA" statement accepts another "CREATE" commands (CREATE {TABLE | VIEW | INDEX | SEQUENCE | TRIGGER}), and the current patch do not consider this options. > A possible compromise is to allow the IF NOT EXISTS option only without > > a schema-element list, which I suspect is the only use-case David had in > > mind to start with anyway. > > Yes, true. > > Ok. > > The existing patch added the check in a pretty randomly chosen spot too, > > with one bad consequence being that if the schema already exists then it > > will fall out with the wrong user ID in effect, creating a security bug. > > But I'm not entirely sure where to put the check instead. Should we put > > it before or after the permissions checks --- that is, should IF NOT > > EXISTS require that you would have had permission to create the schema? > > Or, if the schema does exist, should we just call it good anyway? I'm > > too lazy to look at how other INE options resolved this question, but it > > seems like we ought to be consistent. > > Agreed. But if it already exists, where does it currently die? ISTM that > would be the point to check, if possible. > > I change the patch (attached) to skip only the schema creation and execute others statements... > > Also, the AUTHORIZATION clause of CREATE SCHEMA creates an aspect of all > > this that doesn't exist for any other kind of CREATE command, namely > > that the object might have been requested to be created under some other > > user id. For instance, supposing that we were to go forward with trying > > to create sub-objects, but the ownership of the existing schema is > > different from what's implied or specified by CREATE SCHEMA, should the > > sub-objects be (attempted to be) created as owned by that user instead? > > Perhaps not, but I'm not at all sure. > > I tend to think that if the schema exists, there should be no attempt to > create the sub-objects. Seems the least astonishing to me. > > Why don't create sub-objects? I think the INE clause must affect only "CREATE SCHEMA" statement, the others must be executed normally. We can discuss more about it... [1] http://www.postgresql.org/docs/9.2/interactive/sql-createschema.html -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL >> Blog sobre TI: http://fabriziomello.blogspot.com >> Perfil Linkedin: http://br.linkedin.com/in/fabriziomello >> Twitter: http://twitter.com/fabriziomello
create_schema_if_not_exists_v6.patch
Description: Binary data
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers