Problem with SET CONSTRAINTS some_constraint_fkey DEFERRED

From: Sergey Levchenko <sector119(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Problem with SET CONSTRAINTS some_constraint_fkey DEFERRED
Date: 2009-12-09 18:04:59
Message-ID: e3ffbf240912091004m77a50944n123ed3fbc62d84f1@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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
...

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2009-12-09 18:10:07 Re: Problem with SET CONSTRAINTS some_constraint_fkey DEFERRED
Previous Message Guillaume Lelarge 2009-12-09 16:08:50 Re: Rules, triggers and such