Обсуждение: 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