Re: Another unexpected behaviour

Поиск
Список
Период
Сортировка
От tomas@tuxteam.de
Тема Re: Another unexpected behaviour
Дата
Msg-id 20110720054102.GA8826@tomas
обсуждение исходный текст
Ответ на Another unexpected behaviour  (Shianmiin <Shianmiin@gmail.com>)
Ответы Re: Another unexpected behaviour  (Chris Travers <chris.travers@gmail.com>)
Список pgsql-general
-----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-----

В списке pgsql-general по дате отправления:

Предыдущее
От: Greg Smith
Дата:
Сообщение: Re: Programmer ( Postgres), Milwaukee - offsite-Remote - onsite
Следующее
От: Chris Travers
Дата:
Сообщение: Re: Another unexpected behaviour