Re: pg_dump restore time and Foreign Keys

Simon Riggs wrote:

> If we break down the action into two parts.
>
> ALTER TABLE ... ADD CONSTRAINT foo FOREIGN KEY ... NOVALIDATE;
> which holds exclusive lock, but only momentarily
> After this runs any new data is validated at moment of data change, but
> the older data has yet to be validated.
>
> ALTER TABLE ... VALIDATE CONSTRAINT foo
> which runs lengthy check, though only grabs lock as last part of action

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.

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.

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

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support