Re: CREATE SCHEMA IF NOT EXISTS

2012/9/21 David E. Wheeler <david(at)justatheory(dot)com>

> On Sep 21, 2012, at 10:59 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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