Temporal fkey bugs

Hi,

While looking at FOR PORTION OF (see [1]), some AI tool noted that FPO can
lead to issues with temporal foreign keys. I don't think the issues were
really related to FPO, hence this new thread.

I don't think these are particularly hard to fix.

1) wrong lock level used with exclusion constraints

See exclusion-lock.spec.

The problem is that the constraint is an exclusion constraint, which relcache
doesn't include in the set of key-columns (as it's not unique). Which in turn
means that FOR KEY SHARE is used, which does not conflict with the UPDATE.

2) VALIDATE CONSTRAINT is broken

See validation.sql

QueueFKConstraintValidation() allocates a zeroed NewConstraint but omits
setting conwithperiod from conperiod. That leads to taking the wrong path in
validateForeignKeyConstraint().

FWIW, the issue it flagged with FPO was just that there can be temporary
spurious errors due to fkeys in some edge cases. But those are afaict also
present for non-FPO cases, and are arguably correct (the "problem" is what
snapshot is used to look for required rows, after waiting for the row lock on
a row deletion - a since then newly inserted row is not discovered).

Greetings,

Andres Freund

[1] https://postgr.es/m/vquveff5flfpsgsd55dkjqplhphziah7a7kggnemzfv5krrhet%40jxp5ubrpmxhy