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!
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 tableSo 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.phpIn 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
