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
Date: Fri, 21 Feb 2020 18:33:31 -0500
Message-ID: <6861.1582328011@sss.pgh.pa.us> (raw)
In-Reply-To: <16272-6e32da020e9a9381@postgresql.org>
References: <16272-6e32da020e9a9381@postgresql.org>
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
Attachments:tests-for-incorrect-LIKE-column-mapping.patch (text/x-diff, 3.7K) downloaddiff --git a/src/test/regress/expected/create_table_like.out b/src/test/regress/expected/create_table_like.outindex 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 AND c.conrelid = 'ctlt13_like'::regclass;
diff --git a/src/test/regress/sql/create_table_like.sql b/src/test/regress/sql/create_table_like.sqlindex 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 AND c.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 AND c.conrelid = 'ctlt13_like'::regclass;
=
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, tom@intevation.de, 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: <6861.1582328011@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox