Hi!
Our customer faced with issue, when index is invisible after creation.
The reproducible case is following.
$ psql db2
# begin;
# select txid_current();
$ psql db1
# select i as id, 0 as v into t from generate_series(1, 100000) i;
# create unique index idx on t (id);
# update t set v = v + 1 where id = 10000;
# update t set v = v + 1 where id = 10000;
# update t set v = v + 1 where id = 10000;
# update t set v = v + 1 where id = 10000;
# update t set v = v + 1 where id = 10000;
# drop index idx;
# create unique index idx on t (id);
# explain analyze select v from t where id = 10000;
There is no issue if there is no parallel session in database db2.
The fact that index visibility depends on open transaction in
different database is ridiculous for users.
This happens so, because we're checking that there is no broken HOT
chains after index creation by comparison pg_index.xmin and
TransactionXmin. So, we check that pg_index.xmin is in the past for
current transaction in lossy way by comparison just xmins. Attached
patch changes this check to XidInMVCCSnapshot().
With patch the issue is gone. My doubt about this patch is that it
changes check with TransactionXmin to check with GetActiveSnapshot(),
which might be more recent. However, query shouldn't be executer with
older snapshot than one it was planned with.
Any thoughts?
------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
