domain over virtual generated column

From: jian he <jian(dot)universality(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: domain over virtual generated column
Date: 2025-04-28 02:45:13
Message-ID: CACJufxFNUHxuSE=g20C2aLO3d+4T_j9h0x8esMirzcC4FrLbBg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

hi.

I posted $subject at [1], but that thread is already committed,
now post it in a separate thread, so CI will test it.

While at it, I found some problem when the virtual generated column domain
is with a NOT VALID check constraint.
------------------
CREATE DOMAIN d1 AS int;
CREATE TABLE t (a int, b d1 GENERATED ALWAYS AS (a * 2) VIRTUAL);
INSERT INTO t (a, b) VALUES (4, default), (3,default);
ALTER DOMAIN d1 ADD constraint cc CHECK (VALUE < 7) NOT VALID;

SELECT * FROM t ORDER BY a, b; --error
ERROR: value for domain d1 violates check constraint "cc"
ALTER DOMAIN d1 ADD CHECK (VALUE < 9); --error
ERROR: column "b" of table "t" contains values that violate the new constraint

When a domain constraint is invalidated:
Any evaluation of a virtual generated column will expand and evaluate the
generated expression, which is wrapped in a CoerceToDomain node. CoerceToDomain
itself will compile and check all invalidated domain constraints (refer to
lookup_type_cache). As a result, any SELECT statement would trigger validation
of invalid domain constraints.
Thus, NOT VALID domain constraints on virtual generated columns do not behave as
expected, so I have disallowed them.

[1]: https://postgr.es/m/CACJufxHArQysbDkWFmvK+D1TPHQWWTxWN15cMuUaTYX3xhQXgg@mail.gmail.com

summary of attached patch:
v1-0001
we need to compute the generation expression for the domain with constraints,
thus rename ExecComputeStoredGenerated to ExecComputeGenerated.

v1-0002
soft error variant of ExecPrepareExpr, ExecInitExpr. for soft error processing
of CoerceToDomain. we don't want error messages like "value for domain d2
violates check constraint "d2_check"" while validating existing domain data, we
want something like:
ERROR: column "b" of table "gtest24" contains values that violate the
new constraint

v1-0003 virtual generation columns over domain.

Attachment Content-Type Size
v1-0001-rename-ExecComputeStoredGenerated-to-ExecComputeGenerated.patch text/x-patch 5.2 KB
v1-0003-domain-over-virtual-generated-column.patch text/x-patch 36.2 KB
v1-0002-soft-error-variant-of-ExecPrepareExpr-ExecInitExpr.patch text/x-patch 3.9 KB

Browse pgsql-hackers by date

  From Date Subject
Next Message jian he 2025-04-28 04:36:14 Re: using index to speedup add not null constraints to a table
Previous Message Peter Smith 2025-04-28 02:38:00 Re: DOCS - create publication (tweak for generated columns)