| From: | Zsolt Parragi <zsolt(dot)parragi(at)percona(dot)com> |
|---|---|
| To: | Alberto Piai <alberto(dot)piai(at)gmail(dot)com> |
| Cc: | pgsql-hackers(at)lists(dot)postgresql(dot)org, Xiaoxuan Li <xiaoxuan(dot)li(dot)dev(at)gmail(dot)com>, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>, Matthias van de Meent <boekewurm+postgres(at)gmail(dot)com>, Álvaro Herrera <alvherre(at)kurilemu(dot)de> |
| Subject: | Re: Adding a stored generated column without long-lived locks |
| Date: | 2026-11-18 22:40:39 |
| Message-ID: | CAN4CZFOTELLA9025Sje_5bOf-DqZKnK5gg64nmEU20dgWVqtGw@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
Hello!
I started testing this patchset (with some LLM help), and identified
some issues:
1. The generation expression can change when it is reparsed based on
session GUCs
CREATE TABLE g (a float8, b float8);
INSERT INTO g VALUES (1, 1 + '3.141592653589793'::float8);
ALTER TABLE g ADD CONSTRAINT c CHECK (pg_datum_image_equal(b, a +
'3.141592653589793'::float8));
SET extra_float_digits = 0;
ALTER TABLE g ALTER b ADD GENERATED USING CONSTRAINT c STORED;
RESET extra_float_digits;
\d g
b | double precision | generated always as (a +
'3.14159265358979'::double precision) stored
UPDATE g SET a = a;
-- ERROR: new row for relation "g" violates check constraint "c"
-- DETAIL: Failing row contains (1, 4.14159265358979).
float4/timestamptz is also affected.
2. BEFORE triggers with WHEN on NEW.b survive the conversion
CREATE TABLE t (a int, b int); INSERT INTO t VALUES (1, 2);
ALTER TABLE t ADD CONSTRAINT c CHECK (pg_datum_image_equal(b, a*2));
CREATE TRIGGER w_null BEFORE INSERT OR UPDATE ON t FOR EACH ROW WHEN
(NEW.b IS NULL) EXECUTE FUNCTION trg();
CREATE TRIGGER w_100 BEFORE INSERT OR UPDATE ON t FOR EACH ROW WHEN
(NEW.b = 100) EXECUTE FUNCTION trg();
ALTER TABLE t ALTER b ADD GENERATED USING CONSTRAINT c STORED; -- succeeds
INSERT INTO t (a) VALUES (50); -- fires w_null, not w_100, stored b = 100
-- pg_dump | psql:
-- ERROR: BEFORE trigger's WHEN condition cannot reference NEW
generated columns
CREATE TRIGGER refuses this combination (trigger.c, "BEFORE trigger's
WHEN condition cannot reference NEW generated columns"), because the
generated value isn't computed yet when WHEN runs.
Note that this is partially preexisting, the whole-row variant (WHEN
(NEW IS DISTINCT FROM OLD)) is already reachable on master with ADD
COLUMN ... GENERATED, but the NEW.b one is new.
3. Multiple inheritance
CREATE TABLE p1 (a int, b int); CREATE TABLE p2 (a int, b int);
CREATE TABLE c () INHERITS (p1, p2);
ALTER TABLE p1 ADD CONSTRAINT k CHECK (pg_datum_image_equal(b, a*2));
INSERT INTO c VALUES (1, 2);
ALTER TABLE p1 ALTER b ADD GENERATED USING CONSTRAINT k STORED; -- succeeds
ALTER TABLE p1 DROP CONSTRAINT k;
UPDATE p2 SET b = 999;
SELECT a, b FROM c; -- 1 | 999 (expression gives 2)
-- pg_dump | psql:
-- ERROR: inherited column "b" has a generation conflict
-- ERROR: relation "public.c" does not exist
c.b becomes generated while p2.b stays a regular column. This seems to
come from DROP EXPRESSION: it has the same problem in the other
direction on master (drop the expression on one of two generated
parents), which probably deserves a separate fix.
4. Typed tables are converted although CREATE TABLE OF refuses generated columns
CREATE TYPE tt AS (a int, b int);
CREATE TABLE s6 OF tt;
INSERT INTO s6 VALUES (1, 2);
ALTER TABLE s6 ADD CONSTRAINT c CHECK (pg_datum_image_equal(b, a + 1));
ALTER TABLE s6 ALTER b ADD GENERATED USING CONSTRAINT c STORED; -- succeeds
-- pg_dump | psql:
-- ERROR: generated columns are not supported on typed tables
-- ERROR: relation "public.s6" does not exist
5. FK check also rejects the referenced side
CREATE TABLE pk (a int, b int UNIQUE);
CREATE TABLE fk (x int REFERENCES pk(b) ON UPDATE CASCADE);
ALTER TABLE pk ADD CONSTRAINT k CHECK (pg_datum_image_equal(b, a*2));
ALTER TABLE pk ALTER b ADD GENERATED USING CONSTRAINT k STORED;
-- ERROR: cannot convert column "b" to generated
-- DETAIL: Column "b" is part of a foreign key with an ON DELETE / ON
UPDATE action.
Master only restricts the actions when the referencing columns are
generated, a generated UNIQUE column can be referenced with ON UPDATE
CASCADE.
The detail is also a bit misleading, ON DELETE CASCADE is accepted.
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Melanie Plageman | 2026-11-18 22:45:54 | Re: Fix ReadStream handling when heap rescan strategy changes |
| Previous Message | Koshino Taiki | 2026-11-18 22:25:54 | Re: Proposal: Include CHECK constraint expression in violation error DETAIL |