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

"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. 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.

regards, tom lane