Cannot delete newly inserted record while inside a transaction

From: "Woodcock, Steve" <SWoodcock(at)scholastic(dot)co(dot)uk>
To: "'pgsql-bugs(at)postgresql(dot)org'" <pgsql-bugs(at)postgresql(dot)org>
Subject: Cannot delete newly inserted record while inside a transaction
Date: 2000-08-16 14:04:58
Message-ID: 33844739B96DD211813600A0C9EA678E53DAA1@soemail1.southam.sch
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

============================================================================
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:
---------------------------------------------------------------------

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message baley OUATTARA 2000-08-16 16:09:51
Previous Message Yury Don 2000-08-16 06:49:51 serial field dump bug