Re: failed Delete after Insert in a transaction

> Why won't PostgreSQL let me do this?
>
> db=# begin;
> BEGIN
> db=# insert into foo (name) values ('hmmm');
> INSERT 22288 1
> db=# delete from foo where name='hmmm';
> ERROR: triggered data change violation on relation "foo"
> db=# abort;
>
> The table foo is defined like this:
>
> CREATE TABLE foo (
> ID serial PRIMARY KEY,
> Name text NOT NULL
> );
>
>
> I can't work out what I am doing wrong!
>
>
> Note, there is another table that REFERENCES this table, but as you can
see
> in the example transaction above, I don't touch any other tables. Also
note
> that it works fine outside of a transaction.
>
> I am running postgresql 7.02 on FreeBSD 3.4-STABLE.
>
>
> Thanks
>
>
> Andrew

You should commit transaction before changing any foreign key that was
affected by this transaction.

So if you really want to delete the row you have just inserted (I wonder why
:) you should write your own FOREIGN KEY triggers. It implies performance
loss, but allows you to do everything you want over the referenced fields.

Alex Bolenok.