expand virtual generated columns in get_relation_constraints()

On Wed, Sep 3, 2025 at 11:05 PM Peter Eisentraut <peter@eisentraut.org> wrote:

We (I) missed expanding virtual generated columns in
get_relation_constraints() in plancat.c. That way, some opportunities
for constraint exclusion will be missed if a constraint contains virtual
generated columns, as can be shown in the attached test case (thanks to
Richard Guo). Simple fix attached.

I'm afraid this fix isn't thorough: it covers only CHECK constraints
but not NOT NULL or partitioning constraints. For example,

create table vt (a int, b int generated always as (a * 2) virtual not null);
set constraint_exclusion to on;

explain (costs off) select * from vt where b is null;
QUERY PLAN
-----------------------------
Seq Scan on vt
Filter: ((a * 2) IS NULL)
(2 rows)

We should get a dummy Result rather than a SeqScan.

What I have in mind is something like the attached v2, which covers
all types of constraints.

- Richard

On 11.09.25 04:16, Richard Guo wrote:

On Wed, Sep 3, 2025 at 11:05 PM Peter Eisentraut <peter@eisentraut.org> wrote:

We (I) missed expanding virtual generated columns in
get_relation_constraints() in plancat.c. That way, some opportunities
for constraint exclusion will be missed if a constraint contains virtual
generated columns, as can be shown in the attached test case (thanks to
Richard Guo). Simple fix attached.

I'm afraid this fix isn't thorough: it covers only CHECK constraints
but not NOT NULL or partitioning constraints. For example,

create table vt (a int, b int generated always as (a * 2) virtual not null);
set constraint_exclusion to on;

explain (costs off) select * from vt where b is null;
QUERY PLAN
-----------------------------
Seq Scan on vt
Filter: ((a * 2) IS NULL)
(2 rows)

We should get a dummy Result rather than a SeqScan.

What I have in mind is something like the attached v2, which covers
all types of constraints.

Thanks, I have committed that.