Thread: Another unexpected behaviour

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.

I tried the same test statement in both SQL Server and Oracle
I got correct/expected results as below:

select * from t1;

f1
---
2
3
4

I thought the Atomic should be at statement level, could anyone tell me why
PostgreSQL behaves differently?

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Another-unexpected-behaviour-tp4610242p4610242.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Shianmiin wrote:
>
> I thought the Atomic should be at statement level, could anyone tell me
> why PostgreSQL behaves differently?
>

re-state for clarification purpose.

Since SQL is a set language, there is no concept of row order. I thought the
checking should be on a per set operation (i.e. per statement), not per
micro operation basis (e.g. in this case, the order of operations could
result in success/failure). I don't know how strict the standard defines
here and it could be implementation specific. Still wondering why PostgreSQL
does it differently from other major DBMS?

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Another-unexpected-behaviour-tp4610242p4612763.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

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

On Tue, Jul 19, 2011 at 10:41 PM,  <tomas@tuxteam.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

If you are trying to make this work, you can add an order by to the
update and that allows you to avoid this problem.

 Best Wishes,
Chris Travers

Thanks. But I am not looking for how to avoid the problem at this
moment,
I am more interested in why PostgreSQL is designed to work this way.

To make the problem more obvious,
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;
update t1 set f1 = f1 + 1
==> ERROR:  duplicate key value violates unique constraint "uix_t1"
DETAIL:  Key (f1)=(2) already exists.

delete from t1;
insert into t1(f1) values (3), (2), (1);
select * from t1;
==> Query returned successfully: 3 rows affected, 16 ms execution
time.
update t1 set f1 = f1 + 1

i.e. the same "update t1 set f1 = f1 + 1" on a table with 3 rows (1,
2, 3) will results in different results depend on how the order of the
data got inserted !?
That doesn't feel like very SQL (declarative, set-oriented langauge)

On Jul 19, 11:44 pm, chris.trav...@gmail.com (Chris Travers) wrote:
> On Tue, Jul 19, 2011 at 10:41 PM,  <to...@tuxteam.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
>
> If you are trying to make this work, you can add an order by to the
> update and that allows you to avoid this problem.
>
>  Best Wishes,
> Chris Travers
>
> --
> Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general

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...@tuxteam.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...@postgresql.org)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general

Samuel Hwang <samuel@replicon.com> writes:
> Thanks. But I am not looking for how to avoid the problem at this
> moment,
> I am more interested in why PostgreSQL is designed to work this way.

Performance --- it's significantly more expensive to do a deferred
uniqueness check, since you have to visit the index a second time.
Also backwards compatibility with older versions of PG, which did
not have support for deferred uniqueness checks at all.

            regards, tom lane

On Wed, Jul 20, 2011 at 4:18 PM, Samuel Hwang <samuel@replicon.com> wrote:

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

The real question is why anyone would actually perform that kind of
UPDATE. It doesn't really make much sense to increment a PK value.

PostgreSQL is good at supporting things people want and need, so
differences do exist in places that are fairly low priority.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

It seems to me that it is inherently wrong to perform any operation on a
database that depends on the order in which records are retrieved,
without specifying that order in an ORDER BY clause.  The "update t1 set
f1 = f1 + 1" assumes that the operation will be performed in an order
that guarantees that the highest unchanged record will be the next
record processed.  I don't believe that any database system should be
required to support an action like this.

RobR

I disagree. I think it depends upon all records being modified before any are constraint-checked, which may or may not be a reasonable requirement. If you think of it as a true set operation, it seems like a perfectly reasonable thing to do ("increment the value of column N in each of the records of this set"). It seems odd that this should work:

Simon Riggs wrote:
>
> The real question is why anyone would actually perform that kind of
> UPDATE. It doesn't really make much sense to increment a PK value.
>
> PostgreSQL is good at supporting things people want and need, so
> differences do exist in places that are fairly low priority.
>

I agree it makes less sense to modify PK that way and that's not what we
were doing.

The case we went through is that we have a unique index on a table that
contains a date field. While we rolled the dates forward it happens to
"collide" with the existing data in the transient state and failed the
update. I don't think this is that weird.

There are different ways to get around the way PostgreSQL behaves, just a
little surprise about that since that doesn't seem right from a purist's
point of view.

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Another-unexpected-behaviour-tp4610242p4616541.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Thanks. That's the best reason I can think of too.

"Less expensive" feels like taking shortcuts to speed up things and not a
good reason to make decision on.
Although maintain backward compatibility makes sense (to some extend), if
old PG doesn't behave properly, it should be fixed, shouldn't it?



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Another-unexpected-behaviour-tp4610242p4616513.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.