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
