Re: BUG #14596: False primary/unique key constraint violations

On Wed, Mar 22, 2017 at 8:41 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> > On Wed, Mar 22, 2017 at 7:45 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >> ... Be aware that this can be
> >> significantly slower than immediate uniqueness checking.
>
> > Given that the default SET CONSTRAINT behavior is IMMEDIATE, and that
> > triggers are defined DEFERRABLE, what harm would there be to default to
> the
> > standard mandated behavior noted above?
>
> The performance hit is one very large problem.

​I wasn't expecting a noticeable performance hit unless actual deferred
behavior was requested for the transaction...​

Another is that we don't
> support using deferrable indexes for purposes such as foreign keys,
> which means that
>
> create table x (f1 int primary key);
> create table y (f1 int references x);
>
> would fail if "primary key" defaulted to meaning "deferrable". So the
> standards noncompliance would just move somewhere else.
>

​Interesting...I've only resorted to using this once in an ETL scenario
(recently, which is why the alter table annoyance was on my mind) and the
particular implementation didn't require a FK to point back to the
problematic PK.

​Compliance doesn't really move, though, we are just non-compliant twice
right now - presuming that the standard doesn't allow for FK to only target
non-deferrable PKs. While this is noted in the description of FK in CREATE
TABLE it isn't noted in the Compatibility section. Only the default
definition of PK deferrability is.

This does seem like a show-stopper for any changes to the defaults in this
area. It doesn't preclude making changing deferrabiltiy easier (i.e., not
involving drop index) for those cases when it is the only answer. Failing
the alter table if FK constraints exist would be expected.

David J.