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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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?
--
Michael
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers