Re: Tricky bugs in concurrent index build

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Greg Stark <gsstark(at)mit(dot)edu> writes:
> > Is it not possible to brute force this adding an AM method to insert without
> > the uniqueness check?
>
> Hm. Actually there already is a feature of aminsert to allow
> suppressing the unique check, but I'm not sure whether using it for
> RECENTLY_DEAD tuples helps. Seems like we have to wait to see whether
> DELETE_IN_PROGRESS deleters commit in any case.

Hm, actually don't we need both of these to make it work? We need to see
whether the deleter commits to determine whether to enforce the uniqueness
constraint on the missing tuple.

. If the deleter aborts we need to insert the tuple normally including
enforcing the constraint.

. If the deleter commits then we still need to insert the tuple but without
enforcing the constraint in case some old transaction queries the index

What would happen if we just insert DELETE_IN_PROGRESS tuples normally? Would
the only risk be that the index build would fail with a spurious unique
constraint violation? I suppose it would be pretty common though given how
updates work.

Incidentally does this point out a problem with the planner depending on
unique constraints? For old (serializable) transactions the unique index
exists and the constraint is enforced but they can still find tuples that were
deleted before the index was built and might violate the unique constraint.
Even if we had the plan invalidation mechanism that's frequently mentioned you
would still have to check constraints against your snapshot and not just
snapshotnow for planning purposes.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com