Re: Another unexpected behaviour

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Mon, Jul 18, 2011 at 03:18:43PM -0700, Shianmiin wrote:
> setup:
> ====
> drop table if exists t1;
> create table t1 (f1 int);
> create unique index uix_t1 on t1(f1) ;
> insert into t1(f1) values (1), (2), (3);
> select * from t1;
>
> f1
> ---
> 1
> 2
> 3
>
> test statement:
> ============
> update t1 set f1 = f1 + 1;
>
> In PostgreSQL I got,
> ERROR: duplicate key value violates unique constraint "uix_t1"
> DETAIL: Key (f1)=(2) already exists.

If you look at the result, nothing changed. So it's still atomic.

The question is at which point in the transaction the constraint will be
checked (whether it's DEFERRED or IMMEDIATE in SQL talk).

PostgreSQL version < 9 can't do deferred constraint checking for unique
constraints, this is a limitation wrt SQL standard (see [1]). It seems
that it's possible for versions >= 9.0 (see [2]).

[1] <http://www.postgresql.org/docs/8.4/static/sql-set-constraints.html>
[2] <http://www.postgresql.org/docs/9.0/static/sql-set-constraints.html>

Hope that helps
- -- tomás
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD4DBQFOJmpuBcgs9XrR2kYRAntiAJ90hHBs2Vz9u6u1KJLyqY1k7Pz5KwCYnMuF
gIZPVyHk883zHCfCKjcZhw==
=9ENo
-----END PGP SIGNATURE-----