shihao zhong <[email protected]> writes:
> Named composite types still have the problem. ALTER TYPE can add a
> timestamptz after the index is built.
I measured this on 18.6, 19rc1 and master, and it is broader than a mutable
attribute: a pure structural change does it too, with no mutable type and no
GUC. Adding a plain int attribute is enough.
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);
The index scan returns 0 and a seq scan returns 1; REINDEX restores 1, so the
entries are stale. 18 rejects the CREATE INDEX. RENAME ATTRIBUTE and DROP
ATTRIBUTE do the same, and so does ALTER TABLE ... RENAME COLUMN / ADD COLUMN
on a table's rowtype.
The reason is that the jsonb output carries the attribute names and their
number. The immutability verdict is computed once, at CREATE INDEX, from the
type's definition at that moment, and the catalog does not block a later
ADD / RENAME / DROP ATTRIBUTE (only ALTER ATTRIBUTE ... TYPE is rejected). So
the timestamptz case is one instance of a more general problem, and a recheck
that only looks for a mutable attribute would leave the structural changes
open.
The same expression misbehaves in the other places it can be used, all on
19rc1 and master and all rejected by 18:
- a unique index takes a second row with the same key, and a later REINDEX
fails with "could not create unique index";
- a partial index returns a row that no longer matches its predicate;
- a STORED generated column keeps [{"x": 1}] while a recompute gives
[{"z": 1}];
- a partition key stops pruning to the right partition, and the row ends up
violating its partition constraint.
It also reaches through a nested composite, a domain over a composite and an
array of a composite.
The full matrix, the minimal repro and the per-version output on
18.6 / 19rc1 / master are in the attached evidence.txt.
regards,
Manu
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.<build>.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.
