Re: Cannot delete newly inserted record while inside a transaction

============================================================================
POSTGRESQL BUG REPORT TEMPLATE
============================================================================

Your name : Steve Woodcock
Your email address : swoodcock(at)scholastic(dot)co(dot)uk

System Configuration
---------------------
Architecture (example: Intel Pentium) : PIII

Operating System (example: Linux 2.0.26 ELF) : Linux
soitsrv03.southam.sch 2.2.16-3smp #1 SMP Mon Jun 19 19:00:35 EDT 2000 i686
unknown

PostgreSQL version (example: PostgreSQL-7.0): PostgreSQL-7.0.2

Compiler used (example: gcc 2.8.0) :

Please enter a FULL description of your problem:
------------------------------------------------

Trying to delete a record inserted in the same transaction fails
if the table is referenced from another table (even if the referencing
table is empty).

Please describe a way to repeat the problem. Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------

Load the following model into an empty database:

create table customer (
customer_id integer primary key,
name varchar(30)
);

create table orders (
order_id integer primary key,
customer_id integer references customer(customer_id)
);

In psql:

[steve(at)soitsrv03 pg-bug]$ psql
Welcome to psql, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit

steve=# \d
List of relations
Name | Type | Owner
----------+-------+-------
customer | table | steve
orders | table | steve
(2 rows)

steve=# select count(*) from customer;
count
-------
0
(1 row)

steve=# select count(*) from orders;
count
-------
0
(1 row)

steve=# begin;
BEGIN
steve=# insert into customer values(1, 'A customer');
INSERT 14783936 1
steve=# delete from customer where customer_id = 1;
ERROR: triggered data change violation on relation "customer"
steve=# rollback;
ROLLBACK

If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------

Woodcock, Steve writes:

> Trying to delete a record inserted in the same transaction fails
> if the table is referenced from another table (even if the referencing
> table is empty).

This is correct behaviour, specified in SQL. It's potentially overly
restrictive, but we're not to decide that.

--
Peter Eisentraut Sernanders väg 10:115
peter_e(at)gmx(dot)net 75262 Uppsala
http://yi.org/peter-e/ Sweden