update on unique constraint

From: Theo Kramer <theo(at)flame(dot)co(dot)za>
To: pgsql-bugs(at)postgresql(dot)org
Subject: update on unique constraint
Date: 2000-11-22 17:07:49
Message-ID: 200011221707.TAA27495@flame.flame.co.za
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

postgresql 7.0.3 on RedHat 7.0
------------------------------

create table whois (domain text, version integer);
CREATE
theo=# create unique index whois_idx on whois (domain,version);
CREATE
theo=# insert into whois values ('mydomain.co.za', 0);
INSERT 18829 1
theo=# insert into whois values ('mydomain.co.za', 1);
INSERT 18830 1
theo=# insert into whois values ('mydomain.co.za', 2);
INSERT 18831 1
theo=# update whois set version = version + 1 where domain = 'mydomain.co.za';
ERROR: Cannot insert a duplicate key into unique index whois_idx

Oracle 8.1.6 on RedHat 6.2
--------------------------
SQL> create table whois (domain varchar2(64), version integer);

Table created.

SQL> create unique index whois_idx on whois (domain,version);

Index created.

SQL> insert into whois values ('mydomain.co.za', 0);
1 row created.

SQL> insert into whois values ('mydomain.co.za', 1);

1 row created.

SQL> insert into whois values ('mydomain.co.za', 2);

1 row created.

SQL> update whois set version = version + 1 where domain = 'mydomain.co.za';

3 rows updated.

SQL> select * from whois;

DOMAIN VERSION
---------------------------------------- ----------
mydomain.co.za 1
mydomain.co.za 2
mydomain.co.za 3

SQL>

Ideas/thoughts?

I think oracle is correct - no contraints are violated and the update
should be atomic.

Regards
Theo

Browse pgsql-bugs by date

  From Date Subject
Next Message Matt Benjamin 2000-11-23 01:09:07 STDC_HEADERS on Solaris 8
Previous Message aixit 2000-11-22 16:10:59 error weil dumping