CREATE SCHEMA IF NOT EXISTS

2012/8/15 David E. Wheeler <david@justatheory.com>

Hackers,

Is there any reason not to add $subject? Would it be difficult?

Looking to the source code I think this feature isn't hard to implement...
I'm writing a little path to do that and I'll send soon...

Would save me having to write a DO statement every time I needed it (which
in migration scripts is fairly often).

I understand your difficulty.

Regards,

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL

On Aug 15, 2012, at 11:31 AM, Fabrízio de Royes Mello wrote:

Is there any reason not to add $subject? Would it be difficult?

Looking to the source code I think this feature isn't hard to implement... I'm writing a little path to do that and I'll send soon...

Cool, thanks!

David

2012/8/15 David E. Wheeler <david@justatheory.com>

On Aug 15, 2012, at 11:31 AM, Fabrízio de Royes Mello wrote:

Is there any reason not to add $subject? Would it be difficult?

Looking to the source code I think this feature isn't hard to

implement... I'm writing a little path to do that and I'll send soon...

Cool, thanks!

The attached patch implement this feature:

CREATE SCHEMA [ IF NOT EXISTS ] schema_name [ AUTHORIZATION user_name ] [
schema_element [ ... ] ]
CREATE SCHEMA [ IF NOT EXISTS ] AUTHORIZATION user_name [ schema_element [
... ] ]

Now, PostgreSQL don't trow an error if we use "IF NOT EXISTS" in "CREATE
SCHEMA" statement.

So, I don't know the next steps...

Regards,

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL

On 08/16/2012 01:36 PM, Fabrízio de Royes Mello wrote:

2012/8/15 David E. Wheeler <david@justatheory.com
<mailto:david@justatheory.com>>

On Aug 15, 2012, at 11:31 AM, Fabrízio de Royes Mello wrote:

Is there any reason not to add $subject? Would it be difficult?

Looking to the source code I think this feature isn't hard to

implement... I'm writing a little path to do that and I'll send
soon...

Cool, thanks!

The attached patch implement this feature:

CREATE SCHEMA [ IF NOT EXISTS ] schema_name [ AUTHORIZATION user_name
] [ schema_element [ ... ] ]
CREATE SCHEMA [ IF NOT EXISTS ] AUTHORIZATION user_name [
schema_element [ ... ] ]

Now, PostgreSQL don't trow an error if we use "IF NOT EXISTS" in
"CREATE SCHEMA" statement.

So, I don't know the next steps...

Please see
<http://wiki.postgresql.org/wiki/Developer_FAQ#I_have_developed_a_patch.2C_what_next.3F&gt;

cheers

andrew

On Aug 16, 2012, at 10:36 AM, Fabrízio de Royes Mello wrote:

The attached patch implement this feature:

CREATE SCHEMA [ IF NOT EXISTS ] schema_name [ AUTHORIZATION user_name ] [ schema_element [ ... ] ]
CREATE SCHEMA [ IF NOT EXISTS ] AUTHORIZATION user_name [ schema_element [ ... ] ]

Now, PostgreSQL don't trow an error if we use "IF NOT EXISTS" in "CREATE SCHEMA" statement.

So, I don't know the next steps...

Awesome, thanks! Please add it to the next CommitFest:

https://commitfest.postgresql.org/action/commitfest_view?id=15

Best,

David

2012/8/16 David E. Wheeler <david@justatheory.com>

On Aug 16, 2012, at 10:36 AM, Fabrízio de Royes Mello wrote:

The attached patch implement this feature:

CREATE SCHEMA [ IF NOT EXISTS ] schema_name [ AUTHORIZATION user_name ]

[ schema_element [ ... ] ]

CREATE SCHEMA [ IF NOT EXISTS ] AUTHORIZATION user_name [ schema_element

[ ... ] ]

Now, PostgreSQL don't trow an error if we use "IF NOT EXISTS" in "CREATE

SCHEMA" statement.

So, I don't know the next steps...

Awesome, thanks! Please add it to the next CommitFest:

https://commitfest.postgresql.org/action/commitfest_view?id=15

Patch added to CommitFest:

https://commitfest.postgresql.org/action/patch_view?id=907

Thanks,

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL

2012/8/16 Fabrízio de Royes Mello <fabriziomello@gmail.com>:

The attached patch implement this feature:

CREATE SCHEMA [ IF NOT EXISTS ] schema_name [ AUTHORIZATION user_name ] [
schema_element [ ... ] ]
CREATE SCHEMA [ IF NOT EXISTS ] AUTHORIZATION user_name [ schema_element [
... ] ]

Now, PostgreSQL don't trow an error if we use "IF NOT EXISTS" in "CREATE
SCHEMA" statement.

I started testing this, but I didn't see regression tests for it.
Could you write them?.

Best.
--
Dickson S. Guedes
mail/xmpp: guedes@guedesoft.net - skype: guediz
http://guedesoft.net - http://www.postgresql.org.br

2012/8/16 Dickson S. Guedes <listas@guedesoft.net>

2012/8/16 Fabrízio de Royes Mello <fabriziomello@gmail.com>:

The attached patch implement this feature:

CREATE SCHEMA [ IF NOT EXISTS ] schema_name [ AUTHORIZATION user_name ] [
schema_element [ ... ] ]
CREATE SCHEMA [ IF NOT EXISTS ] AUTHORIZATION user_name [ schema_element

[

... ] ]

Now, PostgreSQL don't trow an error if we use "IF NOT EXISTS" in "CREATE
SCHEMA" statement.

I started testing this, but I didn't see regression tests for it.
Could you write them?.

The attached patch contains regression tests for it.

Regards,

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL

2012/8/17 Fabrízio de Royes Mello <fabriziomello@gmail.com>

I started testing this, but I didn't see regression tests for it.

Could you write them?.

The attached patch contains regression tests for it.

Please, don't consider de last patch (v2) because I make a little mistake
on "create_schema.sgml" structure. The attached patch fix that.

Regards,

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL

Excerpts from Fabrízio de Royes Mello's message of vie ago 17 09:16:30 -0400 2012:

The attached patch contains regression tests for it.

I think you're missing support in copyfuncs.c and equalfuncs.c for the
new field in the node.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

2012/8/17 Alvaro Herrera <alvherre@2ndquadrant.com>

Excerpts from Fabrízio de Royes Mello's message of vie ago 17 09:16:30
-0400 2012:

The attached patch contains regression tests for it.

I think you're missing support in copyfuncs.c and equalfuncs.c for the
new field in the node.

You're completely right, thanks...

The attached patch add support for the new field in the node in
"copyfuncs.c" and "equalfuncs.c".

Regards,

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL

2012/8/17 Fabrízio de Royes Mello <fabriziomello@gmail.com>:

2012/8/17 Alvaro Herrera <alvherre@2ndquadrant.com>

Excerpts from Fabrízio de Royes Mello's message of vie ago 17 09:16:30
-0400 2012:

The attached patch contains regression tests for it.

I think you're missing support in copyfuncs.c and equalfuncs.c for the
new field in the node.

You're completely right, thanks...

The attached patch add support for the new field in the node in
"copyfuncs.c" and "equalfuncs.c".

Maybe I'm missing something but shouldn't it being handled in extension.c too?

[]s
--
Dickson S. Guedes
mail/xmpp: guedes@guedesoft.net - skype: guediz
http://guedesoft.net - http://www.postgresql.org.br

2012/8/17 Alvaro Herrera <alvherre@2ndquadrant.com>

Excerpts from Dickson S. Guedes's message of vie ago 17 10:37:25 -0400
2012:

2012/8/17 Fabrízio de Royes Mello <fabriziomello@gmail.com>:

2012/8/17 Alvaro Herrera <alvherre@2ndquadrant.com>

Excerpts from Fabrízio de Royes Mello's message of vie ago 17 09:16:30
-0400 2012:

The attached patch contains regression tests for it.

I think you're missing support in copyfuncs.c and equalfuncs.c for the
new field in the node.

You're completely right, thanks...

The attached patch add support for the new field in the node in
"copyfuncs.c" and "equalfuncs.c".

Maybe I'm missing something but shouldn't it being handled in

extension.c too?

Please be more explicit? I don't know what you mean.

Returning conversation to the list.

I think he talked about this piece of code:

diff --git a/src/backend/commands/extension.c
b/src/backend/commands/extension.c
index 8512cdb..e359a9c 100644
--- a/src/backend/commands/extension.c
+++ b/src/backend/commands/extension.c
@@ -1374,6 +1374,7 @@ CreateExtension(CreateExtensionStmt *stmt)
                        csstmt->schemaname = schemaName;
                        csstmt->authid = NULL;          /* will be created
by current user */
                        csstmt->schemaElts = NIL;
+                       csstmt->if_not_exists = false;
                        CreateSchemaCommand(csstmt, NULL);

/*

Regards,

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL

Hello,

I reviewed this v5 of patch:

- https://commitfest.postgresql.org/action/patch_view?id=907

The patch is small and implements a new syntax to CREATE SCHEMA
that allow the creation of a schema be skipped when IF NOT EXISTS is
used.

It was applied to 483c2c1071c45e275782d33d646c3018f02f9f94 with
two hunks offset, was compiled without errors or new warnings and
pass all tests, even the tests that covers the expected results for it
self.

The docs was updated with the information about the expected behavior.

I tested against ambiguity, i. e. creating a schema with name 'if' and got the
expected results when try to create it if not exists.

Two questions:

- Should this patch implements others INEs like ADD COLUMN IF NOT EXISTS?
- Should pg_dump or pg_restore support some kind of flag to use a
CREATE SCHEMA IF NOT EXISTS ... instead CREATE SCHEMA ...?

Regards,
--
Dickson S. Guedes
mail/xmpp: guedes@guedesoft.net -
skype/twitter: guediz ~ github.com/guedes
http://guedesoft.net ~ http://www.postgresql.org.br

2012/8/27 Dickson S. Guedes <listas@guedesoft.net>

[...]

Two questions:

- Should this patch implements others INEs like ADD COLUMN IF NOT EXISTS?

If this feature is important I believe we must implement it.

Exists several "CREATE" statements without "IF NOT EXISTS" option too, so
we can discuss more about it and I can implement it in this patch or in
another.

- Should pg_dump or pg_restore support some kind of flag to use a
CREATE SCHEMA IF NOT EXISTS ... instead CREATE SCHEMA ...?

I can't imagine a use case for this feature... can you explain more about
your idea?

Regards,

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL

On Aug 28, 2012, at 8:19 AM, Fabrízio de Royes Mello wrote:

- Should this patch implements others INEs like ADD COLUMN IF NOT EXISTS?

If this feature is important I believe we must implement it.

Exists several "CREATE" statements without "IF NOT EXISTS" option too, so we can discuss more about it and I can implement it in this patch or in another.

I don't think any of them have to be a part of *this* patch. There can be patches for each of them. Probably simpler that way.

But then, I am not like to code them, so WTF do I know? :-)

David

2012/8/28 David E. Wheeler <david@justatheory.com>:

On Aug 28, 2012, at 8:19 AM, Fabrízio de Royes Mello wrote:

- Should this patch implements others INEs like ADD COLUMN IF NOT EXISTS?

If this feature is important I believe we must implement it.

Exists several "CREATE" statements without "IF NOT EXISTS" option too, so we can discuss more about it and I can implement it in this patch or in another.

I don't think any of them have to be a part of *this* patch. There can be patches for each of them. Probably simpler that way.

Yes, agreed. Other implementations should be in their own distinct
patches. BTW, it could be interesting search the archive for past
discussions about CINE.

I changed the status of this patch to "Ready for Committer".

Regards,
--
Dickson S. Guedes
mail/xmpp: guedes@guedesoft.net -
skype/twitter: guediz ~ github.com/guedes
http://guedesoft.net ~ http://www.postgresql.org.br

"Dickson S. Guedes" <listas@guedesoft.net> writes:

I reviewed this v5 of patch:

- https://commitfest.postgresql.org/action/patch_view?id=907

The patch is small and implements a new syntax to CREATE SCHEMA
that allow the creation of a schema be skipped when IF NOT EXISTS is
used.

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

This is somewhat connected to our previous arguments about whether CINE
ought to try to make any guarantees about whether the existing object is
at all like the object that would have been created if the statement
executed successfully. I realize that the existing statements that have
an INE option have entirely blown off this concern, but I still think
that it's a question that ought to be considered, particularly for
objects that have separately creatable sub-structure.

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.

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.

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.

regards, tom lane

On Sep 21, 2012, at 10:59 AM, Tom Lane <tgl@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.

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.

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.

Best,

David