Re: VACUUM FULL results in ERROR: integer out of range

Manuel Rigger <rigger.manuel@gmail.com> writes:
> Consider the example below:

> -- thread 1:
> \c db1;
> CREATE TABLE t1(c0 int);
> INSERT INTO t1(c0) VALUES(2147483647);
> UPDATE t1 SET c0 = 0;
> CREATE INDEX i0 ON t1((1 + t1.c0));
> VACUUM FULL; -- unexpected: ERROR: integer out of range

> -- thread 2:
> DROP DATABASE db2;
> CREATE DATABASE db2;

> I would expect that the VACUUM FULL executes without error. However,
> it shows an error "integer out of range", which would be expected for
> the CREATE INDEX, had the UPDATE not been executed.

This can be reproduced in a less magical way thus:

Session 1:

regression=# begin transaction isolation level serializable;
BEGIN
regression=# select * from unrelated_table;
...

Leave that sit, and in session 2 do:

regression=# CREATE TABLE t1(c0 int);
CREATE TABLE
regression=# INSERT INTO t1(c0) VALUES(2147483647);
INSERT 0 1
regression=# UPDATE t1 SET c0 = 0;
UPDATE 1
regression=# CREATE INDEX i0 ON t1((1 + t1.c0));
CREATE INDEX
regression=# vacuum t1;
VACUUM
regression=# vacuum full t1;
ERROR:  integer out of range

What's evidently happening is that since the row with c0 = 2147483647
is still potentially live to some onlooker transaction, the index
rebuild forced by VACUUM FULL is trying to create an index entry for
it.  I imagine that your original example with a concurrent database
drop/create is likewise causing a transaction to be open during the
relevant window.

Now, what's curious is that the CREATE INDEX itself didn't fail likewise.
Apparently, we have more-careful analysis of live vs. dead rows during
the initial index creation than we do during a forced rebuild, because
somehow CREATE INDEX is deciding that it needn't make an index entry
for that row, even though it was exactly as live-to-somebody at that
point as it was during the VACUUM FULL.

I haven't dug into the details of what the difference is, nor whether
it'd be practical to make the behavior the same for both cases.  It's
even possible that VACUUM FULL is doing the right thing and it's a
bug that the CREATE INDEX didn't fail (though I doubt this).

The larger point here is that even if we decide to change something
about this specific case, there are going to be closely related cases
that will fail and it won't be a bug, because construction of the
failing index entry will be semantically required.  In general, CREATE
INDEX doesn't get to ignore rows just because they're dead to the
current transaction.

            regards, tom lane