> 2011/10/18 Robert Haas <robertmh...@gmail.com> > >> On Mon, Oct 17, 2011 at 7:30 PM, desmodemone <desmodem...@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 <desmodem...@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<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
