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