creating gist index seems to look at data ignoring transaction?

Поиск
Список
Период
Сортировка
От Palle Girgensohn
Тема creating gist index seems to look at data ignoring transaction?
Дата
Msg-id CE8CCF7F-B690-4ED4-9A07-8995BC8E546C@pingpong.se
обсуждение исходный текст
Ответы Re: creating gist index seems to look at data ignoring transaction?  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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
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




В списке pgsql-hackers по дате отправления:

Предыдущее
От: Heikki Linnakangas
Дата:
Сообщение: Re: Pluggable Storage - Andres's take
Следующее
От: Ibrar Ahmed
Дата:
Сообщение: Re: pgbench - add minimal stats on initialization