Hi, While playing with virtual generated columns, I noticed that DefineIndex seems to reject indexes on virtual generated columns when they are referenced directly in index expressions or predicates, but it seems it is missing whole-row Vars. My understanding is that a whole-row Var on a relation that has any virtual generated column logically includes that column, so I was wondering whether allowing it could bypass the existing virtual-column index restriction.
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
If this is indeed something we want to disallow, I have attached a small
patch that I think extends the existing check in DefineIndex so that
varattno == 0 (whole-row Var) is also rejected when the indexed relation
has virtual generated columns. I tried to keep whole-row references on
relations without virtual generated columns working, so existing
whole-row expression indexes should not be affected, but I would
appreciate other eyes on whether that is the right scope.
Thoughts?
Regards,
Ayush
v1-0001-Disallow-whole-row-index-references-with-virtual-generated-columns.patch
Description: Binary data
