Обсуждение: Problem with CREATE TABLE ... (LIKE ... INCLUDING INDEXES)

Поиск
Список
Период
Сортировка

Problem with CREATE TABLE ... (LIKE ... INCLUDING INDEXES)

От
Thom Brown
Дата:
Hi all,

I've noticed that LIKE tablename INCLUDING INDEXES skips any indexes
that were duplicated.

e.g.

CREATE TABLE people (id int, alias text);
CREATE INDEX idx_people_id_1 ON people (id);
CREATE INDEX idx_people_id_2 ON people (id) WHERE id % 2 = 0;
CREATE INDEX idx_people_alias_1 ON people (alias);
CREATE INDEX idx_people_alias_2 ON people (alias);
CREATE INDEX idx_people_alias_3_tblspc ON people (alias) TABLESPACE ts;
CREATE INDEX idx_people_alias_4 ON people (alias) WITH (FILLFACTOR = 24);

\d+ people
                       Table "public.people"Column |  Type   | Modifiers | Storage  | Stats target | Description
--------+---------+-----------+----------+--------------+-------------id     | integer |           | plain    |
    |alias  | text    |           | extended |              |
 
Indexes:   "idx_people_alias_1" btree (alias)   "idx_people_alias_2" btree (alias)   "idx_people_alias_3_tblspc" btree
(alias),tablespace "ts"   "idx_people_alias_4" btree (alias) WITH (fillfactor=24)   "idx_people_id_1" btree (id)
"idx_people_id_2"btree (id) WHERE (id % 2) = 0
 




CREATE SCHEMA test;
CREATE TABLE test.people (LIKE people INCLUDING INDEXES);

\d+ test.people
                        Table "test.people"Column |  Type   | Modifiers | Storage  | Stats target | Description
--------+---------+-----------+----------+--------------+-------------id     | integer |           | plain    |
    |alias  | text    |           | extended |              |
 
Indexes:   "people_alias_idx" btree (alias)   "people_id_idx" btree (id)   "people_id_idx1" btree (id) WHERE (id % 2) =
0


As you can see, 3 indexes are missing, which happen to be ones that
would duplicate the column definition of another index.  Is this
intentional?  If so, shouldn't it be documented behaviour?

-- 
Thom



Re: Problem with CREATE TABLE ... (LIKE ... INCLUDING INDEXES)

От
Michael Paquier
Дата:
On Sun, Jun 14, 2015 at 11:38 AM, Thom Brown <thom@linux.com> wrote:
> As you can see, 3 indexes are missing, which happen to be ones that
> would duplicate the column definition of another index.  Is this
> intentional?  If so, shouldn't it be documented behaviour?

Looking at the code (transformIndexConstraints in parse_utilcmd.c),
this is intentional behavior:       /*        * Scan the index list and remove any redundant index
specifications. This        * can happen if, for instance, the user writes UNIQUE PRIMARY KEY. A        * strict
readingof SQL would suggest raising an error
 
instead, but that        * strikes me as too anal-retentive. - tgl 2001-02-14        *        * XXX in ALTER TABLE
case,it'd be nice to look for duplicate        * pre-existing indexes, too.        */
 
Per this commit:
commit: c7d2ce7bc6eb02eac0c10fae9caf2936a71ad25c
author: Tom Lane <tgl@sss.pgh.pa.us>
date: Wed, 14 Feb 2001 23:32:38 +0000
Repair problems with duplicate index names generated when CREATE TABLE
specifies redundant UNIQUE conditions.

Perhaps a mention in the docs in the page of CREATE TABLE would be
welcome. Something like "Redundant index definitions are ignored with
INCLUDING INDEXES".

Thoughts?
-- 
Michael



Re: Problem with CREATE TABLE ... (LIKE ... INCLUDING INDEXES)

От
Thom Brown
Дата:
On 14 June 2015 at 04:25, Michael Paquier <michael.paquier@gmail.com> wrote:
> On Sun, Jun 14, 2015 at 11:38 AM, Thom Brown <thom@linux.com> wrote:
>> As you can see, 3 indexes are missing, which happen to be ones that
>> would duplicate the column definition of another index.  Is this
>> intentional?  If so, shouldn't it be documented behaviour?
>
> Looking at the code (transformIndexConstraints in parse_utilcmd.c),
> this is intentional behavior:
>         /*
>          * Scan the index list and remove any redundant index
> specifications. This
>          * can happen if, for instance, the user writes UNIQUE PRIMARY KEY. A
>          * strict reading of SQL would suggest raising an error
> instead, but that
>          * strikes me as too anal-retentive. - tgl 2001-02-14
>          *
>          * XXX in ALTER TABLE case, it'd be nice to look for duplicate
>          * pre-existing indexes, too.
>          */
> Per this commit:
> commit: c7d2ce7bc6eb02eac0c10fae9caf2936a71ad25c
> author: Tom Lane <tgl@sss.pgh.pa.us>
> date: Wed, 14 Feb 2001 23:32:38 +0000
> Repair problems with duplicate index names generated when CREATE TABLE
> specifies redundant UNIQUE conditions.
>
> Perhaps a mention in the docs in the page of CREATE TABLE would be
> welcome. Something like "Redundant index definitions are ignored with
> INCLUDING INDEXES".
>
> Thoughts?

The commit refers to duplicate index names, and only for UNIQUE
indexes.  This behaviour is beyond that.  And how does it determine
which index to copy?  In my example, I placed an index in a different
tablespace.  That could be on a drive with very different read/write
characteristics than the default tablespace (seek latency/sequential
read rate/write speed etc.) and possibly with different GUC
parameters, but there's no way for us to determine if this is the
case, so Postgres can easily remove the more performant one.

-- 
Thom



Re: Problem with CREATE TABLE ... (LIKE ... INCLUDING INDEXES)

От
Tom Lane
Дата:
Thom Brown <thom@linux.com> writes:
> The commit refers to duplicate index names, and only for UNIQUE
> indexes.  This behaviour is beyond that.  And how does it determine
> which index to copy?  In my example, I placed an index in a different
> tablespace.  That could be on a drive with very different read/write
> characteristics than the default tablespace (seek latency/sequential
> read rate/write speed etc.) and possibly with different GUC
> parameters, but there's no way for us to determine if this is the
> case, so Postgres can easily remove the more performant one.

TBH, I have no particular concern for this argument.  If you created
duplicate indexes you did a dumb thing anyway; you should not be expecting
that the system's response to that situation will be remarkably
intelligent.  As the comment indicates, the code in question is really
only meant to deal with a specific kind of redundancy we'd observed in
real-world CREATE TABLE commands.  It's probably accidental that it gets
applied in CREATE TABLE LIKE cases, but it doesn't bother me that it is.
        regards, tom lane