Re: Another unexpected behaviour

Thanks for the reply.

You are right, the result is all or nothing, so it's still atomic. I
found my mistake and posted a clarification for my question.

I know in PostgreSQL 9.0 unique constraint can be set to deferrable.
However still no luck for unique indexes.

The real question is that why PostgreSQL behaves differently than
other major DBMS. IMHO, doing checking at set operation boundary is
more appropriate than at row boundary.

I got a sense that PostgreSQL was try to things in the right way. I
wonder if there is a good reason to the design. I have found several
posts discussing this topic, but none of them talked about why it's
designed to work that way.

On Jul 19, 11:41 pm, to(dot)(dot)(dot)(at)tuxteam(dot)de wrote:
> -----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-----
>
> --
> Sent via pgsql-general mailing list (pgsql-gene(dot)(dot)(dot)(at)postgresql(dot)org)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general