Re: deletion of records before commit doesn't work

Hello all,

I just reproduced the same phenomenon on my installation (PostgreSQL
7.0.2 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66) and it seems
to me that maybe the index is not correctly actualized while inserting
the record? It seems that any (!) update on blah (before executing the
delete) will solve the problem:

...
insert into blah (subdiv_code) values ('VG');
delete from blah where subdiv_code='VG';
...

=> ERROR

...
insert into blah (subdiv_code) values ('VG');
update blah set subdiv_code='VG' where subdiv_code='VG';
delete from blah where subdiv_code='VG';
...

=> OK

...
insert into blah (subdiv_code) values ('VG');
update blah set subdiv_code=subdiv_code;
delete from blah where subdiv_code='VG';
...

=> OK

...
insert into blah (subdiv_code) values ('VG');
update blah set id=id;
delete from blah where subdiv_code='VG';
...

=> OK

Best regards, Jens

Ashley Clark schrieb:
>
> I've come up with this example and I want to know why it does what it
> does.
>
> -- snip --
> You are now connected to database template1.
> CREATE DATABASE
> You are now connected to database testing.
> psql:test2.sql:11: NOTICE: CREATE TABLE/UNIQUE will create implicit
> index 'subdivs_name_key' for table 'subdivs'
> psql:test2.sql:11: NOTICE: CREATE TABLE/PRIMARY KEY will create
> implicit index 'subdivs_pkey' for table 'subdivs'
> CREATE
> psql:test2.sql:20: NOTICE: CREATE TABLE will create implicit sequence
> 'blah_id_seq' for SERIAL column 'blah.id'
> psql:test2.sql:20: NOTICE: CREATE TABLE/PRIMARY KEY will create
> implicit index 'blah_pkey' for table 'blah'
> psql:test2.sql:20: NOTICE: CREATE TABLE will create implicit
> trigger(s) for FOREIGN KEY check(s)
> CREATE
> INSERT 218198 1
> BEGIN
> INSERT 218199 1
> psql:test2.sql:29: ERROR: triggered data change violation on relation
> "blah"
> ROLLBACK
> BEGIN
> INSERT 218200 1
> UPDATE 1
> DELETE 1
> ROLLBACK
> -- snip --
>
> and the test file is attached.
>
> --
> hackers ally
>
> ------------------------------------------------------------------------
>
> test2.sqlName: test2.sql
> Type: Plain Text (text/plain)
>
> Part 1.2Type: application/pgp-signature

=============================================
Jens Hartwig
---------------------------------------------
debis Systemhaus GEI mbH
10875 Berlin
Tel. : +49 (0)30 2554-3282
Fax : +49 (0)30 2554-3187
Mobil : +49 (0)170 167-2648
E-Mail : jhartwig(at)debis(dot)com
=============================================