On Fri, 8 May 2026 at 16:12, Álvaro Herrera <[email protected]> wrote: > > On 2026-May-08, Ayush Tiwari wrote: > > > The shape that worried me the most was a partial unique index whose > > predicate uses a whole-row reference, e.g. WHERE rel IS NOT NULL. As > > far as I can tell, the predicate can be true at the SQL level, but > > index build and maintenance evaluate the stored predicate against the > > physical heap tuple, where the virtual column is not stored. If that > > reading is right, the index could end up with no entries for rows that > > satisfy the predicate, which would mean uniqueness is silently not > > enforced. Does that sound like a bug, or am I missing something? > > > > This is what I tried on master: > > > > CREATE TABLE t (a int, b int GENERATED ALWAYS AS (a * 2) VIRTUAL); > > CREATE UNIQUE INDEX t_a_wholerow_pred_idx ON t (a) WHERE t IS NOT NULL; > > INSERT INTO t(a) VALUES (1); > > INSERT INTO t(a) VALUES (1); -- accepted, two rows with a = 1 > > Hmm, but this also works just fine when the column b is a normal column, > so I don't see why you would want to restrict this specifically for > virtual generated columns.
Are you sure it works fine? I get differing behaviour between STORED and VIRTUAL in the script below; though indeed that's with a generated column. non-generated columns with the same value getting inserted do get the expected errors, too. But maybe whole-row IS [NOT] NULL expressions in indexes just shouldn't be allowed (marked as immutable), because you can silently corrupt the whole index by (e.g.) invalidating the IS NOT NULL condition by adding a new default-(non-)NULL column... I think it's one of the few expression types that isn't captured by the immutable-expression-checker, though there may be more. Kind regards, Matthias van de Meent ---- script: DROP TABLE IF EXISTS t5; /* if VIRTUAL instead of STORED, the script succeeds; even if b is marked NOT NULL. With STORED, it fails */ CREATE TABLE t5 (a int, b int GENERATED ALWAYS AS (a * 2) STORED); CREATE UNIQUE INDEX t5_a_wholerow_pred_idx ON t5 (a) WHERE t5 IS NOT NULL; INSERT INTO t5(a) VALUES (1); ANALYZE t5; EXPLAIN SELECT t5 IS NOT NULL FROM t5; SELECT t5 IS NOT NULL FROM t5; INSERT INTO t5(a) VALUES (1); ANALYZE t5; -- insert fails if STORED, without STORED in column definition it succeeds. EXPLAIN SELECT t5 IS NOT NULL FROM t5; SELECT t5 IS NOT NULL FROM t5; vs DROP TABLE IF EXISTS t5; CREATE TABLE t5 (a int, b int); CREATE UNIQUE INDEX t5_a_wholerow_pred_idx ON t5 (a) WHERE t5 IS NOT NULL; INSERT INTO t5(a, b) VALUES (1, 2); ANALYZE t5; explain select t5 is not null from t5; select t5 is not null from t5; INSERT INTO t5(a, b) VALUES (1, 2); ANALYZE t5; EXPLAIN SELECT t5 IS NOT NULL FROM t5; SELECT t5 IS NOT NULL FROM t5;
