Re: ALTER TABLE ... ADD FOREIGN KEY ... NOT ENFORCED

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Simon Riggs <simon(at)2ndQuadrant(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: ALTER TABLE ... ADD FOREIGN KEY ... NOT ENFORCED
Date: 2010-12-12 23:20:52
Message-ID: 11890.1292196052@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
> On Sun, 2010-12-12 at 17:57 -0500, Tom Lane wrote:
>> Huh? It allows you to postpone the check until commit. That's far from
>> not enforcing it.

> This clearly implies that un-enforced constraints are not checked at
> commit.

[ shrug... ] I can't argue with you about what may or may not be in an
unpublished draft of an unratified version of the standard, since I
don't have a copy. But allow me to harbor doubts that they really
intend to allow someone to force a constraint to be considered valid
without any verification. This proposal strikes me as something mysql
would do, not the standards committee. (In particular, can a constraint
go from not-enforced to enforced state without getting checked at that
time?)

Even if you're reading the draft correctly, and the wording makes it
into a released standard, the implementation you propose would break our
code. The incremental FK checks are designed on the assumption that the
constraint condition held before; they aren't likely to behave very
sanely if the data is bad. I'd want to see a whole lot more analysis of
the resulting behavior before even considering an idea like this.

regards, tom lane

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Robert Haas 2010-12-12 23:49:18 Re: ALTER TABLE ... ADD FOREIGN KEY ... NOT ENFORCED
Previous Message Robert Haas 2010-12-12 23:16:50 Re: Problem with pg_upgrade (8.4 -> 9.0) due to ALTER DATABASE SET ROLE