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

=?UTF-8?Q?Fabr=C3=ADzio_de_Royes_Mello?= <fabriziomello(at)gmail(dot)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

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?= <fabriziomello(at)gmail(dot)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

On 09/30/2014 02:43 PM, Tom Lane wrote:
> =?UTF-8?Q?Fabr=C3=ADzio_de_Royes_Mello?= <fabriziomello(at)gmail(dot)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

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?= <fabriziomello(at)gmail(dot)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

On Tue, Sep 30, 2014 at 7:01 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>
> On 09/30/2014 02:43 PM, Tom Lane wrote:
> > =?UTF-8?Q?Fabr=C3=ADzio_de_Royes_Mello?= <fabriziomello(at)gmail(dot)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

Josh Berkus <josh(at)agliodbs(dot)com> writes:
> On 09/30/2014 02:43 PM, Tom Lane wrote:
>> =?UTF-8?Q?Fabr=C3=ADzio_de_Royes_Mello?= <fabriziomello(at)gmail(dot)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

On 2014-09-30 18:47:24 -0400, Tom Lane wrote:
> Josh Berkus <josh(at)agliodbs(dot)com> writes:
> > On 09/30/2014 02:43 PM, Tom Lane wrote:
> >> =?UTF-8?Q?Fabr=C3=ADzio_de_Royes_Mello?= <fabriziomello(at)gmail(dot)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

On 09/30/2014 03:53 PM, Andres Freund wrote:
> On 2014-09-30 18:47:24 -0400, Tom Lane wrote:
>> Josh Berkus <josh(at)agliodbs(dot)com> writes:
>>> On 09/30/2014 02:43 PM, Tom Lane wrote:
>>>> =?UTF-8?Q?Fabr=C3=ADzio_de_Royes_Mello?= <fabriziomello(at)gmail(dot)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

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 <josh(at)agliodbs(dot)com> writes:
> >>> On 09/30/2014 02:43 PM, Tom Lane wrote:
> >>>> =?UTF-8?Q?Fabr=C3=ADzio_de_Royes_Mello?= <fabriziomello(at)gmail(dot)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

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

On Tue, Sep 30, 2014 at 8:47 PM, Josh Berkus <josh(at)agliodbs(dot)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

On 09/30/2014 04:58 PM, Fabrízio de Royes Mello wrote:
> On Tue, Sep 30, 2014 at 8:47 PM, Josh Berkus <josh(at)agliodbs(dot)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

On Tue, Sep 30, 2014 at 9:12 PM, Josh Berkus <josh(at)agliodbs(dot)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 <josh(at)agliodbs(dot)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

On Wed, Oct 1, 2014 at 10:03 AM, Fabrízio de Royes Mello <
fabriziomello(at)gmail(dot)com> wrote:

> Done.
>
You should consider adding that to the next commit fest.
--
Michael

On Tue, Sep 30, 2014 at 10:22 PM, Michael Paquier <michael(dot)paquier(at)gmail(dot)com>
wrote:
>
> On Wed, Oct 1, 2014 at 10:03 AM, Fabrízio de Royes Mello <
fabriziomello(at)gmail(dot)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

On Wed, Oct 1, 2014 at 7:57 AM, José Luis Tallón <jltallon(at)adv-solutions(dot)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_name<DOT>relation_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

On Wed, Oct 1, 2014 at 2:42 PM, Fabrízio de Royes Mello
<fabriziomello(at)gmail(dot)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

On Fri, Oct 3, 2014 at 2:15 AM, Marti Raudsepp <marti(at)juffo(dot)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

On Thu, Oct 2, 2014 at 8:15 PM, Marti Raudsepp <marti(at)juffo(dot)org> wrote:
>
> On Wed, Oct 1, 2014 at 2:42 PM, Fabrízio de Royes Mello
> <fabriziomello(at)gmail(dot)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

On Thu, Oct 2, 2014 at 9:55 PM, Marti Raudsepp <marti(at)juffo(dot)org> wrote:
>
> On Fri, Oct 3, 2014 at 2:15 AM, Marti Raudsepp <marti(at)juffo(dot)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

On Fri, Oct 3, 2014 at 12:29 AM, Fabrízio de Royes Mello <
fabriziomello(at)gmail(dot)com> wrote:
>
>
>
> On Thu, Oct 2, 2014 at 9:55 PM, Marti Raudsepp <marti(at)juffo(dot)org> wrote:
> >
> > On Fri, Oct 3, 2014 at 2:15 AM, Marti Raudsepp <marti(at)juffo(dot)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

"On Fri, Oct 3, 2014 at 6:25 AM, Fabrízio de Royes Mello
<fabriziomello(at)gmail(dot)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."

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

On Fri, Oct 3, 2014 at 6:29 AM, Fabrízio de Royes Mello
<fabriziomello(at)gmail(dot)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.

On Fri, Oct 3, 2014 at 6:35 AM, Fabrízio de Royes Mello
<fabriziomello(at)gmail(dot)com> wrote:
> And just to remember please add your review to commitfest

Thanks for reminding, I always forget to update the CommitFest... :(

Regards,
Marti

On Fri, Oct 3, 2014 at 5:26 AM, Marti Raudsepp <marti(at)juffo(dot)org> wrote:
>
> "On Fri, Oct 3, 2014 at 6:25 AM, Fabrízio de Royes Mello
> <fabriziomello(at)gmail(dot)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
> <fabriziomello(at)gmail(dot)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
> <fabriziomello(at)gmail(dot)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

On Fri, Oct 3, 2014 at 7:25 PM, Fabrízio de Royes Mello
<fabriziomello(at)gmail(dot)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

On Sun, Oct 5, 2014 at 9:52 AM, Marti Raudsepp <marti(at)juffo(dot)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

On Mon, Oct 6, 2014 at 4:17 AM, Fabrízio de Royes Mello
<fabriziomello(at)gmail(dot)com> wrote:
> On Sun, Oct 5, 2014 at 9:52 AM, Marti Raudsepp <marti(at)juffo(dot)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

On Mon, Oct 6, 2014 at 5:12 AM, Marti Raudsepp <marti(at)juffo(dot)org> wrote:
> On Mon, Oct 6, 2014 at 4:17 AM, Fabrízio de Royes Mello <fabriziomello(at)gmail(dot)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

On Mon, Oct 6, 2014 at 4:49 AM, Marti Raudsepp <marti(at)juffo(dot)org> wrote:
>
> On Mon, Oct 6, 2014 at 5:12 AM, Marti Raudsepp <marti(at)juffo(dot)org> wrote:
> > On Mon, Oct 6, 2014 at 4:17 AM, Fabrízio de Royes Mello <
fabriziomello(at)gmail(dot)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

On Mon, Oct 6, 2014 at 4:27 PM, Fabrízio de Royes Mello
<fabriziomello(at)gmail(dot)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

On Mon, Oct 6, 2014 at 11:13 AM, Marti Raudsepp <marti(at)juffo(dot)org> wrote:
>
> On Mon, Oct 6, 2014 at 4:27 PM, Fabrízio de Royes Mello
> <fabriziomello(at)gmail(dot)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

On Tue, Oct 7, 2014 at 2:42 AM, Fabrízio de Royes Mello
<fabriziomello(at)gmail(dot)com> wrote:
> On Mon, Oct 6, 2014 at 11:13 AM, Marti Raudsepp <marti(at)juffo(dot)org> wrote:
>>
>> On Mon, Oct 6, 2014 at 4:27 PM, Fabrízio de Royes Mello
>> <fabriziomello(at)gmail(dot)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.

+ <term><literal>IF 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.

+ bool if_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

On Thu, Oct 30, 2014 at 12:11 PM, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
>
> On Tue, Oct 7, 2014 at 2:42 AM, Fabrízio de Royes Mello
> <fabriziomello(at)gmail(dot)com> wrote:
> > On Mon, Oct 6, 2014 at 11:13 AM, Marti Raudsepp <marti(at)juffo(dot)org> wrote:
> >>
> >> On Mon, Oct 6, 2014 at 4:27 PM, Fabrízio de Royes Mello
> >> <fabriziomello(at)gmail(dot)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.
>
> + <term><literal>IF 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.

> + bool if_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

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(dot)brightwell(at)crunchydatasolutions(dot)com
Database Engineer - www.crunchydatasolutions.com

On Fri, Oct 31, 2014 at 2:46 PM, Adam Brightwell <
adam(dot)brightwell(at)crunchydatasolutions(dot)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

On Sat, Nov 1, 2014 at 1:56 AM, Fabrízio de Royes Mello
<fabriziomello(at)gmail(dot)com> wrote:
>
> On Fri, Oct 31, 2014 at 2:46 PM, Adam Brightwell
> <adam(dot)brightwell(at)crunchydatasolutions(dot)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

On Thu, Nov 6, 2014 at 7:49 AM, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
>
> On Sat, Nov 1, 2014 at 1:56 AM, Fabrízio de Royes Mello
> <fabriziomello(at)gmail(dot)com> wrote:
> >
> > On Fri, Oct 31, 2014 at 2:46 PM, Adam Brightwell
> > <adam(dot)brightwell(at)crunchydatasolutions(dot)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!
>

You're welcome and thanks too!

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