Обсуждение: BUG #6734: create table (like ...) fails if an index has a comment

The following bug has been logged on the website:

Bug reference:      6734
Logged by:          Daniele Varrazzo
Email address:      daniele.varrazzo@gmail.com
PostgreSQL version: 9.1.4
Operating system:   Linux
Description:=20=20=20=20=20=20=20=20

Weird, isn't it? Test case below.=20

Dropping the comment, the create table command works as expected. The
command fails with an: "ERROR: relation "foo2_f1_idx" already exists".

begin;

CREATE TABLE foo
(
  id serial primary key,
  f1 integer NOT NULL
);


CREATE INDEX foo_idx1 ON foo (f1);

CREATE INDEX foo_idx2 ON foo (f1) WHERE id > 10;

COMMENT ON INDEX foo_idx2 IS 'whatever';

create table foo2 (like foo including all);

rollback;

On Fri, Jul 13, 2012 at 12:00:14PM +0000, daniele.varrazzo@gmail.com wrote:
> The following bug has been logged on the website:
>
> Bug reference:      6734
> Logged by:          Daniele Varrazzo
> Email address:      daniele.varrazzo@gmail.com
> PostgreSQL version: 9.1.4
> Operating system:   Linux
> Description:
>
> Weird, isn't it? Test case below.
>
> Dropping the comment, the create table command works as expected. The
> command fails with an: "ERROR: relation "foo2_f1_idx" already exists".
The comments on chooseIndexName in src/backend/parser/parse_utilcmd.c say:

* XXX this is inherently broken because the indexes aren't created
* immediately, so we fail to resolve conflicts when the same name is
* derived for multiple indexes.

Which looks like the case here. So it seems like
chooseIndexName/ChooseIndexName might need to take a list of any indexes
names that have already been created to avoid this.

>
> begin;
>
> CREATE TABLE foo
> (
>   id serial primary key,
>   f1 integer NOT NULL
> );
>
>
> CREATE INDEX foo_idx1 ON foo (f1);
>
> CREATE INDEX foo_idx2 ON foo (f1) WHERE id > 10;
>
> COMMENT ON INDEX foo_idx2 IS 'whatever';
>
> create table foo2 (like foo including all);
>
> rollback;
>
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs

-Ryan Kelly

On Fri, Jul 13, 2012 at 2:24 PM, Ryan Kelly <rpkelly22@gmail.com> wrote:
> On Fri, Jul 13, 2012 at 12:00:14PM +0000, daniele.varrazzo@gmail.com wrote:
>> The following bug has been logged on the website:
>>
>> Bug reference:      6734
>> Logged by:          Daniele Varrazzo
>> Email address:      daniele.varrazzo@gmail.com
>> PostgreSQL version: 9.1.4
>> Operating system:   Linux
>> Description:
>>
>> Weird, isn't it? Test case below.
>>
>> Dropping the comment, the create table command works as expected. The
>> command fails with an: "ERROR: relation "foo2_f1_idx" already exists".
> The comments on chooseIndexName in src/backend/parser/parse_utilcmd.c say:
>
> * XXX this is inherently broken because the indexes aren't created
> * immediately, so we fail to resolve conflicts when the same name is
> * derived for multiple indexes.
>
> Which looks like the case here. So it seems like
> chooseIndexName/ChooseIndexName might need to take a list of any indexes
> names that have already been created to avoid this.

For the work I'm doing now (a migration of a table with a dozen of
indexes, that will need further process downstream) I'm finding it
would be much better to have name generation more predictable: even
without the bug, the index names are not very descriptive. Yes, they
contain the field name and the new table name, but the name may have
been something meaningful such as "open_contracts_idx". For
conflicting names (such as the ones in the test case, without the
comment) I guess it's undefined, or arbitrary anyway, which one gets
the numeric suffix.

Wouldn't it be better to call the indexes NEWTABLE_OLDINDEXNAME? They
would be more verbose, but generation of two equally named indexes in
the same table would be impossible (as OLDINDEXNAMEs are distinct) and
it would be easy to map old indexes into new ones. The "add a number
suffix" behavior would be still required to avoid conflict with the
name of an index of another table, but I guess this is already handled
by the current implementation.

-- Daniele

Ryan Kelly <rpkelly22@gmail.com> writes:
> The comments on chooseIndexName in src/backend/parser/parse_utilcmd.c say:

> * XXX this is inherently broken because the indexes aren't created
> * immediately, so we fail to resolve conflicts when the same name is
> * derived for multiple indexes.

> Which looks like the case here. So it seems like
> chooseIndexName/ChooseIndexName might need to take a list of any indexes
> names that have already been created to avoid this.

I think a cleaner fix might be for IndexStmt to grow the ability to
specify a comment.  What's bogus here is that transformTableLikeClause
has to (try to) lock down the index name in advance of actual creation,
because it wants to generate a plain vanilla CommentStmt.  If it didn't
have to do that, everything would be fine, and we could lose
chooseIndexName altogether.  ChooseIndexName is fine, because it's only
run at the instant of index creation.

            regards, tom lane

Daniele Varrazzo <daniele.varrazzo@gmail.com> writes:
> Wouldn't it be better to call the indexes NEWTABLE_OLDINDEXNAME?

Then the CREATE LIKE command would fail altogether if that name were
already taken.  Postponing the selection of the index name to the time
when DefineIndex runs is really the only safe thing.

            regards, tom lane

On Fri, Jul 13, 2012 at 4:11 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Daniele Varrazzo <daniele.varrazzo@gmail.com> writes:
>> Wouldn't it be better to call the indexes NEWTABLE_OLDINDEXNAME?
>
> Then the CREATE LIKE command would fail altogether if that name were
> already taken.  Postponing the selection of the index name to the time
> when DefineIndex runs is really the only safe thing.

I've actually suggested to keep the numeric suffix to disambiguate in
case the name is already taken, as I assume the current "create like"
does. However this is not strictly related to the bug, it's more an
usability problem, so won't bother you with it anymore.

-- Daniele

daniele.varrazzo@gmail.com writes:
> CREATE TABLE foo
> (
>   id serial primary key,
>   f1 integer NOT NULL
> );

> CREATE INDEX foo_idx1 ON foo (f1);

> CREATE INDEX foo_idx2 ON foo (f1) WHERE id > 10;

> COMMENT ON INDEX foo_idx2 IS 'whatever';

> create table foo2 (like foo including all);

I've applied a fix for this in 9.2 and up.  The issue exists since
CREATE TABLE LIKE grew the ability to copy comments, in 9.0, but
it seems too risky to fix in already-released branches.

            regards, tom lane