From: | zha-vova(at)yandex(dot)ru |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #14114: FK on-delete-cascade trouble |
Date: | 2016-04-26 12:44:45 |
Message-ID: | 20160426124445.2722.57235@wrigleys.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 14114
Logged by: Vladimir Zhavoronkov
Email address: zha-vova(at)yandex(dot)ru
PostgreSQL version: 9.4.5
Operating system: Red Hat 4.8.5-4 64-bit
Description:
After creating a FK constraint with "on delete cascade" option I got a
problem with before-delete-trigger on a dependent table. This short listing
below will make it clear:
test_db=# create table t (id integer primary key);
CREATE TABLE
test_db=# create table tt (id integer primary key, t_id integer references
t(id) on delete cascade);
CREATE TABLE
--here we have table "t" that is referenced from table "tt" with
on-delete-cascade foreign key
--this before-trigger will prevent deleting rows from table "tt"
test_db=# create or replace
test_db-# function d_tt()
test_db-# returns trigger as $$
test_db$# begin
test_db$# return null;
test_db$# end;
test_db$# $$ language plpgsql;
CREATE FUNCTION
test_db=# create trigger d_tt before delete on tt for each row execute
procedure d_tt();
CREATE TRIGGER
--insert some data in tables:
test_db=# insert into t(id) values(1);
INSERT 0 1
test_db=# insert into tt(id, t_id) values(1,1);
INSERT 0 1
--so we have a record in table "tt" that references table "t"; then we try
to delete referenced record from table "t":
test_db=# delete from t where id = 1;
DELETE 1
Now we deleted record with id=1 from table "t", but still got records in
table "tt" with t_id=1. This contradicts with our foreign key constraint:
test_db=# select * from t;
id
----
(0 rows)
test_db=# select * from tt;
id | t_id
----+------
1 | 1
(1 row)
From | Date | Subject | |
---|---|---|---|
Next Message | John Lumby | 2016-04-26 13:37:38 | Re: BUG #14109: pg_rewind fails to update target control file in one scenario |
Previous Message | David G. Johnston | 2016-04-26 07:16:19 | Re: BUG #14111: After minor upgrade (9.2.6 -> 9.2.16): ERROR: failed to build any 2-way joins |