Re: BUG or strange behaviour of update on primary key

On tis, 2011-10-18 at 01:30 +0200, desmodemone wrote:
>
> alter table testup DROP CONSTRAINT testup_pkey ;
>
> alter table testup add primary key(a) DEFERRABLE INITIALLY
> IMMEDIATE ;
>
> update testup set a=a+1 ;
> UPDATE 2
>
> commit;
>
>
> Seems an Oracle bug not Postgresql one!

Oracle's behavior is OK. PostgreSQL's default behavior is wrong in the
sense that it checks the constraint even in invisible states *during*
the statement, rather than only after. Marking the constraint
DEFERRABLE (which means deferrable to the end of the *transaction*, not
statement) is a red herring to get the system to do it right(er),
because there is no separate syntax to say deferrable to end of
statement.

Basically, this is maintaining historical buggy behavior for
performance. If you want correct and slow behavior instead, you need to
tell explicitly.