Thread

1. Cannot delete newly inserted record while inside a transaction

   Steve Woodcock <swoodcock@scholastic.co.uk> — 2000-08-16T14:04:58Z

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

2. Re: Cannot delete newly inserted record while inside a transaction

   Peter Eisentraut <peter_e@gmx.net> — 2000-08-20T00:40:56Z

   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