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 |
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) |