Re: Deferrable constraint execution not respecting "initially immediate"?

"David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> On Mon, Jul 10, 2017 at 9:25 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> The solution for that is documented: declare the unique constraint as
>> deferrable.
>>
>> regression=# CREATE TABLE T_UNIK (ID INT UNIQUE deferrable );
>> CREATE TABLE
>> regression=# INSERT INTO T_UNIK VALUES (1), (2), (3), (4), (5);
>> INSERT 0 5
>> regression=# UPDATE T_UNIK SET ID = ID + 1;
>> UPDATE 5

> ​I was expecting the above to require a "set constraints deferred" since
> "initially immediate" is the default.

Right, but "deferrable initially immediate" still means "at the end of the
statement", not "after each row". "deferrable initially deferred" means
"at the end of the transaction". In this example there's no real
difference between those two behaviors.

> From "CREATE TABLE":
> "A constraint that is not deferrable will be checked immediately after
> every command."

> I think the above should be "after every row" instead of "after every
> command".

I believe that FK constraints work differently from indexes in this
regard. Not sure that we want to get into that level of detail here.

regards, tom lane