From: Tom Lane <tgl@sss.pgh.pa.us>
To: tom@intevation.de
cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #16272: Index expression can refer to wrong attributes if
 index is created via CREATE TABLE LIKE
In-reply-to: <16272-6e32da020e9a9381@postgresql.org>
References: <16272-6e32da020e9a9381@postgresql.org>
Comments: In-reply-to PG Bug reporting form <noreply@postgresql.org>
	message dated "Fri, 21 Feb 2020 19:37:55 +0000"
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0"
Content-ID: <6837.1582327978.0@sss.pgh.pa.us>
Date: Fri, 21 Feb 2020 18:33:31 -0500
Message-ID: <6861.1582328011@sss.pgh.pa.us>
Precedence: bulk

------- =_aaaaaaaaaa0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <6837.1582327978.1@sss.pgh.pa.us>

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


------- =_aaaaaaaaaa0
Content-Type: text/x-diff;
	name="tests-for-incorrect-LIKE-column-mapping.patch";
	charset="us-ascii"
Content-ID: <6837.1582327978.2@sss.pgh.pa.us>
Content-Description: tests-for-incorrect-LIKE-column-mapping.patch
Content-Transfer-Encoding: quoted-printable

diff --git a/src/test/regress/expected/create_table_like.out b/src/test/re=
gress/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 c=
olumn "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 COMM=
ENTS INCLUDING STORAGE) INHERITS (ctlt1);
+CREATE TABLE ctlt13_like (LIKE ctlt3 INCLUDING CONSTRAINTS INCLUDING INDE=
XES 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 =3D=
 'pg_constraint'::regclass AND objoid =3D c.oid AND c.conrelid =3D '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 CONSTRAIN=
TS INCLUDING COMMENTS) INH
 SELECT description FROM pg_description, pg_constraint c WHERE classoid =3D=
 'pg_constraint'::regclass AND objoid =3D c.oid AND c.conrelid =3D 'ctlt1_=
inh'::regclass;
 CREATE TABLE ctlt13_inh () INHERITS (ctlt1, ctlt3);
 \d+ ctlt13_inh
-CREATE TABLE ctlt13_like (LIKE ctlt3 INCLUDING CONSTRAINTS INCLUDING COMM=
ENTS INCLUDING STORAGE) INHERITS (ctlt1);
+CREATE TABLE ctlt13_like (LIKE ctlt3 INCLUDING CONSTRAINTS INCLUDING INDE=
XES INCLUDING COMMENTS INCLUDING STORAGE) INHERITS (ctlt1);
 \d+ ctlt13_like
 SELECT description FROM pg_description, pg_constraint c WHERE classoid =3D=
 'pg_constraint'::regclass AND objoid =3D c.oid AND c.conrelid =3D 'ctlt13=
_like'::regclass;
 =


------- =_aaaaaaaaaa0--