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
transactionsafe?
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
differentdatasets. Is this correct behaviour?
This is on PostgreSQL-9.6.
Cheers,
Palle
