Re: Another unexpected behaviour

From: tomas@tuxteam.de
Subject: Re: Another unexpected behaviour
Date: ,
Msg-id: 20110720054102.GA8826@tomas
(view: Whole thread, Raw)
In response to: Another unexpected behaviour  (Shianmiin)
Responses: Re: Another unexpected behaviour  (Chris Travers)
List: pgsql-general

Tree view

Another unexpected behaviour  (Shianmiin, )
 Re: Another unexpected behaviour  (Shianmiin, )
 Re: Another unexpected behaviour  (, )
  Re: Another unexpected behaviour  (Chris Travers, )
 Re: Another unexpected behaviour  (Samuel Hwang, )
  Re: Another unexpected behaviour  (Tom Lane, )
   Re: Another unexpected behaviour  (Shianmiin, )
  Re: Another unexpected behaviour  ("Rob Richardson", )
   Re: Another unexpected behaviour  (Rick Genter, )
 Re: Another unexpected behaviour  (Samuel Hwang, )
  Re: Another unexpected behaviour  (Simon Riggs, )
   Re: Another unexpected behaviour  (Shianmiin, )

-----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 by date:

From: Ruben Blanco
Date:
Subject: Worse performance on partitioned table than in non partitioned table
From: "Sofer, Yuval"
Date:
Subject: compile postgres with visual studio 2010