| From: | greg(at)antrez(dot)pl | 
|---|---|
| To: | pgsql-bugs(at)postgresql(dot)org | 
| Subject: | BUG #8227: referential integrity problem | 
| Date: | 2013-06-12 20:08:53 | 
| Message-ID: | E1UmrLh-0001y7-C0@wrigleys.postgresql.org | 
| Views: | Whole Thread | Raw Message | Download mbox | Resend email | 
| Thread: | |
| Lists: | pgsql-bugs | 
The following bug has been logged on the website:
Bug reference:      8227
Logged by:          Greg Brzezinski
Email address:      greg(at)antrez(dot)pl
PostgreSQL version: 9.2.4
Operating system:   linux
Description:        
postgres=# CREATE TABLE test( id int primary key, name text );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey"
for table "test"
CREATE TABLE
postgres=# CREATE TABLE test_item( id int primary key, test_id int
references test(id)  match full on update cascade on delete cascade, content
text );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"test_item_pkey" for table "test_item"
CREATE TABLE
postgres=# CREATE OR REPLACE FUNCTION test_item_delete_lock() returns
trigger AS $f$
postgres$# begin
postgres$#     return null;
postgres$# end;
postgres$# $f$ language plpgsql;
CREATE FUNCTION
postgres=# CREATE TRIGGER test_item_delete_lock BEFORE DELETE ON test_item
FOR EACH ROW EXECUTE PROCEDURE test_item_delete_lock();
CREATE TRIGGER
postgres=#
postgres=# insert into test values (1, 'one');
INSERT 0 1
postgres=# insert into test_item values (1, 1, 'for one');
INSERT 0 1
postgres=# delete from test where id = 1;
DELETE 1
postgres=# select * from test_item;
 id | test_id | content
----+---------+---------
  1 |       1 | for one
(1 row)
postgres=# select * from test;
 id | name
----+------
(0 rows)
Is it ok that we loose referential integrity by locking DELETE on table
test_item ?
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Tom Lane | 2013-06-12 21:36:34 | Re: BUG #8227: referential integrity problem | 
| Previous Message | hubert depesz lubaczewski | 2013-06-12 11:03:50 | Re: BUG #8226: Inconsistent unnesting of arrays |