CREATE TABLE LIKE with INHERITS skips checks on generated columns

Started by shihao zhong6 days ago4 messageshackers
Jump to latest
#1shihao zhong
zhong950419@gmail.com

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

#2shihao zhong
zhong950419@gmail.com
In reply to: shihao zhong (#1)
Re: CREATE TABLE LIKE with INHERITS skips checks on generated columns

Forget patches.

Attachments:

t259267_2
v1-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
#3Zsolt Parragi
zsolt.parragi@percona.com
In reply to: shihao zhong (#2)
Re: CREATE TABLE LIKE with INHERITS skips checks on generated columns

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?

#4shihao zhong
zhong950419@gmail.com
In reply to: Zsolt Parragi (#3)
Re: CREATE TABLE LIKE with INHERITS skips checks on generated columns

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