Re: Exhaustive list of what takes what locks

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> The problem is that constraints can affect the query plan. If a
> transaction sees the constraint in the system catalogs (under
> SnapshotNow) but the table data doesn't conform (under some earlier
> snapshot) and if the chosen plan depends on the validity of the
> constraint, then we've got trouble. At least when running at READ
> COMMITTED, taking an AccessExclusiveLock protects us against that
> hazard (I'm not exactly sure what if anything protects us at higher
> isolation levels... but I hope there is something).

Interesting point. If we really wanted to make that work "right",
we might have to do something like the hack that's in place for CREATE
INDEX CONCURRENTLY, wherein there's a notion that an index can't be used
by a transaction with xmin before some horizon. Not entirely convinced
it's worth the trouble, but ...

> Now, it's true that in the specific case of a foreign key constraint,
> we don't currently have anything in the planner that depends on that.
> But I'm hoping to get around to working on inner join removal again
> one of these days.

Yeah, that sort of thing will certainly be there eventually.

regards, tom lane