Re: [HACKERS] CREATE IF NOT EXISTS INDEX
On Sat, Nov 1, 2014 at 1:56 AM, Fabrízio de Royes Mello fabriziome...@gmail.com wrote: On Fri, Oct 31, 2014 at 2:46 PM, Adam Brightwell adam.brightw...@crunchydatasolutions.com wrote: All, FWIW, I've cleanly applied v8 of this patch to master (252e652) and check-world was successful. I also successfully ran through a few manual test cases. Thanks for your review! Applied. Thanks! Regards, -- Fujii Masao -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CREATE IF NOT EXISTS INDEX
All, FWIW, I've cleanly applied v8 of this patch to master (252e652) and check-world was successful. I also successfully ran through a few manual test cases. -Adam -- Adam Brightwell - adam.brightw...@crunchydatasolutions.com Database Engineer - www.crunchydatasolutions.com
Re: [HACKERS] CREATE IF NOT EXISTS INDEX
On Fri, Oct 31, 2014 at 2:46 PM, Adam Brightwell adam.brightw...@crunchydatasolutions.com wrote: All, FWIW, I've cleanly applied v8 of this patch to master (252e652) and check-world was successful. I also successfully ran through a few manual test cases. Thanks for your review! Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Timbira: http://www.timbira.com.br Blog: http://fabriziomello.github.io Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello Github: http://github.com/fabriziomello
Re: [HACKERS] CREATE IF NOT EXISTS INDEX
On Tue, Oct 7, 2014 at 2:42 AM, Fabrízio de Royes Mello fabriziome...@gmail.com wrote: On Mon, Oct 6, 2014 at 11:13 AM, Marti Raudsepp ma...@juffo.org wrote: On Mon, Oct 6, 2014 at 4:27 PM, Fabrízio de Royes Mello fabriziome...@gmail.com wrote: create_index_if_not_exists_v7.patch Looks good to me. Marking ready for committer. Thanks. The patch looks good to me except the following minor comments. + termliteralIF NOT EXISTS/literal/term It's better to place this after the paragraph of CONCURRENTLY for the consistency with the syntax. +Do not throw an error if the index already exists. I think that this should be Do not throw an error if a relation with the same name already exists. +Index name is required when IF NOT EXISTS is specified. IF NOT EXISTS should be enclosed with literal tag. @@ -60,7 +60,8 @@ extern Oid index_create(Relation heapRelation, bool allow_system_table_mods, bool skip_build, bool concurrent, - bool is_internal); + bool is_internal, + bool if_not_exists); You forgot to add the comment of if_not_exists argument into the top of index_create function. +boolif_not_exists;/* just do nothing if index already exists */ You forgot to add the trailing ? at the above comment. There are similar comments in parsenodes.h, and they have such ?. Regards, -- Fujii Masao -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CREATE IF NOT EXISTS INDEX
On Thu, Oct 30, 2014 at 12:11 PM, Fujii Masao masao.fu...@gmail.com wrote:
On Tue, Oct 7, 2014 at 2:42 AM, Fabrízio de Royes Mello
fabriziome...@gmail.com wrote:
On Mon, Oct 6, 2014 at 11:13 AM, Marti Raudsepp ma...@juffo.org wrote:
On Mon, Oct 6, 2014 at 4:27 PM, Fabrízio de Royes Mello
fabriziome...@gmail.com wrote:
create_index_if_not_exists_v7.patch
Looks good to me. Marking ready for committer.
Thanks.
The patch looks good to me except the following minor comments.
+ termliteralIF NOT EXISTS/literal/term
It's better to place this after the paragraph of CONCURRENTLY
for the consistency with the syntax.
Fixed.
+Do not throw an error if the index already exists.
I think that this should be
Do not throw an error if a relation with the same name already exists.
Fixed.
+Index name is required when IF NOT EXISTS is specified.
IF NOT EXISTS should be enclosed with literal tag.
Fixed.
@@ -60,7 +60,8 @@ extern Oid index_create(Relation heapRelation,
bool allow_system_table_mods,
bool skip_build,
bool concurrent,
- bool is_internal);
+ bool is_internal,
+ bool if_not_exists);
You forgot to add the comment of if_not_exists argument into the top of
index_create function.
Fixed.
+boolif_not_exists;/* just do nothing if index already
exists */
You forgot to add the trailing ? at the above comment. There are similar
comments in parsenodes.h, and they have such ?.
Fixed.
Thanks for your review!
Regards,
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
Timbira: http://www.timbira.com.br
Blog: http://fabriziomello.github.io
Linkedin: http://br.linkedin.com/in/fabriziomello
Twitter: http://twitter.com/fabriziomello
Github: http://github.com/fabriziomello
diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgml
index 43df32f..0414c26 100644
--- a/doc/src/sgml/ref/create_index.sgml
+++ b/doc/src/sgml/ref/create_index.sgml
@@ -21,7 +21,7 @@ PostgreSQL documentation
refsynopsisdiv
synopsis
-CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ replaceable class=parametername/replaceable ] ON replaceable class=parametertable_name/replaceable [ USING replaceable class=parametermethod/replaceable ]
+CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] replaceable class=parametername/replaceable ] ON replaceable class=parametertable_name/replaceable [ USING replaceable class=parametermethod/replaceable ]
( { replaceable class=parametercolumn_name/replaceable | ( replaceable class=parameterexpression/replaceable ) } [ COLLATE replaceable class=parametercollation/replaceable ] [ replaceable class=parameteropclass/replaceable ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
[ WITH ( replaceable class=PARAMETERstorage_parameter/replaceable = replaceable class=PARAMETERvalue/replaceable [, ... ] ) ]
[ TABLESPACE replaceable class=parametertablespace_name/replaceable ]
@@ -97,7 +97,6 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ replaceable class=parametername/
refsect1
titleParameters/title
-variablelist
varlistentry
termliteralUNIQUE/literal/term
listitem
@@ -126,6 +125,19 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ replaceable class=parametername/
/listitem
/varlistentry
+variablelist
+ varlistentry
+ termliteralIF NOT EXISTS/literal/term
+ listitem
+ para
+Do not throw an error if a relation with the same name already exists.
+A notice is issued in this case. Note that there is no guarantee that
+the existing index is anything like the one that would have been created.
+Index name is required when literalIF NOT EXISTS/literal is specified.
+ /para
+ /listitem
+ /varlistentry
+
varlistentry
termreplaceable class=parametername/replaceable/term
listitem
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 01ed880..c886f74 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -674,6 +674,8 @@ UpdateIndexRelation(Oid indexoid,
* will be marked invalid and the caller must take additional steps
* to fix it up.
* is_internal: if true, post creation hook for new index
+ * if_not_exists: if true, issue a notice instead an error if the index with
+ * the same name already exists
*
* Returns the OID of the created index.
*/
@@ -697,7 +699,8 @@ index_create(Relation heapRelation,
bool allow_system_table_mods,
bool skip_build,
bool concurrent,
- bool is_internal)
+ bool is_internal,
+ bool if_not_exists)
{
Oid heapRelationId = RelationGetRelid(heapRelation);
Relation pg_class;
@@ -773,10 +776,22 @@ index_create(Relation heapRelation,
elog(ERROR, shared relations must be placed in pg_global tablespace);
if (get_relname_relid(indexRelationName,
Re: [HACKERS] CREATE IF NOT EXISTS INDEX
On Mon, Oct 6, 2014 at 5:12 AM, Marti Raudsepp ma...@juffo.org wrote: On Mon, Oct 6, 2014 at 4:17 AM, Fabrízio de Royes Mello fabriziome...@gmail.com wrote: CREATE INDEX ... [ IF NOT EXISTS [ name ] ] ON ... I think this one is wrong now. I see now, I think you meant: CREATE INDEX ... [ [ IF NOT EXISTS ] name ] ON ... If yes, +1 for this, there's no redundancy any more. Regards, Marti -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CREATE IF NOT EXISTS INDEX
On Mon, Oct 6, 2014 at 4:49 AM, Marti Raudsepp ma...@juffo.org wrote:
On Mon, Oct 6, 2014 at 5:12 AM, Marti Raudsepp ma...@juffo.org wrote:
On Mon, Oct 6, 2014 at 4:17 AM, Fabrízio de Royes Mello
fabriziome...@gmail.com wrote:
CREATE INDEX ... [ IF NOT EXISTS [ name ] ] ON ...
I think this one is wrong now.
I see now, I think you meant:
CREATE INDEX ... [ [ IF NOT EXISTS ] name ] ON ...
If yes, +1 for this, there's no redundancy any more.
You are correct...
Regards,
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
Timbira: http://www.timbira.com.br
Blog: http://fabriziomello.github.io
Linkedin: http://br.linkedin.com/in/fabriziomello
Twitter: http://twitter.com/fabriziomello
Github: http://github.com/fabriziomello
diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgml
index e469b17..ecebcbf 100644
--- a/doc/src/sgml/ref/create_index.sgml
+++ b/doc/src/sgml/ref/create_index.sgml
@@ -21,7 +21,7 @@ PostgreSQL documentation
refsynopsisdiv
synopsis
-CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ replaceable class=parametername/replaceable ] ON replaceable class=parametertable_name/replaceable [ USING replaceable class=parametermethod/replaceable ]
+CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] replaceable class=parametername/replaceable ] ON replaceable class=parametertable_name/replaceable [ USING replaceable class=parametermethod/replaceable ]
( { replaceable class=parametercolumn_name/replaceable | ( replaceable class=parameterexpression/replaceable ) } [ COLLATE replaceable class=parametercollation/replaceable ] [ replaceable class=parameteropclass/replaceable ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
[ WITH ( replaceable class=PARAMETERstorage_parameter/replaceable = replaceable class=PARAMETERvalue/replaceable [, ... ] ) ]
[ TABLESPACE replaceable class=parametertablespace_name/replaceable ]
@@ -99,6 +99,18 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ replaceable class=parametername/
variablelist
varlistentry
+ termliteralIF NOT EXISTS/literal/term
+ listitem
+ para
+Do not throw an error if the index already exists. A notice is issued
+in this case. Note that there is no guarantee that the existing index
+is anything like the one that would have been created.
+Index name is required when IF NOT EXISTS is specified.
+ /para
+ /listitem
+ /varlistentry
+
+ varlistentry
termliteralUNIQUE/literal/term
listitem
para
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index ee10594..8905e30 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -697,7 +697,8 @@ index_create(Relation heapRelation,
bool allow_system_table_mods,
bool skip_build,
bool concurrent,
- bool is_internal)
+ bool is_internal,
+ bool if_not_exists)
{
Oid heapRelationId = RelationGetRelid(heapRelation);
Relation pg_class;
@@ -773,10 +774,22 @@ index_create(Relation heapRelation,
elog(ERROR, shared relations must be placed in pg_global tablespace);
if (get_relname_relid(indexRelationName, namespaceId))
+ {
+ if (if_not_exists)
+ {
+ ereport(NOTICE,
+ (errcode(ERRCODE_DUPLICATE_TABLE),
+ errmsg(relation \%s\ already exists, skipping,
+ indexRelationName)));
+ heap_close(pg_class, RowExclusiveLock);
+ return InvalidOid;
+ }
+
ereport(ERROR,
(errcode(ERRCODE_DUPLICATE_TABLE),
errmsg(relation \%s\ already exists,
indexRelationName)));
+ }
/*
* construct tuple descriptor for index tuples
diff --git a/src/backend/catalog/toasting.c b/src/backend/catalog/toasting.c
index 160f006..5ef6dcc 100644
--- a/src/backend/catalog/toasting.c
+++ b/src/backend/catalog/toasting.c
@@ -342,7 +342,7 @@ create_toast_table(Relation rel, Oid toastOid, Oid toastIndexOid,
rel-rd_rel-reltablespace,
collationObjectId, classObjectId, coloptions, (Datum) 0,
true, false, false, false,
- true, false, false, true);
+ true, false, false, true, false);
heap_close(toast_rel, NoLock);
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index 8a1cb4b..a03773b 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -610,7 +610,14 @@ DefineIndex(Oid relationId,
stmt-isconstraint, stmt-deferrable, stmt-initdeferred,
allowSystemTableMods,
skip_build || stmt-concurrent,
- stmt-concurrent, !check_rights);
+ stmt-concurrent, !check_rights,
+ stmt-if_not_exists);
+
+ if (!OidIsValid(indexRelationId))
+ {
+ heap_close(rel, NoLock);
+ return indexRelationId;
+ }
/* Add any requested comment */
if (stmt-idxcomment != NULL)
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 225756c..39b55db 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -2907,6 +2907,7 @@
Re: [HACKERS] CREATE IF NOT EXISTS INDEX
On Mon, Oct 6, 2014 at 4:27 PM, Fabrízio de Royes Mello fabriziome...@gmail.com wrote: create_index_if_not_exists_v7.patch Looks good to me. Marking ready for committer. If you have any feedback about my reviews, I would gladly hear it. I'm quite new to this. PS: You seem to be submitting many patches, but have you reviewed any recently? See: https://wiki.postgresql.org/wiki/Submitting_a_Patch#Mutual_Review_Offset_Obligations Each patch submitter to a CommitFest is expected to review at least one other patch Regards, Marti -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CREATE IF NOT EXISTS INDEX
On Mon, Oct 6, 2014 at 11:13 AM, Marti Raudsepp ma...@juffo.org wrote: On Mon, Oct 6, 2014 at 4:27 PM, Fabrízio de Royes Mello fabriziome...@gmail.com wrote: create_index_if_not_exists_v7.patch Looks good to me. Marking ready for committer. Thanks. If you have any feedback about my reviews, I would gladly hear it. I'm quite new to this. Was great... PS: You seem to be submitting many patches, but have you reviewed any recently? See: https://wiki.postgresql.org/wiki/Submitting_a_Patch#Mutual_Review_Offset_Obligations Each patch submitter to a CommitFest is expected to review at least one other patch Yes, I know it... I'll dedicate more time to help on reviews too... It's very important and fundamental activity. Thanks for reminder. Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Timbira: http://www.timbira.com.br Blog: http://fabriziomello.github.io Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello Github: http://github.com/fabriziomello
Re: [HACKERS] CREATE IF NOT EXISTS INDEX
On Fri, Oct 3, 2014 at 7:25 PM, Fabrízio de Royes Mello fabriziome...@gmail.com wrote: I agree with your grammar change. Cool. The version 5 (attached) contains all discussed until now. From documentation: CREATE INDEX ... [ IF NOT EXISTS name | name ] ON ... Maybe I'm just slow, but it took me a few minutes to understand what this means. :) I would add a human-language explanation to IF NOT EXISTS description: Index name is required when IF NOT EXISTS is specified You have resurrected this bit again, which now conflicts with git master... - write_msg(NULL, reading row-security enabled for table \%s\, + write_msg(NULL, reading row-security enabled for table \%s\\n, n-concurrent = $4; + n-if_not_exists = false; n-idxname = $5; Minor stylistic thing: now that this is a constant, I would move it to the end together with other constant assignments, and follow the struct's field ordering (in both code paths): n-isconstraint = false; n-deferrable = false; n-initdeferred = false; n-if_not_exists = false; Regards, Marti -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CREATE IF NOT EXISTS INDEX
On Sun, Oct 5, 2014 at 9:52 AM, Marti Raudsepp ma...@juffo.org wrote:
The version 5 (attached) contains all discussed until now.
From documentation:
CREATE INDEX ... [ IF NOT EXISTS name | name ] ON ...
Maybe I'm just slow, but it took me a few minutes to understand what
this means. :)
Well, I try to show that IF NOT EXISTS require the name. Is this wrong?
Anyway I fixed that way:
CREATE INDEX ... [ IF NOT EXISTS [ name ] ] ON ...
Maybe is better than the last... what you think?
I would add a human-language explanation to IF NOT EXISTS description:
Index name is required when IF NOT EXISTS is specified
Ok.
You have resurrected this bit again, which now conflicts with git
master...
- write_msg(NULL, reading row-security enabled for table \%s\,
+ write_msg(NULL, reading row-security enabled for table \%s\\n,
Ohh... sorry... again... my mistake :-( now all was fixed.
n-concurrent = $4;
+ n-if_not_exists = false;
n-idxname = $5;
Minor stylistic thing: now that this is a constant, I would move it to
the end together with other constant assignments, and follow the
struct's field ordering (in both code paths):
n-isconstraint = false;
n-deferrable = false;
n-initdeferred = false;
n-if_not_exists = false;
Fixed.
Thanks again!
Regards,
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
Timbira: http://www.timbira.com.br
Blog: http://fabriziomello.github.io
Linkedin: http://br.linkedin.com/in/fabriziomello
Twitter: http://twitter.com/fabriziomello
Github: http://github.com/fabriziomello
diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgml
index e469b17..9b3f3a3 100644
--- a/doc/src/sgml/ref/create_index.sgml
+++ b/doc/src/sgml/ref/create_index.sgml
@@ -21,7 +21,7 @@ PostgreSQL documentation
refsynopsisdiv
synopsis
-CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ replaceable class=parametername/replaceable ] ON replaceable class=parametertable_name/replaceable [ USING replaceable class=parametermethod/replaceable ]
+CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ IF NOT EXISTS [ replaceable class=parametername/replaceable ] ] ON replaceable class=parametertable_name/replaceable [ USING replaceable class=parametermethod/replaceable ]
( { replaceable class=parametercolumn_name/replaceable | ( replaceable class=parameterexpression/replaceable ) } [ COLLATE replaceable class=parametercollation/replaceable ] [ replaceable class=parameteropclass/replaceable ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
[ WITH ( replaceable class=PARAMETERstorage_parameter/replaceable = replaceable class=PARAMETERvalue/replaceable [, ... ] ) ]
[ TABLESPACE replaceable class=parametertablespace_name/replaceable ]
@@ -99,6 +99,18 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ replaceable class=parametername/
variablelist
varlistentry
+ termliteralIF NOT EXISTS/literal/term
+ listitem
+ para
+Do not throw an error if the index already exists. A notice is issued
+in this case. Note that there is no guarantee that the existing index
+is anything like the one that would have been created.
+Index name is required when IF NOT EXISTS is specified.
+ /para
+ /listitem
+ /varlistentry
+
+ varlistentry
termliteralUNIQUE/literal/term
listitem
para
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index ee10594..8905e30 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -697,7 +697,8 @@ index_create(Relation heapRelation,
bool allow_system_table_mods,
bool skip_build,
bool concurrent,
- bool is_internal)
+ bool is_internal,
+ bool if_not_exists)
{
Oid heapRelationId = RelationGetRelid(heapRelation);
Relation pg_class;
@@ -773,10 +774,22 @@ index_create(Relation heapRelation,
elog(ERROR, shared relations must be placed in pg_global tablespace);
if (get_relname_relid(indexRelationName, namespaceId))
+ {
+ if (if_not_exists)
+ {
+ ereport(NOTICE,
+ (errcode(ERRCODE_DUPLICATE_TABLE),
+ errmsg(relation \%s\ already exists, skipping,
+ indexRelationName)));
+ heap_close(pg_class, RowExclusiveLock);
+ return InvalidOid;
+ }
+
ereport(ERROR,
(errcode(ERRCODE_DUPLICATE_TABLE),
errmsg(relation \%s\ already exists,
indexRelationName)));
+ }
/*
* construct tuple descriptor for index tuples
diff --git a/src/backend/catalog/toasting.c b/src/backend/catalog/toasting.c
index 160f006..5ef6dcc 100644
--- a/src/backend/catalog/toasting.c
+++ b/src/backend/catalog/toasting.c
@@ -342,7 +342,7 @@ create_toast_table(Relation rel, Oid toastOid, Oid toastIndexOid,
rel-rd_rel-reltablespace,
collationObjectId, classObjectId, coloptions, (Datum) 0,
true, false, false, false,
- true, false, false, true);
+ true, false, false, true, false);
heap_close(toast_rel, NoLock);
diff --git
Re: [HACKERS] CREATE IF NOT EXISTS INDEX
On Mon, Oct 6, 2014 at 4:17 AM, Fabrízio de Royes Mello fabriziome...@gmail.com wrote: On Sun, Oct 5, 2014 at 9:52 AM, Marti Raudsepp ma...@juffo.org wrote: CREATE INDEX ... [ IF NOT EXISTS name | name ] ON ... Maybe I'm just slow, but it took me a few minutes to understand what this means. :) Well, I try to show that IF NOT EXISTS require the name. Is this wrong? No, I'm sorry, you misunderstood me. It was totally correct before, it just wasn't easy to understand at first. CREATE INDEX ... [ IF NOT EXISTS [ name ] ] ON ... I think this one is wrong now. It suggests these are valid syntaxes: CREATE INDEX ... ON ... CREATE INDEX ... IF NOT EXISTS ON ... -- wrong CREATE INDEX ... IF NOT EXISTS name ON ... Regards, Marti -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CREATE IF NOT EXISTS INDEX
On Fri, Oct 3, 2014 at 5:26 AM, Marti Raudsepp ma...@juffo.org wrote:
On Fri, Oct 3, 2014 at 6:25 AM, Fabrízio de Royes Mello
fabriziome...@gmail.com wrote:
Documentation: I would prefer if the explanation were consistent with
Do not throw an error if the index already exists. A notice is issued
in
this case.
Fixed in that way. Ok?
And also Note that there is no guarantee that the existing index is
anything like the one that would have been created.
Fixed.
I think ERRCODE_SYNTAX_ERROR makes more sense, it's something that we
decided we *don't want* to support.
I don't think so. It's the same as CREATE SCHEMA IF NOT EXISTS that not
support to include schema elements.
IMO that's wrong too, the CREATE SCHEMA documentation doesn't list it
as valid syntax.
But now that you split the syntax in two, you can simply replace
opt_index_name with index_name and it will naturally cause a
syntax error without the need for an if(). What do you think?
Patch attached, which applies on top of your v4 patch.
Fine. Thanks.
On Fri, Oct 3, 2014 at 6:29 AM, Fabrízio de Royes Mello
fabriziome...@gmail.com wrote:
I would also move this check to after all the attributes have been
assigned, rather than splitting the assignments in half.
Why? If you see other places in gram.y it's a common usage...
Looks cleaner to me: first input all the fields, then validate. And
there are examples like this too, like COPY select_with_parens. But
this is moot now, if you agree with my grammar change.
I agree with your grammar change.
On Fri, Oct 3, 2014 at 6:35 AM, Fabrízio de Royes Mello
fabriziome...@gmail.com wrote:
And just to remember please add your review to commitfest
Thanks for reminding, I always forget to update the CommitFest... :(
You're welcome.
The version 5 (attached) contains all discussed until now.
Regards.
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
Timbira: http://www.timbira.com.br
Blog: http://fabriziomello.github.io
Linkedin: http://br.linkedin.com/in/fabriziomello
Twitter: http://twitter.com/fabriziomello
Github: http://github.com/fabriziomello
diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgml
index e469b17..2dfe2f4 100644
--- a/doc/src/sgml/ref/create_index.sgml
+++ b/doc/src/sgml/ref/create_index.sgml
@@ -21,7 +21,7 @@ PostgreSQL documentation
refsynopsisdiv
synopsis
-CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ replaceable class=parametername/replaceable ] ON replaceable class=parametertable_name/replaceable [ USING replaceable class=parametermethod/replaceable ]
+CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ IF NOT EXISTS replaceable class=parametername/replaceable | replaceable class=parametername/replaceable ] ON replaceable class=parametertable_name/replaceable [ USING replaceable class=parametermethod/replaceable ]
( { replaceable class=parametercolumn_name/replaceable | ( replaceable class=parameterexpression/replaceable ) } [ COLLATE replaceable class=parametercollation/replaceable ] [ replaceable class=parameteropclass/replaceable ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
[ WITH ( replaceable class=PARAMETERstorage_parameter/replaceable = replaceable class=PARAMETERvalue/replaceable [, ... ] ) ]
[ TABLESPACE replaceable class=parametertablespace_name/replaceable ]
@@ -99,6 +99,17 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ replaceable class=parametername/
variablelist
varlistentry
+ termliteralIF NOT EXISTS/literal/term
+ listitem
+ para
+Do not throw an error if the index already exists. A notice is issued
+in this case. Note that there is no guarantee that the existing index
+is anything like the one that would have been created.
+ /para
+ /listitem
+ /varlistentry
+
+ varlistentry
termliteralUNIQUE/literal/term
listitem
para
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index ee10594..8905e30 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -697,7 +697,8 @@ index_create(Relation heapRelation,
bool allow_system_table_mods,
bool skip_build,
bool concurrent,
- bool is_internal)
+ bool is_internal,
+ bool if_not_exists)
{
Oid heapRelationId = RelationGetRelid(heapRelation);
Relation pg_class;
@@ -773,10 +774,22 @@ index_create(Relation heapRelation,
elog(ERROR, shared relations must be placed in pg_global tablespace);
if (get_relname_relid(indexRelationName, namespaceId))
+ {
+ if (if_not_exists)
+ {
+ ereport(NOTICE,
+ (errcode(ERRCODE_DUPLICATE_TABLE),
+ errmsg(relation \%s\ already exists, skipping,
+ indexRelationName)));
+ heap_close(pg_class, RowExclusiveLock);
+ return InvalidOid;
+ }
+
ereport(ERROR,
(errcode(ERRCODE_DUPLICATE_TABLE),
errmsg(relation \%s\ already exists,
indexRelationName)));
+ }
/*
* construct tuple
Re: [HACKERS] CREATE IF NOT EXISTS INDEX
On Wed, Oct 1, 2014 at 2:42 PM, Fabrízio de Royes Mello
fabriziome...@gmail.com wrote:
So, what's the correct/best grammar?
CREATE [ IF NOT EXISTS ] [ UNIQUE ] INDEX index_name
or
CREATE [ UNIQUE ] INDEX [ IF NOT EXISTS ] index_name
I've elected myself as the reviewer for this patch. Here are some
preliminary comments...
I agree with José. The 2nd is more consistent given the other syntaxes:
CREATE { TABLE | SCHEMA | EXTENSION | ... } IF NOT EXISTS name ...
It's also compatible with SQLite's grammar:
https://www.sqlite.org/lang_createindex.html
Do we want to enforce an order on the keywords or allow both?
CREATE INDEX IF NOT EXISTS CONCURRENTLY foo ...
CREATE INDEX CONCURRENTLY IF NOT EXISTS foo ...
It's probably very rare to use both keywords at the same time, so I'd
prefer only the 2nd, unless someone else chimes in.
Documentation: I would prefer if the explanation were consistent with
the description for ALTER TABLE/EXTENSION; just copy it and replace
relation with index.
+ ereport(NOTICE,
+ (errcode(ERRCODE_DUPLICATE_TABLE),
+ errmsg(relation \%s\ already exists, skipping,
+ indexRelationName)));
1. Clearly relation should be index.
2. Use ERRCODE_DUPLICATE_OBJECT not TABLE
+ if (n-if_not_exists n-idxname == NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg(IF NOT EXISTS requires that you
name the index.),
I think ERRCODE_SYNTAX_ERROR makes more sense, it's something that we
decided we *don't want* to support.
- write_msg(NULL, reading row-security enabled for table \%s\,
+ write_msg(NULL, reading row-security enabled for table \%s\\n,
???
Regards,
Marti
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CREATE IF NOT EXISTS INDEX
On Fri, Oct 3, 2014 at 2:15 AM, Marti Raudsepp ma...@juffo.org wrote: + ereport(NOTICE, + (errcode(ERRCODE_DUPLICATE_TABLE), + errmsg(relation \%s\ already exists, skipping, + indexRelationName))); 1. Clearly relation should be index. 2. Use ERRCODE_DUPLICATE_OBJECT not TABLE My bad, this code is OK. The current code already uses relation and TABLE elsewhere because indexes share the same namespace with tables. + /* + * Throw an exception when IF NOT EXISTS is used without a named + * index + */ I'd say without an index name. And the line goes beyond 80 characters wide. I would also move this check to after all the attributes have been assigned, rather than splitting the assignments in half. Regards, Marti -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CREATE IF NOT EXISTS INDEX
On Thu, Oct 2, 2014 at 8:15 PM, Marti Raudsepp ma...@juffo.org wrote:
On Wed, Oct 1, 2014 at 2:42 PM, Fabrízio de Royes Mello
fabriziome...@gmail.com wrote:
So, what's the correct/best grammar?
CREATE [ IF NOT EXISTS ] [ UNIQUE ] INDEX index_name
or
CREATE [ UNIQUE ] INDEX [ IF NOT EXISTS ] index_name
I've elected myself as the reviewer for this patch. Here are some
preliminary comments...
Thanks...
I agree with José. The 2nd is more consistent given the other syntaxes:
CREATE { TABLE | SCHEMA | EXTENSION | ... } IF NOT EXISTS name ...
It's also compatible with SQLite's grammar:
https://www.sqlite.org/lang_createindex.html
Do we want to enforce an order on the keywords or allow both?
CREATE INDEX IF NOT EXISTS CONCURRENTLY foo ...
CREATE INDEX CONCURRENTLY IF NOT EXISTS foo ...
It's probably very rare to use both keywords at the same time, so I'd
prefer only the 2nd, unless someone else chimes in.
Fixed.
Documentation: I would prefer if the explanation were consistent with
the description for ALTER TABLE/EXTENSION; just copy it and replace
relation with index.
Well, I'm not native English so I tend to agree with you.
Do not throw an error if the index already exists. A notice is issued in
this case.
Fixed in that way. Ok?
+ ereport(NOTICE,
+ (errcode(ERRCODE_DUPLICATE_TABLE),
+ errmsg(relation \%s\ already exists, skipping,
+ indexRelationName)));
1. Clearly relation should be index.
2. Use ERRCODE_DUPLICATE_OBJECT not TABLE
Sorry, but I'm not with you. I just copy the original error message and add
, skipping at the end.
788 ereport(ERROR,
789 (errcode(ERRCODE_DUPLICATE_TABLE),
790 errmsg(relation \%s\ already exists,
791 indexRelationName)));
+ if (n-if_not_exists n-idxname == NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg(IF NOT EXISTS requires that you
name the index.),
I think ERRCODE_SYNTAX_ERROR makes more sense, it's something that we
decided we *don't want* to support.
I don't think so. It's the same as CREATE SCHEMA IF NOT EXISTS that not
support to include schema elements.
Other opinions?
- write_msg(NULL, reading row-security enabled for table \%s\,
+ write_msg(NULL, reading row-security enabled for table \%s\\n,
???
Sorry, my mistake. I merged with a wrong local branch. Fixed.
Regards,
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
Timbira: http://www.timbira.com.br
Blog: http://fabriziomello.github.io
Linkedin: http://br.linkedin.com/in/fabriziomello
Twitter: http://twitter.com/fabriziomello
Github: http://github.com/fabriziomello
diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgml
index e469b17..06c2567 100644
--- a/doc/src/sgml/ref/create_index.sgml
+++ b/doc/src/sgml/ref/create_index.sgml
@@ -21,7 +21,7 @@ PostgreSQL documentation
refsynopsisdiv
synopsis
-CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ replaceable class=parametername/replaceable ] ON replaceable class=parametertable_name/replaceable [ USING replaceable class=parametermethod/replaceable ]
+CREATE [ IF NOT EXISTS ] [ UNIQUE ] INDEX [ CONCURRENTLY ] [ replaceable class=parametername/replaceable ] ON replaceable class=parametertable_name/replaceable [ USING replaceable class=parametermethod/replaceable ]
( { replaceable class=parametercolumn_name/replaceable | ( replaceable class=parameterexpression/replaceable ) } [ COLLATE replaceable class=parametercollation/replaceable ] [ replaceable class=parameteropclass/replaceable ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
[ WITH ( replaceable class=PARAMETERstorage_parameter/replaceable = replaceable class=PARAMETERvalue/replaceable [, ... ] ) ]
[ TABLESPACE replaceable class=parametertablespace_name/replaceable ]
@@ -99,6 +99,16 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ replaceable class=parametername/
variablelist
varlistentry
+ termliteralIF NOT EXISTS/literal/term
+ listitem
+ para
+Do not throw an error if the index already exists. A notice is issued
+in this case.
+ /para
+ /listitem
+ /varlistentry
+
+ varlistentry
termliteralUNIQUE/literal/term
listitem
para
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index ee10594..8905e30 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -697,7 +697,8 @@ index_create(Relation heapRelation,
bool allow_system_table_mods,
bool skip_build,
bool concurrent,
- bool is_internal)
+ bool is_internal,
+ bool if_not_exists)
{
Oid heapRelationId = RelationGetRelid(heapRelation);
Relation pg_class;
@@ -773,10 +774,22 @@ index_create(Relation heapRelation,
elog(ERROR, shared relations must be placed in
Re: [HACKERS] CREATE IF NOT EXISTS INDEX
On Thu, Oct 2, 2014 at 9:55 PM, Marti Raudsepp ma...@juffo.org wrote: On Fri, Oct 3, 2014 at 2:15 AM, Marti Raudsepp ma...@juffo.org wrote: + ereport(NOTICE, + (errcode(ERRCODE_DUPLICATE_TABLE), + errmsg(relation \%s\ already exists, skipping, + indexRelationName))); 1. Clearly relation should be index. 2. Use ERRCODE_DUPLICATE_OBJECT not TABLE My bad, this code is OK. The current code already uses relation and TABLE elsewhere because indexes share the same namespace with tables. Ok. I reply it in other message. + /* + * Throw an exception when IF NOT EXISTS is used without a named + * index + */ I'd say without an index name. And the line goes beyond 80 characters wide. Fixed and send in other reply... I would also move this check to after all the attributes have been assigned, rather than splitting the assignments in half. Why? If you see other places in gram.y it's a common usage... Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Timbira: http://www.timbira.com.br Blog: http://fabriziomello.github.io Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello Github: http://github.com/fabriziomello
Re: [HACKERS] CREATE IF NOT EXISTS INDEX
On Fri, Oct 3, 2014 at 12:29 AM, Fabrízio de Royes Mello fabriziome...@gmail.com wrote: On Thu, Oct 2, 2014 at 9:55 PM, Marti Raudsepp ma...@juffo.org wrote: On Fri, Oct 3, 2014 at 2:15 AM, Marti Raudsepp ma...@juffo.org wrote: + ereport(NOTICE, + (errcode(ERRCODE_DUPLICATE_TABLE), + errmsg(relation \%s\ already exists, skipping, + indexRelationName))); 1. Clearly relation should be index. 2. Use ERRCODE_DUPLICATE_OBJECT not TABLE My bad, this code is OK. The current code already uses relation and TABLE elsewhere because indexes share the same namespace with tables. Ok. I reply it in other message. + /* + * Throw an exception when IF NOT EXISTS is used without a named + * index + */ I'd say without an index name. And the line goes beyond 80 characters wide. Fixed and send in other reply... I would also move this check to after all the attributes have been assigned, rather than splitting the assignments in half. Why? If you see other places in gram.y it's a common usage... And just to remember please add your review to commitfest [1] and change the patch status to Waiting on Author when the author needs to fix/change something. Thanks for your review! Regards, [1] https://commitfest.postgresql.org/action/patch_view?id=1584 -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Timbira: http://www.timbira.com.br Blog: http://fabriziomello.github.io Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello Github: http://github.com/fabriziomello
Re: [HACKERS] CREATE IF NOT EXISTS INDEX
On Wed, Oct 1, 2014 at 7:57 AM, José Luis Tallón jltal...@adv-solutions.net
wrote:
[snip]
Please excuse my jumping in, but the EXPECTED syntax is:
CREATE INDEX IF NOT EXISTS .
whereas your current patch implements:
CREATE [IF NOT EXISTS] INDEX
if I'm reading the grammar correctly.
I think it's not wrong. Look at other CINE that already implemented [1] [2].
But CINE for CREATE TABLE is like your proposal [3] :
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF
NOT EXISTS ] table_name ...
So, what's the correct/best grammar?
CREATE [ IF NOT EXISTS ] [ UNIQUE ] INDEX index_name
or
CREATE [ UNIQUE ] INDEX [ IF NOT EXISTS ] index_name
I guess it would be most interesting to implement this minor change for
the next version of the patch. Please do remember to update the
documentation accordingly.
I will...
By the way, you also forgot to remove a previous patch implementing
namespace_nameDOTrelation_name for RLS messages. Maybe a rebase is
needed?
Sorry... my mistake. Fix attached.
Regards,
[1] http://www.postgresql.org/docs/devel/static/sql-createschema.html
[2] http://www.postgresql.org/docs/devel/static/sql-createsequence.html
[3] http://www.postgresql.org/docs/devel/static/sql-createtable.html
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
Timbira: http://www.timbira.com.br
Blog: http://fabriziomello.github.io
Linkedin: http://br.linkedin.com/in/fabriziomello
Twitter: http://twitter.com/fabriziomello
Github: http://github.com/fabriziomello
diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgml
index e469b17..7886729 100644
--- a/doc/src/sgml/ref/create_index.sgml
+++ b/doc/src/sgml/ref/create_index.sgml
@@ -21,7 +21,7 @@ PostgreSQL documentation
refsynopsisdiv
synopsis
-CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ replaceable class=parametername/replaceable ] ON replaceable class=parametertable_name/replaceable [ USING replaceable class=parametermethod/replaceable ]
+CREATE [ IF NOT EXISTS ] [ UNIQUE ] INDEX [ CONCURRENTLY ] [ replaceable class=parametername/replaceable ] ON replaceable class=parametertable_name/replaceable [ USING replaceable class=parametermethod/replaceable ]
( { replaceable class=parametercolumn_name/replaceable | ( replaceable class=parameterexpression/replaceable ) } [ COLLATE replaceable class=parametercollation/replaceable ] [ replaceable class=parameteropclass/replaceable ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
[ WITH ( replaceable class=PARAMETERstorage_parameter/replaceable = replaceable class=PARAMETERvalue/replaceable [, ... ] ) ]
[ TABLESPACE replaceable class=parametertablespace_name/replaceable ]
@@ -99,6 +99,16 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ replaceable class=parametername/
variablelist
varlistentry
+ termliteralIF NOT EXISTS/literal/term
+ listitem
+ para
+Do nothing (except issuing a notice) if a index with the same name
+already exists.
+ /para
+ /listitem
+ /varlistentry
+
+ varlistentry
termliteralUNIQUE/literal/term
listitem
para
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index ee10594..8905e30 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -697,7 +697,8 @@ index_create(Relation heapRelation,
bool allow_system_table_mods,
bool skip_build,
bool concurrent,
- bool is_internal)
+ bool is_internal,
+ bool if_not_exists)
{
Oid heapRelationId = RelationGetRelid(heapRelation);
Relation pg_class;
@@ -773,10 +774,22 @@ index_create(Relation heapRelation,
elog(ERROR, shared relations must be placed in pg_global tablespace);
if (get_relname_relid(indexRelationName, namespaceId))
+ {
+ if (if_not_exists)
+ {
+ ereport(NOTICE,
+ (errcode(ERRCODE_DUPLICATE_TABLE),
+ errmsg(relation \%s\ already exists, skipping,
+ indexRelationName)));
+ heap_close(pg_class, RowExclusiveLock);
+ return InvalidOid;
+ }
+
ereport(ERROR,
(errcode(ERRCODE_DUPLICATE_TABLE),
errmsg(relation \%s\ already exists,
indexRelationName)));
+ }
/*
* construct tuple descriptor for index tuples
diff --git a/src/backend/catalog/toasting.c b/src/backend/catalog/toasting.c
index 160f006..5ef6dcc 100644
--- a/src/backend/catalog/toasting.c
+++ b/src/backend/catalog/toasting.c
@@ -342,7 +342,7 @@ create_toast_table(Relation rel, Oid toastOid, Oid toastIndexOid,
rel-rd_rel-reltablespace,
collationObjectId, classObjectId, coloptions, (Datum) 0,
true, false, false, false,
- true, false, false, true);
+ true, false, false, true, false);
heap_close(toast_rel, NoLock);
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index 8a1cb4b..a03773b 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -610,7 +610,14 @@ DefineIndex(Oid relationId,
[HACKERS] CREATE IF NOT EXISTS INDEX
Hi all, What's your thoughts about we implement IF NOT EXISTS for CREATE INDEX? As it holds data (like sequences and tables) I think we can do that. Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Timbira: http://www.timbira.com.br Blog: http://fabriziomello.github.io Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello Github: http://github.com/fabriziomello
Re: [HACKERS] CREATE IF NOT EXISTS INDEX
=?UTF-8?Q?Fabr=C3=ADzio_de_Royes_Mello?= fabriziome...@gmail.com writes: What's your thoughts about we implement IF NOT EXISTS for CREATE INDEX? It's got the same semantic problems as every other variant of CINE. If there were a huge groundswell of demand for it, maybe we'd hold our noses and do it anyway. But I'm against doing it without that. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CREATE IF NOT EXISTS INDEX
Since PostgreSQL started down that road for so many other relations, I think many people just expect this to happen as a logical extension. It certainly makes life a lot easier in combination with build management systems. /kirk On 2014-09-30 16:43, Tom Lane wrote: =?UTF-8?Q?Fabr=C3=ADzio_de_Royes_Mello?= fabriziome...@gmail.com writes: What's your thoughts about we implement IF NOT EXISTS for CREATE INDEX? It's got the same semantic problems as every other variant of CINE. If there were a huge groundswell of demand for it, maybe we'd hold our noses and do it anyway. But I'm against doing it without that. regards, tom lane
Re: [HACKERS] CREATE IF NOT EXISTS INDEX
On 09/30/2014 02:43 PM, Tom Lane wrote: =?UTF-8?Q?Fabr=C3=ADzio_de_Royes_Mello?= fabriziome...@gmail.com writes: What's your thoughts about we implement IF NOT EXISTS for CREATE INDEX? It's got the same semantic problems as every other variant of CINE. If there were a huge groundswell of demand for it, maybe we'd hold our noses and do it anyway. But I'm against doing it without that. This isn't the sort of thing there would ever be a clamor of support for, because it's just not that visible of a feature. It's more of a regular annoyance for those who encounter it. More importantly, adding an IF NOT EXISTS to CREATE INDEX would allow complete idempotent create this bunch of tables scripts, since now the create index statements could be included. This would be very nice for schema management tools. I do think it should be name-based. While an IF NOT EXISTS which checked for a duplicate column declartion would be nice, there's a raft of issues with implementing it that way. Users I know are generally just looking to avoid getting a transaction-halting error when they run the same create index statement twice. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CREATE IF NOT EXISTS INDEX
On 2014-09-30 17:01, Josh Berkus wrote: On 09/30/2014 02:43 PM, Tom Lane wrote: =?UTF-8?Q?Fabr=C3=ADzio_de_Royes_Mello?= fabriziome...@gmail.com writes: What's your thoughts about we implement IF NOT EXISTS for CREATE INDEX? It's got the same semantic problems as every other variant of CINE. If there were a huge groundswell of demand for it, maybe we'd hold our noses and do it anyway. But I'm against doing it without that. This isn't the sort of thing there would ever be a clamor of support for, because it's just not that visible of a feature. It's more of a regular annoyance for those who encounter it. More importantly, adding an IF NOT EXISTS to CREATE INDEX would allow complete idempotent create this bunch of tables scripts, since now the create index statements could be included. This would be very nice for schema management tools. I do think it should be name-based. While an IF NOT EXISTS which checked for a duplicate column declartion would be nice, there's a raft of issues with implementing it that way. Users I know are generally just looking to avoid getting a transaction-halting error when they run the same create index statement twice. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com [1] I second this evaluation. Using build tools to manage schemas, there is no expectation that the full index signature is checked. Any index of the same name would be considered a collision for my purposes. It is much easier to show CINE to a developer than to explain how an anonymous code block does the same thing. /Kirk Links: -- [1] http://pgexperts.com
Re: [HACKERS] CREATE IF NOT EXISTS INDEX
On Tue, Sep 30, 2014 at 7:01 PM, Josh Berkus j...@agliodbs.com wrote:
On 09/30/2014 02:43 PM, Tom Lane wrote:
=?UTF-8?Q?Fabr=C3=ADzio_de_Royes_Mello?= fabriziome...@gmail.com
writes:
What's your thoughts about we implement IF NOT EXISTS for CREATE INDEX?
It's got the same semantic problems as every other variant of CINE.
If there were a huge groundswell of demand for it, maybe we'd hold our
noses and do it anyway. But I'm against doing it without that.
This isn't the sort of thing there would ever be a clamor of support
for, because it's just not that visible of a feature. It's more of a
regular annoyance for those who encounter it. More importantly, adding
an IF NOT EXISTS to CREATE INDEX would allow complete idempotent create
this bunch of tables scripts, since now the create index statements
could be included. This would be very nice for schema management tools.
I do think it should be name-based. While an IF NOT EXISTS which
checked for a duplicate column declartion would be nice, there's a raft
of issues with implementing it that way. Users I know are generally
just looking to avoid getting a transaction-halting error when they run
the same create index statement twice.
Here is the patch... it's name-based.
Regards,
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
Timbira: http://www.timbira.com.br
Blog: http://fabriziomello.github.io
Linkedin: http://br.linkedin.com/in/fabriziomello
Twitter: http://twitter.com/fabriziomello
Github: http://github.com/fabriziomello
diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgml
index e469b17..7886729 100644
--- a/doc/src/sgml/ref/create_index.sgml
+++ b/doc/src/sgml/ref/create_index.sgml
@@ -21,7 +21,7 @@ PostgreSQL documentation
refsynopsisdiv
synopsis
-CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ replaceable class=parametername/replaceable ] ON replaceable class=parametertable_name/replaceable [ USING replaceable class=parametermethod/replaceable ]
+CREATE [ IF NOT EXISTS ] [ UNIQUE ] INDEX [ CONCURRENTLY ] [ replaceable class=parametername/replaceable ] ON replaceable class=parametertable_name/replaceable [ USING replaceable class=parametermethod/replaceable ]
( { replaceable class=parametercolumn_name/replaceable | ( replaceable class=parameterexpression/replaceable ) } [ COLLATE replaceable class=parametercollation/replaceable ] [ replaceable class=parameteropclass/replaceable ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
[ WITH ( replaceable class=PARAMETERstorage_parameter/replaceable = replaceable class=PARAMETERvalue/replaceable [, ... ] ) ]
[ TABLESPACE replaceable class=parametertablespace_name/replaceable ]
@@ -99,6 +99,16 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ replaceable class=parametername/
variablelist
varlistentry
+ termliteralIF NOT EXISTS/literal/term
+ listitem
+ para
+Do nothing (except issuing a notice) if a index with the same name
+already exists.
+ /para
+ /listitem
+ /varlistentry
+
+ varlistentry
termliteralUNIQUE/literal/term
listitem
para
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index ee10594..8905e30 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -697,7 +697,8 @@ index_create(Relation heapRelation,
bool allow_system_table_mods,
bool skip_build,
bool concurrent,
- bool is_internal)
+ bool is_internal,
+ bool if_not_exists)
{
Oid heapRelationId = RelationGetRelid(heapRelation);
Relation pg_class;
@@ -773,10 +774,22 @@ index_create(Relation heapRelation,
elog(ERROR, shared relations must be placed in pg_global tablespace);
if (get_relname_relid(indexRelationName, namespaceId))
+ {
+ if (if_not_exists)
+ {
+ ereport(NOTICE,
+ (errcode(ERRCODE_DUPLICATE_TABLE),
+ errmsg(relation \%s\ already exists, skipping,
+ indexRelationName)));
+ heap_close(pg_class, RowExclusiveLock);
+ return InvalidOid;
+ }
+
ereport(ERROR,
(errcode(ERRCODE_DUPLICATE_TABLE),
errmsg(relation \%s\ already exists,
indexRelationName)));
+ }
/*
* construct tuple descriptor for index tuples
diff --git a/src/backend/catalog/toasting.c b/src/backend/catalog/toasting.c
index 160f006..5ef6dcc 100644
--- a/src/backend/catalog/toasting.c
+++ b/src/backend/catalog/toasting.c
@@ -342,7 +342,7 @@ create_toast_table(Relation rel, Oid toastOid, Oid toastIndexOid,
rel-rd_rel-reltablespace,
collationObjectId, classObjectId, coloptions, (Datum) 0,
true, false, false, false,
- true, false, false, true);
+ true, false, false, true, false);
heap_close(toast_rel, NoLock);
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index 8a1cb4b..a03773b 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -610,7 +610,14 @@ DefineIndex(Oid relationId,
Re: [HACKERS] CREATE IF NOT EXISTS INDEX
Josh Berkus j...@agliodbs.com writes: On 09/30/2014 02:43 PM, Tom Lane wrote: =?UTF-8?Q?Fabr=C3=ADzio_de_Royes_Mello?= fabriziome...@gmail.com writes: What's your thoughts about we implement IF NOT EXISTS for CREATE INDEX? It's got the same semantic problems as every other variant of CINE. I do think it should be name-based. Name-based, eh? Don't you recall that in modern practice, people generally don't specify names for indexes at all? They've usually got system-generated names, which doesn't seem like a very cool thing to have scripts depending on. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CREATE IF NOT EXISTS INDEX
On 2014-09-30 18:47:24 -0400, Tom Lane wrote: Josh Berkus j...@agliodbs.com writes: On 09/30/2014 02:43 PM, Tom Lane wrote: =?UTF-8?Q?Fabr=C3=ADzio_de_Royes_Mello?= fabriziome...@gmail.com writes: What's your thoughts about we implement IF NOT EXISTS for CREATE INDEX? It's got the same semantic problems as every other variant of CINE. I do think it should be name-based. Name-based, eh? Don't you recall that in modern practice, people generally don't specify names for indexes at all? They've usually got system-generated names, which doesn't seem like a very cool thing to have scripts depending on. Good point. I think it's fair enough to only allow CINE on named indexes. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CREATE IF NOT EXISTS INDEX
On 09/30/2014 03:53 PM, Andres Freund wrote: On 2014-09-30 18:47:24 -0400, Tom Lane wrote: Josh Berkus j...@agliodbs.com writes: On 09/30/2014 02:43 PM, Tom Lane wrote: =?UTF-8?Q?Fabr=C3=ADzio_de_Royes_Mello?= fabriziome...@gmail.com writes: What's your thoughts about we implement IF NOT EXISTS for CREATE INDEX? It's got the same semantic problems as every other variant of CINE. I do think it should be name-based. Name-based, eh? Don't you recall that in modern practice, people generally don't specify names for indexes at all? They've usually got system-generated names, which doesn't seem like a very cool thing to have scripts depending on. Good point. I think it's fair enough to only allow CINE on named indexes. On the other hand, the way we form system-generated names is predicable, so I think it would be perfectly OK to include them. Desirable, in fact. For example, if I did this: CREATE INDEX ON tab1 (cola, colb); CREATE INDEX IF NOT EXISTS ON tab1 (cola, colb); I would expect to not end up with two indexes on those two particular columns, and if we don't omit system-generated names, I won't. Not that I'm a fan of omitting the name ... -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CREATE IF NOT EXISTS INDEX
On 2014-09-30 16:03:01 -0700, Josh Berkus wrote: On 09/30/2014 03:53 PM, Andres Freund wrote: On 2014-09-30 18:47:24 -0400, Tom Lane wrote: Josh Berkus j...@agliodbs.com writes: On 09/30/2014 02:43 PM, Tom Lane wrote: =?UTF-8?Q?Fabr=C3=ADzio_de_Royes_Mello?= fabriziome...@gmail.com writes: What's your thoughts about we implement IF NOT EXISTS for CREATE INDEX? It's got the same semantic problems as every other variant of CINE. I do think it should be name-based. Name-based, eh? Don't you recall that in modern practice, people generally don't specify names for indexes at all? They've usually got system-generated names, which doesn't seem like a very cool thing to have scripts depending on. Good point. I think it's fair enough to only allow CINE on named indexes. On the other hand, the way we form system-generated names is predicable, so I think it would be perfectly OK to include them. Desirable, in fact. It's not really that predicable. Think about expression indexes. They also don't contain information about opclasses et all. Seems like pit of hairy semantics. Not that I'm a fan of omitting the name ... Me neither. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CREATE IF NOT EXISTS INDEX
On 09/30/2014 04:16 PM, Andres Freund wrote: On 2014-09-30 16:03:01 -0700, Josh Berkus wrote: On 09/30/2014 03:53 PM, Andres Freund wrote: Good point. I think it's fair enough to only allow CINE on named indexes. On the other hand, the way we form system-generated names is predicable, so I think it would be perfectly OK to include them. Desirable, in fact. It's not really that predicable. Think about expression indexes. They also don't contain information about opclasses et all. Seems like pit of hairy semantics. Not that I'm a fan of omitting the name ... Me neither. I'd be OK with a CINE which required you to name the index. How does your patch work at present, Fabrizio? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CREATE IF NOT EXISTS INDEX
On Tue, Sep 30, 2014 at 8:47 PM, Josh Berkus j...@agliodbs.com wrote: On 09/30/2014 04:16 PM, Andres Freund wrote: On 2014-09-30 16:03:01 -0700, Josh Berkus wrote: On 09/30/2014 03:53 PM, Andres Freund wrote: Good point. I think it's fair enough to only allow CINE on named indexes. On the other hand, the way we form system-generated names is predicable, so I think it would be perfectly OK to include them. Desirable, in fact. It's not really that predicable. Think about expression indexes. They also don't contain information about opclasses et all. Seems like pit of hairy semantics. Not that I'm a fan of omitting the name ... Me neither. I'd be OK with a CINE which required you to name the index. How does your patch work at present, Fabrizio? My patch will work just if you name the index, because postgres generates a index name that doesn't exists. I don't check to a name if we use IF NOT EXISTS, but I can add this check. Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Timbira: http://www.timbira.com.br Blog: http://fabriziomello.github.io Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello Github: http://github.com/fabriziomello
Re: [HACKERS] CREATE IF NOT EXISTS INDEX
On 09/30/2014 04:58 PM, Fabrízio de Royes Mello wrote: On Tue, Sep 30, 2014 at 8:47 PM, Josh Berkus j...@agliodbs.com wrote: On 09/30/2014 04:16 PM, Andres Freund wrote: On 2014-09-30 16:03:01 -0700, Josh Berkus wrote: On 09/30/2014 03:53 PM, Andres Freund wrote: Good point. I think it's fair enough to only allow CINE on named indexes. On the other hand, the way we form system-generated names is predicable, so I think it would be perfectly OK to include them. Desirable, in fact. It's not really that predicable. Think about expression indexes. They also don't contain information about opclasses et all. Seems like pit of hairy semantics. Not that I'm a fan of omitting the name ... Me neither. I'd be OK with a CINE which required you to name the index. How does your patch work at present, Fabrizio? My patch will work just if you name the index, because postgres generates a index name that doesn't exists. I don't check to a name if we use IF NOT EXISTS, but I can add this check. The consensus is that we don't want IF NOT EXISTS to work for automatically generated index names. For that reason, we'd want it to error out if someone does this: CREATE INDEX IF NOT EXISTS ON table(col); My suggestion for the error message: IF NOT EXISTS requires that you name the index. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CREATE IF NOT EXISTS INDEX
On Tue, Sep 30, 2014 at 9:12 PM, Josh Berkus j...@agliodbs.com wrote:
On 09/30/2014 04:58 PM, Fabrízio de Royes Mello wrote:
On Tue, Sep 30, 2014 at 8:47 PM, Josh Berkus j...@agliodbs.com wrote:
On 09/30/2014 04:16 PM, Andres Freund wrote:
On 2014-09-30 16:03:01 -0700, Josh Berkus wrote:
On 09/30/2014 03:53 PM, Andres Freund wrote:
Good point. I think it's fair enough to only allow CINE on named
indexes.
On the other hand, the way we form system-generated names is
predicable,
so I think it would be perfectly OK to include them. Desirable, in
fact.
It's not really that predicable. Think about expression indexes. They
also don't contain information about opclasses et all.
Seems like pit of hairy semantics.
Not that I'm a fan of omitting the name ...
Me neither.
I'd be OK with a CINE which required you to name the index.
How does your patch work at present, Fabrizio?
My patch will work just if you name the index, because postgres
generates a
index name that doesn't exists.
I don't check to a name if we use IF NOT EXISTS, but I can add this
check.
The consensus is that we don't want IF NOT EXISTS to work for
automatically generated index names. For that reason, we'd want it to
error out if someone does this:
CREATE INDEX IF NOT EXISTS ON table(col);
My suggestion for the error message:
IF NOT EXISTS requires that you name the index.
Done.
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
Timbira: http://www.timbira.com.br
Blog: http://fabriziomello.github.io
Linkedin: http://br.linkedin.com/in/fabriziomello
Twitter: http://twitter.com/fabriziomello
Github: http://github.com/fabriziomello
diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgml
index e469b17..7886729 100644
--- a/doc/src/sgml/ref/create_index.sgml
+++ b/doc/src/sgml/ref/create_index.sgml
@@ -21,7 +21,7 @@ PostgreSQL documentation
refsynopsisdiv
synopsis
-CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ replaceable class=parametername/replaceable ] ON replaceable class=parametertable_name/replaceable [ USING replaceable class=parametermethod/replaceable ]
+CREATE [ IF NOT EXISTS ] [ UNIQUE ] INDEX [ CONCURRENTLY ] [ replaceable class=parametername/replaceable ] ON replaceable class=parametertable_name/replaceable [ USING replaceable class=parametermethod/replaceable ]
( { replaceable class=parametercolumn_name/replaceable | ( replaceable class=parameterexpression/replaceable ) } [ COLLATE replaceable class=parametercollation/replaceable ] [ replaceable class=parameteropclass/replaceable ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
[ WITH ( replaceable class=PARAMETERstorage_parameter/replaceable = replaceable class=PARAMETERvalue/replaceable [, ... ] ) ]
[ TABLESPACE replaceable class=parametertablespace_name/replaceable ]
@@ -99,6 +99,16 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ replaceable class=parametername/
variablelist
varlistentry
+ termliteralIF NOT EXISTS/literal/term
+ listitem
+ para
+Do nothing (except issuing a notice) if a index with the same name
+already exists.
+ /para
+ /listitem
+ /varlistentry
+
+ varlistentry
termliteralUNIQUE/literal/term
listitem
para
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index ee10594..8905e30 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -697,7 +697,8 @@ index_create(Relation heapRelation,
bool allow_system_table_mods,
bool skip_build,
bool concurrent,
- bool is_internal)
+ bool is_internal,
+ bool if_not_exists)
{
Oid heapRelationId = RelationGetRelid(heapRelation);
Relation pg_class;
@@ -773,10 +774,22 @@ index_create(Relation heapRelation,
elog(ERROR, shared relations must be placed in pg_global tablespace);
if (get_relname_relid(indexRelationName, namespaceId))
+ {
+ if (if_not_exists)
+ {
+ ereport(NOTICE,
+ (errcode(ERRCODE_DUPLICATE_TABLE),
+ errmsg(relation \%s\ already exists, skipping,
+ indexRelationName)));
+ heap_close(pg_class, RowExclusiveLock);
+ return InvalidOid;
+ }
+
ereport(ERROR,
(errcode(ERRCODE_DUPLICATE_TABLE),
errmsg(relation \%s\ already exists,
indexRelationName)));
+ }
/*
* construct tuple descriptor for index tuples
diff --git a/src/backend/catalog/toasting.c b/src/backend/catalog/toasting.c
index 160f006..5ef6dcc 100644
--- a/src/backend/catalog/toasting.c
+++ b/src/backend/catalog/toasting.c
@@ -342,7 +342,7 @@ create_toast_table(Relation rel, Oid toastOid, Oid toastIndexOid,
rel-rd_rel-reltablespace,
collationObjectId, classObjectId, coloptions, (Datum) 0,
true, false, false, false,
- true, false, false, true);
+ true, false, false, true, false);
heap_close(toast_rel, NoLock);
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
Re: [HACKERS] CREATE IF NOT EXISTS INDEX
On Wed, Oct 1, 2014 at 10:03 AM, Fabrízio de Royes Mello fabriziome...@gmail.com wrote: Done. You should consider adding that to the next commit fest. -- Michael
Re: [HACKERS] CREATE IF NOT EXISTS INDEX
On Tue, Sep 30, 2014 at 10:22 PM, Michael Paquier michael.paqu...@gmail.com wrote: On Wed, Oct 1, 2014 at 10:03 AM, Fabrízio de Royes Mello fabriziome...@gmail.com wrote: Done. You should consider adding that to the next commit fest. Sure. Added [1] Regards, [1] https://commitfest.postgresql.org/action/patch_view?id=1584 -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Timbira: http://www.timbira.com.br Blog: http://fabriziomello.github.io Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello Github: http://github.com/fabriziomello
