Re: pg_dump restore time and Foreign Keys

"Alvaro Herrera" <alvherre(at)commandprompt(dot)com> writes:

> The problem I see with this approach in general (two-phase FK creation)
> is that you have to keep the same transaction for the first and second
> command, but you really want concurrent backends to see the tuple for
> the not-yet-validated constraint row.

Do you? It seems like having a constraint which is enforced on any new
operations but which doesn't guarantee that existing records satisfy it is a
useful feature in itself -- separating the two concepts "this property is true
for all records" and "any action taken must leave the record with this
property"

ISTM you can validate an "invalid" constraint using any snapshot taken at any
time >= the original snapshot. As long as the constraint is being enforced for
all transactions which start after the validating snapshot's xmin then when
it's done it can know the constraint is valid.

Taking a lock on the table to create the constraint certainly leaves that
property fulfilled. Actually it seems we could not take any lock and just
check when it comes time to do the validation that the snapshot's xmin is >=
the xmin on the constraint. I'm starting to get leery of all these tightly
argued bits of logic though. Each one on its own is safe but the resulting
system is getting to be quite complex.

> Another benefit that could arise from this is that the hypothetical
> VALIDATE CONSTRAINT step could validate more than one constraint at a
> time, possibly processing all the constraints with a single table scan.

Interesting.

> Perhaps VALIDATE CONSTRAINT could be handled as an automatic commit-time
> action.

I don't really like this, at least not as the only option, because as I said
above and Robert Treat also said, it could be useful to have the constraint in
place for new operations but check it for the existing data at some later
date. (Or even never)

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's Slony Replication support!