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

On 14 June 2015 at 04:25, Michael Paquier <michael(dot)paquier(at)gmail(dot)com> wrote:
> On Sun, Jun 14, 2015 at 11:38 AM, Thom Brown <thom(at)linux(dot)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(at)sss(dot)pgh(dot)pa(dot)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