I recently came across a scenario like this (tested on git head): ---- CREATE TABLE test (id int); CREATE INDEX test_idx1 ON test (id); CREATE INDEX test_idx2 ON test (id);
CREATE TABLE test_copycat (LIKE test INCLUDING ALL); \d test_copycat ---- Why do we end up with only one index on test_copycat? The culprit seems to be transformIndexConstraints(), which explains: * 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 SQL92 would suggest raising an error instead, but * that strikes me as too anal-retentive. - tgl 2001-02-14 and this code happily throws out the second index statement in this example, since its properties are identical to the first. (Side note: some index properties, such as tablespace specification and comment, are ignored when determining duplicates). This behavior does seem like a minor POLA violation to me -- if we do not forbid duplicate indexes on the original table, it seems surprising to do so silently with INCLUDING INDEXES. There was consideration of similar behavior when this patch was proposed[1], so perhaps the behavior is as-designed, and I guess no one else has complained. IMO this behavior should at least be documented under the "LIKE source_table" section of CREATE TABLE's doc page. Josh [1] http://archives.postgresql.org/pgsql-patches/2007-07/msg00173.php -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
