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

    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.

> > 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

Attachment: 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:

Reply via email to