Hi, I have a table with two dates, timeframe_begin and timeframe_end.
I'd like to use daterange operators on this table, and an easy way would be to set up an index using gist on daterange(timeframe_begin, timeframe_end, '[]'); I noticed some bad data where end < begin, so I modified these first, and tried to vcreate the index in the same transaction. The index creation does not notice the data changes. It seems creating the gist index this is not transaction safe? db=> begin; BEGIN db=> update group_info set timeframe_begin = timeframe_end where timeframe_begin > timeframe_end; UPDATE 76 db=> create index group_info_timeframe_idx on group_info using gist (daterange(timeframe_begin, timeframe_end, '[]')); ERROR: range lower bound must be less than or equal to range upper bound db=> abort; ROLLBACK db=> begin; BEGIN db=> update group_info set timeframe_begin = timeframe_end where timeframe_begin > timeframe_end; UPDATE 76 db=> commit; COMMIT db=> begin; BEGIN db=> create index group_info_timeframe_idx on group_info using gist (daterange(timeframe_begin, timeframe_end, '[]')); CREATE INDEX db=> commit; COMMIT db=> I cannot find anything about gist indexes not being transaction safe? It is reprodcable on different machines with different datasets. Is this correct behaviour? This is on PostgreSQL-9.6. Cheers, Palle
