JSON_ARRAY / JSON_OBJECT over named composite types: stale indexes after ALTER TYPE, no GUC =========================================================================================== Context: shihao's report that named composite types still get a stale expression index after ALTER TYPE ... ADD ATTRIBUTE ts timestamptz. This sweep checks whether that is the only path, and whether create-time detection is complete. Builds (all --enable-cassert --enable-debug, CFLAGS=-O2): r18 18.6 r19rc1 19rc1 (0b8bff042a9) master 20devel, source tree at f9ccc5e4f92 jian's v2-0001 was NOT measured in this run (see "Caveats"). Oracle (oracle.sql): the same predicate is counted twice, once with enable_seqscan=off and once with enable_indexscan/indexonlyscan/bitmapscan=off. EXPLAIN confirms the first plan uses the index. The row is always inserted BEFORE the ALTER. Then REINDEX and count again. Files: sweep.sql (all cases), oracle.sql, run.sh (one fresh cluster per build), out/sweep..out (full psql output). 1. Verdict ---------- (a) Create-time detection works. A composite that already contains timestamptz when the index is built is rejected on 19rc1 and master, both directly (K0) and nested (K0n). json_check_mutability() does recurse into the attributes. (b) shihao's case is not specific to timestamptz. The JSON value of a composite contains the attribute names and one entry per attribute, so any change to the type's shape changes the value. With an all-int composite and no GUC change, ADD ATTRIBUTE, RENAME ATTRIBUTE and DROP ATTRIBUTE each leave the index stale. So do ALTER TABLE ... RENAME COLUMN / ADD COLUMN on a table whose row type is used as a column type. (c) The same happens through a nested composite, a domain over a composite, an array of a composite, JSON_OBJECT, and RETURNING json (indexed through ::text). Beyond plain btree indexes it also affects UNIQUE indexes, partial-index predicates, STORED generated columns and partition keys. (d) 18.6 rejects every CREATE in this sweep. 19rc1 and master give identical results. (e) The ALTER is not blocked. ADD / RENAME / DROP ATTRIBUTE go through on a type used by an indexed column. Only ALTER ATTRIBUTE ... TYPE is refused ('cannot alter type "c9" because column "t9.v" uses it'), on every build. 2. Matrix --------- "rej" = CREATE rejected (must be marked IMMUTABLE). "i/s" = index count / seq count. "*" = index and seq disagree. "+REINDEX" = after REINDEX of that index. case r18 r19rc1 master +REINDEX (19/master) S0 shihao: ADD ATTRIBUTE ts + TimeZone rej 0/1 * 0/1 * - K0 composite with timestamptz at CREATE rej rej rej - K0n nested, timestamptz inside, at CREATE rej rej rej - C1 ADD ATTRIBUTE y int rej 0/1 * 0/1 * 1/1 C2 RENAME ATTRIBUTE rej 0/1 * 0/1 * 1/1 C3 DROP ATTRIBUTE rej 0/1 * 0/1 * 1/1 C4 table row type, ALTER TABLE RENAME COLUMN rej 0/1 * 0/1 * 1/1 C4b table row type, ALTER TABLE ADD COLUMN rej 0/1 * 0/1 * (not run) C5 nested composite, RENAME in inner type rej 0/1 * 0/1 * 1/1 C6 domain over composite, RENAME rej 0/1 * 0/1 * 1/1 C7 array of composite, RENAME rej 0/1 * 0/1 * 1/1 C8 JSON_OBJECT('k' VALUE v), RENAME rej 0/1 * 0/1 * (not run) C8b JSON_ARRAY(v RETURNING json)::text, RENAME rej 0/1 * 0/1 * (not run) C9 ALTER ATTRIBUTE TYPE on an indexed type refused on every build U1 UNIQUE index, RENAME, insert same value rej dup dup REINDEX fails P1 partial index predicate, RENAME rej 1/0 * 1/0 * 0/0 G1 STORED generated column, RENAME rej stale stale - K1 LIST partition key, RENAME rej 0 vs 1 0 vs 1 - Details: - C1 after the ALTER the row reads [{"x": 1, "y": null}]; C2 reads [{"z": 1}]; C5 reads [{"a": {"z": 1}}]. - U1: the second INSERT of row(1) is accepted. The table then holds 2 rows with 1 distinct key; the index finds 1, a seq scan 2. REINDEX fails with "could not create unique index". - P1: the index on (id) WHERE json_array(v) = '[{"x": 1}]' still returns the row after the RENAME, although the predicate is now false for it (seq scan: 0). - G1: stored [{"x": 1}], recomputed [{"z": 1}]. - K1: count is 0 with enable_partition_pruning=on and 1 with it off. The row stays in the partition for '[{"x": 1}]' and now violates that partition's constraint. 3. Minimal repro (same shape as shihao's, no mutable type, no GUC) ------------------------------------------------------------------ CREATE TYPE c1 AS (x int); CREATE TABLE t1 (v c1); INSERT INTO t1 VALUES (row(1)); CREATE INDEX t1_i ON t1 ((json_array(v returning jsonb))); ALTER TYPE c1 ADD ATTRIBUTE y int; SELECT count(*) FROM t1 WHERE json_array(v returning jsonb) = json_array(row(1, null)::c1 returning jsonb); -- 19rc1 / master: index scan 0, seq scan 1; REINDEX gives 1. 18.6 rejects the CREATE INDEX. 4. Mechanism (line numbers from master at f9ccc5e4f92) ------------------------------------------------------ contain_mutable_functions_walker() (optimizer/util/clauses.c:424) asks to_json_is_immutable() / to_jsonb_is_immutable() about the type of each argument of a JsonConstructorExpr. Both call json_check_mutability() (utils/adt/jsonfuncs.c:6078). For TYPTYPE_COMPOSITE it reads the current tuple descriptor (lookup_rowtype_tupdesc(typoid, -1), jsonfuncs.c:6103) and recurses into each live attribute. The answer is only valid for the type's shape at that moment, and nothing records it: - composite_to_json() (json.c:521) and composite_to_jsonb() (jsonb.c:978) emit the attribute names and one entry per live attribute, so the value depends on catalog state even when every attribute type is immutable. That is the same reason record_out() is STABLE. - ALTER TYPE ... ADD / DROP / RENAME ATTRIBUTE and ALTER TABLE ... ADD / RENAME COLUMN on the row type are allowed while an index expression depends on the type (measured: C1-C8). find_composite_type_dependencies() (commands/tablecmds.c:6982) only stops ALTER ATTRIBUTE TYPE here (C9). So the recursion is not missing. What is missing is a way to account for the composite's shape changing after the check. A recheck limited to "a mutable attribute was added" would leave C1-C8, U1, P1, G1 and K1 open. 5. Caveats ---------- - jian's v2-0001 was not measured in this run. From reading it, v2 changes only record, anyarray and anycompatiblearray; named composites still go through the TYPTYPE_COMPOSITE recursion, so these vectors are expected to remain. This is an inference, not a result. - No fix is measured here. - The REINDEX step was not run for C4b, C8 and C8b; their divergence is shown without it. - Scalar types whose text output depends on a GUC (interval, bytea_output, float, money) were not part of this sweep. - The exact commit of the master binary is not recorded separately from its source tree.