pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #16342: CREATE TABLE LIKE INCLUDING GENERATED column order issue
5+ messages / 4 participants
[nested] [flat]
* BUG #16342: CREATE TABLE LIKE INCLUDING GENERATED column order issue
@ 2020-04-03 23:50 PG Bug reporting form <noreply@postgresql.org>
2020-04-05 11:28 ` Re: BUG #16342: CREATE TABLE LIKE INCLUDING GENERATED column order issue Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
0 siblings, 1 reply; 5+ messages in thread
From: PG Bug reporting form @ 2020-04-03 23:50 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: ewaldo@healthetechs.com
The following bug has been logged on the website:
Bug reference: 16342
Logged by: Ethan Waldo
Email address: ewaldo@healthetechs.com
PostgreSQL version: 12.2
Operating system: Debian Buster
Description:
CREATE TABLE succeeds when using generated columns and it doesn't matter
what order they are in. When CREATE TABLE LIKE INCLUDING GENERATED is used,
it only works if the source column was created first in the original
table.
postgres=# CREATE TABLE test (
funds float GENERATED ALWAYS AS (cast(funds_t AS double precision))
STORED,
funds_t text,
id bigserial not null
);
CREATE TABLE
postgres=# CREATE TABLE IF NOT EXISTS test_temp (LIKE test INCLUDING
GENERATED);
ERROR: XX000: unexpected varattno 3 in expression to be mapped
LOCATION: map_variable_attnos_mutator, rewriteManip.c:1255
postgres=# DROP TABLE test;
DROP TABLE
# Works with funds and funds_t order flipped
postgres=# CREATE TABLE test (
funds_t text,
funds float GENERATED ALWAYS AS (cast(funds_t AS double precision))
STORED,
id bigserial not null
);
CREATE TABLE
postgres=# CREATE TABLE IF NOT EXISTS test_temp (LIKE test INCLUDING
GENERATED);
CREATE TABLE
postgres=#
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #16342: CREATE TABLE LIKE INCLUDING GENERATED column order issue
2020-04-03 23:50 BUG #16342: CREATE TABLE LIKE INCLUDING GENERATED column order issue PG Bug reporting form <noreply@postgresql.org>
@ 2020-04-05 11:28 ` Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
2020-04-05 15:26 ` Re: BUG #16342: CREATE TABLE LIKE INCLUDING GENERATED column order issue Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Peter Eisentraut @ 2020-04-05 11:28 UTC (permalink / raw)
To: ewaldo@healthetechs.com; pgsql-bugs@lists.postgresql.org
On 2020-04-04 01:50, PG Bug reporting form wrote:
> postgres=# CREATE TABLE test (
> funds float GENERATED ALWAYS AS (cast(funds_t AS double precision))
> STORED,
> funds_t text,
> id bigserial not null
> );
> CREATE TABLE
>
> postgres=# CREATE TABLE IF NOT EXISTS test_temp (LIKE test INCLUDING
> GENERATED);
> ERROR: XX000: unexpected varattno 3 in expression to be mapped
> LOCATION: map_variable_attnos_mutator, rewriteManip.c:1255
Confirmed. Attached is a patch to fix it. Thanks for the report!
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Attachments:
0001-Fix-CREATE-TABLE-LIKE-INCLUDING-GENERATED-column-ord.patch (text/plain, 8.1K) download
From ca569103615d440426f628c7c3d94b2e7869020f Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter@eisentraut.org>
Date: Sun, 5 Apr 2020 13:16:55 +0200
Subject: [PATCH] Fix CREATE TABLE LIKE INCLUDING GENERATED column order issue
CREATE TABLE LIKE INCLUDING GENERATED would fail if a generated column
referred to a column with a higher attribute number. This is because
the column mapping mechanism created the mapping incrementally as
columns are added. This was sufficient for previous uses of that
mechanism (omitting dropped columns), and it also happened to work if
generated columns only referred to columns with lower attribute
numbers, but here it failed.
This fix is to build the attribute mapping in a separate loop before
processing the columns in detail.
Bug: #16342
Reported-by: Ethan Waldo <ewaldo@healthetechs.com>
---
src/backend/parser/parse_utilcmd.c | 21 ++++++++++++--
.../regress/expected/create_table_like.out | 29 ++++++++++---------
src/test/regress/sql/create_table_like.sql | 19 ++++++------
3 files changed, 43 insertions(+), 26 deletions(-)
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index 484ca40ddf..b0daad69d5 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -914,6 +914,7 @@ static void
transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_clause)
{
AttrNumber parent_attno;
+ AttrNumber new_attno;
Relation relation;
TupleDesc tupleDesc;
TupleConstr *constr;
@@ -976,6 +977,22 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
*/
attmap = (AttrNumber *) palloc0(sizeof(AttrNumber) * tupleDesc->natts);
+ new_attno = 1;
+ for (parent_attno = 1; parent_attno <= tupleDesc->natts;
+ parent_attno++)
+ {
+ Form_pg_attribute attribute = TupleDescAttr(tupleDesc,
+ parent_attno - 1);
+
+ /*
+ * Ignore dropped columns in the parent. attmap entry is left zero.
+ */
+ if (attribute->attisdropped)
+ continue;
+
+ attmap[parent_attno - 1] = list_length(cxt->columns) + (new_attno++);
+ }
+
/*
* Insert the copied attributes into the cxt for the new table definition.
*/
@@ -988,7 +1005,7 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
ColumnDef *def;
/*
- * Ignore dropped columns in the parent. attmap entry is left zero.
+ * Ignore dropped columns in the parent.
*/
if (attribute->attisdropped)
continue;
@@ -1020,8 +1037,6 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
*/
cxt->columns = lappend(cxt->columns, def);
- attmap[parent_attno - 1] = list_length(cxt->columns);
-
/*
* Copy default, if present and it should be copied. We have separate
* options for plain default expressions and GENERATED defaults.
diff --git a/src/test/regress/expected/create_table_like.out b/src/test/regress/expected/create_table_like.out
index 2a063a8369..da0e8ac298 100644
--- a/src/test/regress/expected/create_table_like.out
+++ b/src/test/regress/expected/create_table_like.out
@@ -159,14 +159,15 @@ SELECT * FROM test_like_gen_3;
(1 row)
DROP TABLE test_like_gen_1, test_like_gen_2, test_like_gen_3;
-CREATE TABLE test_like_4 (a int, b int DEFAULT 42, c int GENERATED ALWAYS AS (a * 2) STORED);
+-- also test generated column with a "forward" reference (bug #16342)
+CREATE TABLE test_like_4 (b int DEFAULT 42, c int GENERATED ALWAYS AS (a * 2) STORED, a int);
\d test_like_4
Table "public.test_like_4"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+------------------------------------
- a | integer | | |
b | integer | | | 42
c | integer | | | generated always as (a * 2) stored
+ a | integer | | |
CREATE TABLE test_like_4a (LIKE test_like_4);
CREATE TABLE test_like_4b (LIKE test_like_4 INCLUDING DEFAULTS);
@@ -176,12 +177,12 @@ CREATE TABLE test_like_4d (LIKE test_like_4 INCLUDING DEFAULTS INCLUDING GENERAT
Table "public.test_like_4a"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
- a | integer | | |
b | integer | | |
c | integer | | |
+ a | integer | | |
-INSERT INTO test_like_4a VALUES(11);
-TABLE test_like_4a;
+INSERT INTO test_like_4a (a) VALUES(11);
+SELECT a, b, c FROM test_like_4a;
a | b | c
----+---+---
11 | |
@@ -191,12 +192,12 @@ TABLE test_like_4a;
Table "public.test_like_4b"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
- a | integer | | |
b | integer | | | 42
c | integer | | |
+ a | integer | | |
-INSERT INTO test_like_4b VALUES(11);
-TABLE test_like_4b;
+INSERT INTO test_like_4b (a) VALUES(11);
+SELECT a, b, c FROM test_like_4b;
a | b | c
----+----+---
11 | 42 |
@@ -206,12 +207,12 @@ TABLE test_like_4b;
Table "public.test_like_4c"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+------------------------------------
- a | integer | | |
b | integer | | |
c | integer | | | generated always as (a * 2) stored
+ a | integer | | |
-INSERT INTO test_like_4c VALUES(11);
-TABLE test_like_4c;
+INSERT INTO test_like_4c (a) VALUES(11);
+SELECT a, b, c FROM test_like_4c;
a | b | c
----+---+----
11 | | 22
@@ -221,12 +222,12 @@ TABLE test_like_4c;
Table "public.test_like_4d"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+------------------------------------
- a | integer | | |
b | integer | | | 42
c | integer | | | generated always as (a * 2) stored
+ a | integer | | |
-INSERT INTO test_like_4d VALUES(11);
-TABLE test_like_4d;
+INSERT INTO test_like_4d (a) VALUES(11);
+SELECT a, b, c FROM test_like_4d;
a | b | c
----+----+----
11 | 42 | 22
diff --git a/src/test/regress/sql/create_table_like.sql b/src/test/regress/sql/create_table_like.sql
index 589ee12ebc..5d30f63e00 100644
--- a/src/test/regress/sql/create_table_like.sql
+++ b/src/test/regress/sql/create_table_like.sql
@@ -65,24 +65,25 @@ CREATE TABLE test_like_gen_3 (LIKE test_like_gen_1 INCLUDING GENERATED);
SELECT * FROM test_like_gen_3;
DROP TABLE test_like_gen_1, test_like_gen_2, test_like_gen_3;
-CREATE TABLE test_like_4 (a int, b int DEFAULT 42, c int GENERATED ALWAYS AS (a * 2) STORED);
+-- also test generated column with a "forward" reference (bug #16342)
+CREATE TABLE test_like_4 (b int DEFAULT 42, c int GENERATED ALWAYS AS (a * 2) STORED, a int);
\d test_like_4
CREATE TABLE test_like_4a (LIKE test_like_4);
CREATE TABLE test_like_4b (LIKE test_like_4 INCLUDING DEFAULTS);
CREATE TABLE test_like_4c (LIKE test_like_4 INCLUDING GENERATED);
CREATE TABLE test_like_4d (LIKE test_like_4 INCLUDING DEFAULTS INCLUDING GENERATED);
\d test_like_4a
-INSERT INTO test_like_4a VALUES(11);
-TABLE test_like_4a;
+INSERT INTO test_like_4a (a) VALUES(11);
+SELECT a, b, c FROM test_like_4a;
\d test_like_4b
-INSERT INTO test_like_4b VALUES(11);
-TABLE test_like_4b;
+INSERT INTO test_like_4b (a) VALUES(11);
+SELECT a, b, c FROM test_like_4b;
\d test_like_4c
-INSERT INTO test_like_4c VALUES(11);
-TABLE test_like_4c;
+INSERT INTO test_like_4c (a) VALUES(11);
+SELECT a, b, c FROM test_like_4c;
\d test_like_4d
-INSERT INTO test_like_4d VALUES(11);
-TABLE test_like_4d;
+INSERT INTO test_like_4d (a) VALUES(11);
+SELECT a, b, c FROM test_like_4d;
DROP TABLE test_like_4, test_like_4a, test_like_4b, test_like_4c, test_like_4d;
CREATE TABLE inhg (x text, LIKE inhx INCLUDING INDEXES, y text); /* copies indexes */
--
2.26.0
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #16342: CREATE TABLE LIKE INCLUDING GENERATED column order issue
2020-04-03 23:50 BUG #16342: CREATE TABLE LIKE INCLUDING GENERATED column order issue PG Bug reporting form <noreply@postgresql.org>
2020-04-05 11:28 ` Re: BUG #16342: CREATE TABLE LIKE INCLUDING GENERATED column order issue Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
@ 2020-04-05 15:26 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-04-09 14:48 ` Re: BUG #16342: CREATE TABLE LIKE INCLUDING GENERATED column order issue Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2020-04-05 15:26 UTC (permalink / raw)
To: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>; +Cc: ewaldo@healthetechs.com; pgsql-bugs@lists.postgresql.org
Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:
> Confirmed. Attached is a patch to fix it. Thanks for the report!
LGTM. Perhaps extend the comment block just above, adding something
like "We must fill the attmap now so it can be used to process
GENERATED default expressions in the per-column loop."
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #16342: CREATE TABLE LIKE INCLUDING GENERATED column order issue
2020-04-03 23:50 BUG #16342: CREATE TABLE LIKE INCLUDING GENERATED column order issue PG Bug reporting form <noreply@postgresql.org>
2020-04-05 11:28 ` Re: BUG #16342: CREATE TABLE LIKE INCLUDING GENERATED column order issue Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
2020-04-05 15:26 ` Re: BUG #16342: CREATE TABLE LIKE INCLUDING GENERATED column order issue Tom Lane <tgl@sss.pgh.pa.us>
@ 2020-04-09 14:48 ` Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
2020-04-09 14:55 ` RE: BUG #16342: CREATE TABLE LIKE INCLUDING GENERATED column order issue Ethan Waldo <ewaldo@healthetechs.com>
0 siblings, 1 reply; 5+ messages in thread
From: Peter Eisentraut @ 2020-04-09 14:48 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: ewaldo@healthetechs.com; pgsql-bugs@lists.postgresql.org
On 2020-04-05 17:26, Tom Lane wrote:
> Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:
>> Confirmed. Attached is a patch to fix it. Thanks for the report!
>
> LGTM. Perhaps extend the comment block just above, adding something
> like "We must fill the attmap now so it can be used to process
> GENERATED default expressions in the per-column loop."
Committed with that.
Ethan, this will be in the next minor release of PostgreSQL 12.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
^ permalink raw reply [nested|flat] 5+ messages in thread
* RE: BUG #16342: CREATE TABLE LIKE INCLUDING GENERATED column order issue
2020-04-03 23:50 BUG #16342: CREATE TABLE LIKE INCLUDING GENERATED column order issue PG Bug reporting form <noreply@postgresql.org>
2020-04-05 11:28 ` Re: BUG #16342: CREATE TABLE LIKE INCLUDING GENERATED column order issue Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
2020-04-05 15:26 ` Re: BUG #16342: CREATE TABLE LIKE INCLUDING GENERATED column order issue Tom Lane <tgl@sss.pgh.pa.us>
2020-04-09 14:48 ` Re: BUG #16342: CREATE TABLE LIKE INCLUDING GENERATED column order issue Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
@ 2020-04-09 14:55 ` Ethan Waldo <ewaldo@healthetechs.com>
0 siblings, 0 replies; 5+ messages in thread
From: Ethan Waldo @ 2020-04-09 14:55 UTC (permalink / raw)
To: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Thank you for such a timely response and the best and most fully featured database software money can't buy!
Sent from my Samsung Galaxy smartphone.
-------- Original message --------
From: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
Date: 4/9/20 10:48 AM (GMT-05:00)
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Ethan Waldo <ewaldo@healthetechs.com>, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #16342: CREATE TABLE LIKE INCLUDING GENERATED column order issue
On 2020-04-05 17:26, Tom Lane wrote:
> Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:
>> Confirmed. Attached is a patch to fix it. Thanks for the report!
>
> LGTM. Perhaps extend the comment block just above, adding something
> like "We must fill the attmap now so it can be used to process
> GENERATED default expressions in the per-column loop."
Committed with that.
Ethan, this will be in the next minor release of PostgreSQL 12.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2020-04-09 14:55 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-04-03 23:50 BUG #16342: CREATE TABLE LIKE INCLUDING GENERATED column order issue PG Bug reporting form <noreply@postgresql.org>
2020-04-05 11:28 ` Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
2020-04-05 15:26 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-04-09 14:48 ` Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
2020-04-09 14:55 ` Ethan Waldo <ewaldo@healthetechs.com>
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