creating gist index seems to look at data ignoring transaction?

From: Palle Girgensohn <girgen(at)pingpong(dot)se>
To: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: creating gist index seems to look at data ignoring transaction?
Date: 2019-04-11 12:41:25
Message-ID: CE8CCF7F-B690-4ED4-9A07-8995BC8E546C@pingpong.se
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Ibrar Ahmed 2019-04-11 12:43:09 Re: pgbench - add minimal stats on initialization
Previous Message Heikki Linnakangas 2019-04-11 11:52:40 Re: Pluggable Storage - Andres's take