Thread: UPDATE bug in PostgreSQL-7.1.3

Hi!

I have found that some of my queries stop working after upgrade from 7.0 to
7.1.3.
Some investigation show that UPDATE queries grow to crash.

Here is my SQL code that produces error (it is supposed to be in pg_bug.sql
file):

----------
\connect template1 postgres
CREATE DATABASE "pg_bug" WITH TEMPLATE = template0;
\connect pg_bug postgres

CREATE TABLE "buggy" ( "field1" integer UNIQUE );
GRANT ALL on "buggy" to "postgres";

COPY "buggy" FROM stdin;
1
2
3
\.

UPDATE buggy SET field1 = field1+1;
----------

And here is the execution result:

bash$ /usr/local/pgsql/bin/psql -d template1 -f ~/tmp/pg_bug.sql
You are now connected to database template1 as user postgres.
CREATE DATABASE
You are now connected to database pg_bug as user postgres.
psql:/home/barancev/tmp/pg_bug.sql:7: NOTICE:  CREATE TABLE/UNIQUE will
create implicit index 'buggy_field1_key' for tab
le 'buggy'
CREATE
CHANGE
psql:/home/barancev/tmp/pg_bug.sql:17: ERROR:  Cannot insert a duplicate key
into unique index buggy_field1_key
bash$

So you can see UPDATE failed.

Best regards,
Alexei

--
Alexei Barantsev, ISP RAS
E-mail: barancev@kazbek.ispras.ru
ICQ   : 3959207

"Alexei Barantsev" <barancev@kazbek.ispras.ru> writes:
> UPDATE buggy SET field1 = field1+1;
> psql:/home/barancev/tmp/pg_bug.sql:17: ERROR:  Cannot insert a duplicate key
> into unique index buggy_field1_key

Unfortunately, that's never worked, as our unique constraints are tested
immediately not at end of statement.  7.0 is certainly no better about
it than 7.1...

            regards, tom lane