Deferrable constraint execution not respecting "initially immediate"?

In bug # 14739 Tom Lane wrote the following. My response follows but I
decided to create a new thread since the topic for 14739 is about encoding
and not the constraint itself.

On Mon, Jul 10, 2017 at 9:25 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

fbd@datasapiens.com writes:

I am testing PG on this query :
CREATE TABLE T_UNIK (ID INT UNIQUE);
INSERT INTO T_UNIK VALUES (1), (2), (3), (4), (5);
UPDATE T_UNIK SET ID = ID + 1;

I know that PG is unable to do this set based operation properly because

it

does it row by row (which is a nonsense since it works on many RDBMS)

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=# U
​​
PDATE 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.

create table t_unik (id int unique deferrable initially immediate);
​
It appears to be useful, but undocumented, that changing the primary mode
to "deferrable" also changes the default timing to "initially deferred" -
irrespective of whether the constraint itself is defined as initially
immediate or initially deferred. i.e., changing just the create table to
explicitly "deferrable initially immediate" doesn't provoke the duplicate
key error like I was expecting it to.

Thinking on it further I believe the issue is that regardless of whether
the timing is immediate or deferred a deferrable constraint never validates
during the execution of an individual command while an immediate constraint
does.

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". My reading of this is that "command" and "statement" are the
same thing and since the only way to get a unique violation is to be
checking intra-command the above is wrong.

I read the sequence "deferrable initially immediate" as "deferrable
initially "not deferred"" and expect the same behavior as a constraint not
defined as "deferrable" unless some other action, at the transaction level,
is taken. In this case the example doesn't "set constraints" and so the
original failure should persist.

Ultimately my interpretation ends up working just fine because issuing set
constraints in a transaction is just a more liberal directive.

David J.