Re: BUG or strange behaviour of update on primary key

> 2011/10/18 Robert Haas <robertmhaas(at)gmail(dot)com>
>
>> On Mon, Oct 17, 2011 at 7:30 PM, desmodemone <desmodemone(at)gmail(dot)com>
>> wrote:
>> > Seems an Oracle bug not Postgresql one!
>>
>> I don't think it's a bug for it to work. It'd probably work in
>> PostgreSQL too, if you inserted (2) first and then (1). It's just
>> that, as Tom says, if you want it to be certain to work (rather than
>> depending on the order in which the rows are inserted), you need the
>> checks to be deferred.
>>
>> --
>> Robert Haas
>> EnterpriseDB: http://www.enterprisedb.com
>> The Enterprise PostgreSQL Company
>>
>

> 2011/10/18 desmodemone <desmodemone(at)gmail(dot)com>
> Hi there,
> I could workaround the behavior with deferred constraint, and
> it's ok, but as I show, I have different behavior for constraint with the
> same definition in two rdbms and Postgresql depends on the physical order of
> row (with the same definition of constraint NOT DEFERRABLE INITIALLY
> IMMEDIATE) , or better Postgresql seems to check for every row, even if
> the command is one (I am doing one update on all of rows) , right? .
>
> Moreover , in documentation the definition says that a not deferrable
> constraints will check after "every command" , not after every row of the
> command:
>
> http://www.postgresql.org/docs/9.1/static/sql-createtable.html
>
> DEFERRABLE
> NOT DEFERRABLE
>
> This controls whether the constraint can be deferred.* A constraint that
> is not deferrable will be checked immediately after every command*.
> Checking of constraints that are deferrable can be postponed until the end
> of the transaction (using the SET CONSTRAINTS<http://www.postgresql.org/docs/9.0/static/sql-set-constraints.html>
> command). NOT DEFERRABLE is the default. Currently, only UNIQUE, PRIMARY
> KEY, EXCLUDE, and REFERENCES (foreign key) constraints accept this clause.
> NOT NULL and CHECK constraints are not deferrable.
> ---------------
>
> If this is "historical buggy behavior for performance" , I think we have to
> change the definition of NOT DEFERRABLE in documentation,
> because Postgresql is not checking at end of a dml, but for every row
> modified by the command or there is something needs a patch.
>
>
> Regards, Mat
>

Hello there,

I think I have find a limit of this workaround. Imagine I have two tables in
Oracle or other rdbms with a foreign key between them :

testup3 ( a int) primary key on a NOT DEFERRABLE INITIALLY IMMEDIATE ;

testup4 ( a int) foreign key on a references testup3(a) ;

For first table I could create this (to have a "normal" sql standard
behavior on update with multiple rows) :

testup3 ( a int) primary key on a DEFERRABLE INITIALLY IMMEDIATE ;

By the way I could not create a foreign key on a DEFERRABLE constraint ,
in fact I obtain an error like this :

ERROR: cannot use a deferrable unique constraint for referenced table

So if I have a normal ERD schema with FK , I could not use the workaround
of "DEFERRABLE" constraints .
I found an old discussion on this :
<http://archives.postgresql.org/pgsql-hackers/2010-06/msg00151.php>
http://archives.postgresql.org/pgsql-hackers/2010-06/msg00168.php

In my opinion it could be a big limitation for who want migrate
applications or is developing applications on different db.

Any suggest or idea ?

Regards, Mat