CREATE TABLE LIKE with INHERITS skips checks on generated columns
Hi hackers,
I ran into this while extending the patch in [1]/messages/by-id/E1xFKs5-0000000116b-2Cge@gemulon.postgresql.org to plain inheritance,
as Zsolt suggested.
CREATE TABLE ... (LIKE ...) INHERITS adds the copied defaults and
generation expressions after the table is created. That path skips the
checks the explicit syntax gets. In the worst case a stored generated
column is never computed:
create table p (a int, b int generated always as (a * 10) stored);
create table s (b int, v int generated always as (b * 2) stored);
create table c (like s including generated) inherits (p);
insert into c (a) values (1);
select * from c;
a | b | v
---+----+---
1 | 10 |
(1 row)
The patch does the same checks as the explicit syntax. When the copied
expression replaces an inherited one, it first drops the internal
dependency like SET EXPRESSION does. The tests are in a separate patch.
[1]: /messages/by-id/E1xFKs5-0000000116b-2Cge@gemulon.postgresql.org
Forget patches.
Attachments:
t259267_2v1-0001-Check-LIKE-defaults-and-generation-expressions-again.patchapplication/octet-stream; name=v1-0001-Check-LIKE-defaults-and-generation-expressions-again.patchDownload+66-6
v1-0002-Add-tests-for-LIKE-with-INHERITS-on-generated-column.patchapplication/octet-stream; name=v1-0002-Add-tests-for-LIKE-with-INHERITS-on-generated-column.patchDownload+47-1
Hello!
Thanks for finding this, I missed this case during the review.
create table p5 (a int, b int generated always as (a * 2) virtual);
create table s5 (a int, b int generated always as (a * 3) virtual);
create table e5 (a int, b int generated always as (a * 3) virtual) inherits (p5); -- fails with the patch in the other thread
create table c5 (like s5 including generated) inherits (p5); -- fails on master (but only with an elog), works with the patch
I think c5 should keep failing, but most likely with a proper ereport?
Hi Zsolt,
I think c5 should keep failing, but most likely with a proper ereport?
Thanks for looking. On master alone a child can override an inherited
virtual expression, so e5 works there. v1 lets c5 through to match.
With the patch in [1]/messages/by-id/CAGRkXqSL2fnh-EWSHQ878+LdpG8z9Y8+YQ1UkSG_vftr1BSvzQ@mail.gmail.com both should fail, so I'll add that check to the
LIKE path in whichever patch goes in second.
[1]: /messages/by-id/CAGRkXqSL2fnh-EWSHQ878+LdpG8z9Y8+YQ1UkSG_vftr1BSvzQ@mail.gmail.com
/messages/by-id/CAGRkXqSL2fnh-EWSHQ878+LdpG8z9Y8+YQ1UkSG_vftr1BSvzQ@mail.gmail.com
Thanks,
Shihao