Manuel Rigger <rigger.manuel@gmail.com> writes:
> CREATE TABLE t0(c0 TEXT);
> INSERT INTO t0(c0) VALUES('b'), ('a');
> ANALYZE t0;
> INSERT INTO t0(c0) VALUES (NULL);
> UPDATE t0 SET c0 = 'a';
> CREATE INDEX i0 ON t0(c0);
> SELECT * FROM t0 WHERE 'baaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' > t0.c0;
> -- unexpected: ERROR: found unexpected null value in index "i0"
Nifty. As before, the way to make this reproducible is to do it with
another open transaction holding a snapshot, so that the NULL entry
has to be reflected in the index. (I'm not sure why the HOT-update
exception doesn't apply here, but apparently it doesn't.) That is,
it's sufficient to set up one session with
begin transaction isolation level serializable;
select * from some_table;
and then run the above script in another session.
The error is coming from the planner's get_actual_variable_range:
/* Shouldn't have got a null, but be careful */
if (isnull[0])
elog(ERROR, "found unexpected null value in index \"%s\"",
RelationGetRelationName(indexRel));
and I think it's entirely within its rights to complain, because it
set up the scan key to reject nulls. In short, somebody seems to
have broken btrees' processing of SK_ISNULL | SK_SEARCHNOTNULL scankeys,
and they broke it in v11, because prior versions don't show this failure.
regards, tom lane
