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