Cannot delete newly inserted record while inside a transaction
============================================================================
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:
---------------------------------------------------------------------
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