Re: BUG #16272: Index expression can refer to wrong attributes if index is created via CREATE TABLE LIKE - Mailing list pgsql-bugs

PG Bug reporting form <noreply@postgresql.org> writes:
> The index expression in the index created via LIKE ... INCLUDING INDEXES
> still refers to the first two attributes of the table, although an attribute
> has been put in place before the columns the expression referred to in the
> original index.

Ugh.  So the problem here is that transformTableLikeClause carefully
renumbers the Vars in the index expression to match the new column
numbers ... as they stand when it runs, which is before any account
has been taken of inheritance.  It looks like Vars in check constraints
are likewise misprocessed, and probably GENERATED expressions as well.

I think this is basically another instance of the ALTER TABLE issues
I recently fixed: doing this sort of transformation at parse time is
fundamentally broken.  We should refrain from trying to import the LIKE
table's indexes etc. until after MergeAttributes has done its work, and
most likely ought to just punt LIKE transformation into DefineRelation
altogether.  That's probably too big a change to consider back-patching,
unfortunately.

For future reference, there are some test cases in create_table_like.sql
that come oh so close to exposing these issues.  But not close enough.
See attached test-case patch (with wrong results).

            regards, tom lane

diff --git a/src/test/regress/expected/create_table_like.out b/src/test/regress/expected/create_table_like.out
index 94d4858..deafc81 100644
--- a/src/test/regress/expected/create_table_like.out
+++ b/src/test/regress/expected/create_table_like.out
@@ -268,9 +268,10 @@ ALTER TABLE ctlt1 ALTER COLUMN a SET STORAGE MAIN;
 CREATE TABLE ctlt2 (c text);
 ALTER TABLE ctlt2 ALTER COLUMN c SET STORAGE EXTERNAL;
 COMMENT ON COLUMN ctlt2.c IS 'C';
-CREATE TABLE ctlt3 (a text CHECK (length(a) < 5), c text);
+CREATE TABLE ctlt3 (a text CHECK (length(a) < 5), c text CHECK (length(c) < 7));
 ALTER TABLE ctlt3 ALTER COLUMN c SET STORAGE EXTERNAL;
 ALTER TABLE ctlt3 ALTER COLUMN a SET STORAGE MAIN;
+CREATE INDEX ctlt3_fnidx ON ctlt3 ((a || c));
 COMMENT ON COLUMN ctlt3.a IS 'A3';
 COMMENT ON COLUMN ctlt3.c IS 'C';
 COMMENT ON CONSTRAINT ctlt3_a_check ON ctlt3 IS 't3_a_check';
@@ -326,10 +327,11 @@ NOTICE:  merging multiple inherited definitions of column "a"
 Check constraints:
     "ctlt1_a_check" CHECK (length(a) > 2)
     "ctlt3_a_check" CHECK (length(a) < 5)
+    "ctlt3_c_check" CHECK (length(c) < 7)
 Inherits: ctlt1,
           ctlt3

-CREATE TABLE ctlt13_like (LIKE ctlt3 INCLUDING CONSTRAINTS INCLUDING COMMENTS INCLUDING STORAGE) INHERITS (ctlt1);
+CREATE TABLE ctlt13_like (LIKE ctlt3 INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING COMMENTS INCLUDING STORAGE)
INHERITS(ctlt1); 
 NOTICE:  merging column "a" with inherited definition
 \d+ ctlt13_like
                                Table "public.ctlt13_like"
@@ -338,9 +340,12 @@ NOTICE:  merging column "a" with inherited definition
  a      | text |           | not null |         | main     |              | A3
  b      | text |           |          |         | extended |              |
  c      | text |           |          |         | external |              | C
+Indexes:
+    "ctlt13_like_expr_idx" btree ((a || b))
 Check constraints:
     "ctlt1_a_check" CHECK (length(a) > 2)
     "ctlt3_a_check" CHECK (length(a) < 5)
+    "ctlt3_c_check" CHECK (length(b) < 7)
 Inherits: ctlt1

 SELECT description FROM pg_description, pg_constraint c WHERE classoid = 'pg_constraint'::regclass AND objoid = c.oid
ANDc.conrelid = 'ctlt13_like'::regclass; 
diff --git a/src/test/regress/sql/create_table_like.sql b/src/test/regress/sql/create_table_like.sql
index 589ee12..185ab84 100644
--- a/src/test/regress/sql/create_table_like.sql
+++ b/src/test/regress/sql/create_table_like.sql
@@ -118,9 +118,10 @@ CREATE TABLE ctlt2 (c text);
 ALTER TABLE ctlt2 ALTER COLUMN c SET STORAGE EXTERNAL;
 COMMENT ON COLUMN ctlt2.c IS 'C';

-CREATE TABLE ctlt3 (a text CHECK (length(a) < 5), c text);
+CREATE TABLE ctlt3 (a text CHECK (length(a) < 5), c text CHECK (length(c) < 7));
 ALTER TABLE ctlt3 ALTER COLUMN c SET STORAGE EXTERNAL;
 ALTER TABLE ctlt3 ALTER COLUMN a SET STORAGE MAIN;
+CREATE INDEX ctlt3_fnidx ON ctlt3 ((a || c));
 COMMENT ON COLUMN ctlt3.a IS 'A3';
 COMMENT ON COLUMN ctlt3.c IS 'C';
 COMMENT ON CONSTRAINT ctlt3_a_check ON ctlt3 IS 't3_a_check';
@@ -137,7 +138,7 @@ CREATE TABLE ctlt1_inh (LIKE ctlt1 INCLUDING CONSTRAINTS INCLUDING COMMENTS) INH
 SELECT description FROM pg_description, pg_constraint c WHERE classoid = 'pg_constraint'::regclass AND objoid = c.oid
ANDc.conrelid = 'ctlt1_inh'::regclass; 
 CREATE TABLE ctlt13_inh () INHERITS (ctlt1, ctlt3);
 \d+ ctlt13_inh
-CREATE TABLE ctlt13_like (LIKE ctlt3 INCLUDING CONSTRAINTS INCLUDING COMMENTS INCLUDING STORAGE) INHERITS (ctlt1);
+CREATE TABLE ctlt13_like (LIKE ctlt3 INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING COMMENTS INCLUDING STORAGE)
INHERITS(ctlt1); 
 \d+ ctlt13_like
 SELECT description FROM pg_description, pg_constraint c WHERE classoid = 'pg_constraint'::regclass AND objoid = c.oid
ANDc.conrelid = 'ctlt13_like'::regclass;