Cannot delete newly inserted record while inside a transaction

Started by Steve Woodcockover 25 years ago2 messagesbugs
Jump to latest
#1Steve Woodcock
swoodcock@scholastic.co.uk

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

Your name : Steve Woodcock
Your email address : swoodcock@scholastic.co.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@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:
---------------------------------------------------------------------

#2Peter Eisentraut
peter_e@gmx.net
In reply to: Steve Woodcock (#1)
Re: Cannot delete newly inserted record while inside a transaction

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@gmx.net 75262 Uppsala
http://yi.org/peter-e/ Sweden