Re: failed Delete after Insert in a transaction

Andrew Snow wrote:
>
> 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.

It is according to the SQL3 specs, that you cannot modify one
and the same row, that is or might be subject to referential
integrity or triggers, multiple times in one transaction. If
you touch anything else or not doesn't matter.

>
> I am running postgresql 7.02 on FreeBSD 3.4-STABLE.
>
>
> Thanks
>
>
> Andrew
>
>

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #