Thread: Problem with CREATE TABLE ... (LIKE ... INCLUDING INDEXES)
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
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
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
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