"Alvaro Herrera" <[EMAIL PROTECTED]> 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! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers