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