Problem with SET CONSTRAINTS some_constraint_fkey DEFERRED

Started by Sergey Levchenkoover 16 years ago2 messagesgeneral
Jump to latest
#1Sergey Levchenko
sector119@gmail.com

Why I get: update or delete on table "people" violates foreign key
constraint "transactions_person_id_fkey" on table "transactions"

But before that query I run: SET CONSTRAINTS
transactions_person_id_fkey DEFERRED; before delete from people table

Log:

2009-12-09 20:01:51 EET LOG: statement: BEGIN; SET TRANSACTION
ISOLATION LEVEL READ COMMITTED
2009-12-09 20:01:51 EET LOG: duration: 0.176 ms
2009-12-09 20:01:51 EET LOG: statement: select version()
2009-12-09 20:01:51 EET LOG: duration: 0.552 ms
2009-12-09 20:01:51 EET LOG: statement: select current_schema()
2009-12-09 20:01:51 EET LOG: duration: 0.391 ms
2009-12-09 20:01:51 EET LOG: statement: SELECT CAST('test unicode
returns' AS VARCHAR(60)) AS anon_1
2009-12-09 20:01:51 EET LOG: duration: 0.506 ms
2009-12-09 20:01:51 EET LOG: statement: SET search_path TO system,ternopil
2009-12-09 20:01:51 EET LOG: duration: 0.223 ms
2009-12-09 20:01:51 EET LOG: statement: SELECT imports.id AS
imports_id, imports.name AS imports_name, imports."table" AS
imports_table, imports.priority AS imports_priority, imports.source AS
imports_source, imports.encoding AS imports_encoding, imports.fields
AS imports_fields, imports.filter AS imports_filter, imports.disabled
AS imports_disabled
FROM imports
WHERE imports.source IN (E'people.csv') ORDER BY imports.priority
2009-12-09 20:01:51 EET LOG: duration: 1.993 ms
2009-12-09 20:01:51 EET LOG: statement: SELECT pg_constraint.conname
FROM pg_catalog.pg_constraint, pg_catalog.pg_class,
pg_catalog.pg_namespace WHERE pg_constraint.confrelid = pg_class.oid
AND pg_constraint.contype = E'f' AND pg_class.relname = E'people' AND
pg_namespace.oid = pg_class.relnamespace AND pg_namespace.nspname =
E'ternopil' AND pg_constraint.conrelid != pg_class.oid
2009-12-09 20:01:51 EET LOG: duration: 3.722 ms

IT'S HERE >>>>

2009-12-09 20:01:51 EET LOG: statement: SET CONSTRAINTS
meter_readings_person_id_fkey,transactions_person_id_fkey DEFERRED
2009-12-09 20:01:51 EET LOG: duration: 0.490 ms
2009-12-09 20:01:51 EET LOG: statement: DELETE FROM people
2009-12-09 20:01:51 EET LOG: duration: 743.187 ms
2009-12-09 20:01:51 EET LOG: statement: COPY
people(id,street_id,building,corpus,appartment,block,last_name,hd,residents,space,rooms,privilege,comment)
FROM stdin WITH DELIMITER AS E' '
2009-12-09 20:02:12 EET LOG: duration: 20142.596 ms
2009-12-09 20:02:12 EET LOG: statement: COMMIT
2009-12-09 20:02:12 EET ERROR: update or delete on table "people"
violates foreign key constraint "transactions_person_id_fkey" on table
"transactions"
2009-12-09 20:02:12 EET DETAIL: Key (id)=(306216) is still referenced
from table "transactions".
2009-12-09 20:02:12 EET STATEMENT: COMMIT

eps=# \d transactions
Table "ternopil.transactions"
Column | Type |
Modifiers
--------------------+------------------------+-----------------------------------------------------------
id | integer | not null default
nextval('transactions_id_seq'::regclass)
...
person_id | integer | not null
...

Foreign-key constraints:
...
"transactions_person_id_fkey" FOREIGN KEY (person_id) REFERENCES
people(id) DEFERRABLE
...

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Sergey Levchenko (#1)
Re: Problem with SET CONSTRAINTS some_constraint_fkey DEFERRED

Sergey Levchenko <sector119@gmail.com> writes:

Why I get: update or delete on table "people" violates foreign key
constraint "transactions_person_id_fkey" on table "transactions"

But before that query I run: SET CONSTRAINTS
transactions_person_id_fkey DEFERRED; before delete from people table

It looks to me like it's doing what it is supposed to, ie, applying
the check at COMMIT.

regards, tom lane