Re: Improve checking for pg_index.xmin

On 01/11/2019 01:50, Alexander Korotkov wrote:
> 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.

Hmm. Maybe you could construct a case like that with a creative mix of 
stable and volatile functions? Using GetOldestSnapshot() would be safer.

- Heikki