creating gist index seems to look at data ignoring transaction?

Started by Palle Girgensohnabout 7 years ago2 messageshackers
Jump to latest
#1Palle Girgensohn
girgen@pingpong.se

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

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Palle Girgensohn (#1)
Re: creating gist index seems to look at data ignoring transaction?

Palle Girgensohn <girgen@pingpong.se> writes:

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?

Index creation has to include not-yet-dead tuples in case the index gets
used by some transaction that can still see those tuples. So in this
case index entries get made for both the original and the updated versions
of the tuples in question.

regards, tom lane