Range check error when creating GIST index on tsrange value

Question

Fixing range bound data and creating gist tsrange index causes exception. I can guess the PostgreSQL sees old version of records and takes them into account when creating the gist index.

You can reproduce it using this script:

BEGIN;
CREATE  TABLE _test_gist_range_index("from" timestamp(0) WITHOUT time zone, "till" timestamp(0) WITHOUT time zone);
--let's enter some invalid data
INSERT INTO _test_gist_range_index VALUES ('2021-01-02', '2021-01-01');
--let's fix the data
DELETE FROM _test_gist_range_index;
CREATE INDEX idx_range_idx_test2 ON _test_gist_range_index USING gist (tsrange("from", "till", '[]'));
COMMIT;

The result is:

SQL Error [22000]: ERROR: range lower bound must be less than or equal to range upper bound

db<>fiddle

I've tested this on all versions of PostgreSQL starting from v9.5 and ending with v13 using db<>fiddle. The result is the same on all of them.

The same error is received if we fix the data using "UPDATE".

Is there a way to fix the data and have range index on it? Maybe there is a way to clean the table somehow from those old values?..

EDIT

It seems that the exception is raised only if data correcting statements (DELETE in my example) and CREATE INDEX statement are in the same transaction. If I DELETE and COMMIT first, and then creating the index succeeds.

Answer 1

That is working as expected and not a bug.

When you delete a row in a PostgreSQL table, it is not actually deleted, but marked as invisible. Similarly, updating a row creates a new version of the row, but the old version is retained and marked invisible. This is the way how PostgreSQL implements multiversioning: concurrent transactions can still see the "invisible" data. Eventually, invisible rows are reclaimed by VACUUM.

Now a B-tree or GiST index contains one entry for each row version in the table, unless the row version is not visible by anybody (is dead). This explains why a deleted row will still cause an error if the data don't form a valid range.

If you run the statements in autocommit mode on an otherwise idle database, the deleted rows are dead, and no index entry has to be created.