Re: BUG or strange behaviour of update on primary key - Mailing list pgsql-hackers

From desmodemone
Subject Re: BUG or strange behaviour of update on primary key
Date
Msg-id CAEs9oFmwQKaxOo=yS0zT9uh23Xp3GeRmvvcJjhZg3-k-NMf6vQ@mail.gmail.com
Whole thread Raw
In response to Re: BUG or strange behaviour of update on primary key  (desmodemone <desmodemone@gmail.com>)
List pgsql-hackers




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
 


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/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

pgsql-hackers by date:

Previous
From: Simon Riggs
Date:
Subject: Re: HeapTupleHeaderAdvanceLatestRemovedXid doing the wrong thing with multixacts
Next
From: Thom Brown
Date:
Subject: Silent failure with invalid hba_file setting