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.

@a_horse_with_no_name - the point is I do delete all the records from the table, but still get the error (please see the example). The same is when I update the incorrect data. — Julius Tuskenis
– Julius Tuskenis, Commented Aug 16, 2021 at 7:51
@a_horse_with_no_name - yes, that's the point: 1. we have a table with incorrect data, 2. we fix the data, 3. we still can't use range index. — Julius Tuskenis
– Julius Tuskenis, Commented Aug 16, 2021 at 8:00
@a_horse_with_no_name - let's be constructive. I've provided the reproducible example of the problem I face. You can clearly see I delete all the records from the table using the DELETE statement, so no invalid data is left on it. If it is somehow not done correctly - please point it out. What more can I provide do clear up the situation? — Julius Tuskenis
– Julius Tuskenis, Commented Aug 16, 2021 at 8:13
I just tested with 13.3, and your script runs just fine, without any error, just as I would expect. There must be some mistake on your end. — Laurenz Albe
– Laurenz Albe, Commented Aug 16, 2021 at 8:54

Laurenz Albe · Accepted Answer · 2021-08-16 18:09:44Z

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.

answered Aug 16, 2021 at 18:09

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Julius Tuskenis Over a year ago

I was convinced that deleted rows are not taken into index because you can remove dublicates and add unique index in the same transaction, but I guess that is a special case. If you added a link to a documentation describing how B-tree and GiST operate it would even improve your answer. Thank you for your time.

Laurenz Albe Over a year ago

There is not a special link - that is how MVCC works in PostgreSQL. Imagine you are inside a REPEATABLE READ transaction. Now someone deletes a row from a table. Then you still have to be able to see the row during in your transaction, even if you perform an index scan.

Collectives™ on Stack Overflow

Range check error when creating GIST index on tsrange value

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related