Re: BUG or strange behaviour of update on primary key

2011/10/18 Robert Haas <robertmhaas@gmail.com>

On Mon, Oct 17, 2011 at 7:30 PM, desmodemone <desmodemone@gmail.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@gmail.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 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