BUG #16342: CREATE TABLE LIKE INCLUDING GENERATED column order issue
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=#
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 TABLEpostgres=# 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.patchtext/plain; charset=UTF-8; name=0001-Fix-CREATE-TABLE-LIKE-INCLUDING-GENERATED-column-ord.patch; x-mac-creator=0; x-mac-type=0Download+43-27
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
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
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