Tom Lane wrote:
The differential would have to be index updates or triggers fired by the UPDATE. I'd bet on
the latter, but since you've told us zip about your schema or what PG
version this is, it's impossible to speculate further...
This is my development machine. I'm running PostgreSQL 8.0.3 on a
Pentium 4 3GHZ Gentoo machine with a 2.6.12 kernel, 1 gig of RAM.
Everything is running on one big partition on a SATA drive.
You're right, it looks to be trigger related. I did have a timestamp
trigger, which I have removed, however it still has a bunch of foreign
key triggers on it. Even if I run:
update tbl_claim SET ins_lname = NULL;
I get the same problem.
In looking at the "\d tbl_claim" output, there is something odd I
notice. I have many foreign keys (the claim_id in tbl_claim is
referenced by 12 or so other tables, and tbl_claim references about 6 or
so tables by their _id) What is strange is that two of my newer foreign
keys are shown as follows:
Foreign-key constraints:
"fk_tbl_claim_tbl_stormgroup_stormgroup_id" FOREIGN KEY
(stormgroup_id) REFERENCES tbl_stormgroup(stormgroup_id) MATCH FULL
"fk_tbl_claim_emp_id" FOREIGN KEY (emp_id) REFERENCES
tbl_employee(emp_id) MATCH FULL
Which matches the syntax I used to create them, however all of my older
foreign keys are under the Triggers section and are defined as follows:
"RI_ConstraintTrigger_23354821" AFTER INSERT OR UPDATE ON tbl_claim
FROM tbl_agents NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE
PROCEDURE "RI_FKey_check_ins"('fk_tbl_claim_tbl_agents_fk', 'tbl_claim',
'tbl_agents', 'UNSPECIFIED', 'agent_id', 'agent_id')
Why are they different? Should all of my foreign keys look like the
first two, or are they logically identical? I assume the difference is
because the older keys were initially created in a 7.x version of
Postgres, and got into 8.x from a pg_dumpall
Below is a full \d dump of this table. Obviously, there are a ton of
triggers on it, but how would I avoid this in a heavily referenced
table? (tbl_claim is the core table of this entire system).
Certainly, dropping all of the triggers, indexes, etc. would solve the
problem and allow me to update, but I'd like a more elegant solution. I
don't have THAT many records in this table, and I wouldn't expect a
simple update of a column to hang everything. Should I modify my
foreign key triggers?
-----------------------------------------------------
Table "public.tbl_claim"
Column | Type |
Modifiers
------------------+-----------------------------+----------------------------------------------------------
claim_id | integer | not null default
nextval('tbl_claim_claim_id_key'::text)
worlfilenum | character varying(12) |
createby | integer |
claimnum | character varying(50) |
insured_id | integer |
comaster_id | integer |
clntmaster_id | integer |
agent_id | integer |
storm_id | integer |
claim_createdate | timestamp with time zone |
claim_lossdate | timestamp with time zone |
claim_mailer | timestamp with time zone |
claim_contdate | timestamp with time zone |
claim_inpecdate | timestamp with time zone |
claim_closedate | timestamp with time zone |
claim_clntnum | character varying(25) |
claim_deductible | double precision |
clmtype_id | integer |
subrogation | character varying(10) |
peril_id | integer |
rcv | double precision |
policydate | timestamp with time zone |
limita | double precision |
limitb | double precision |
limitc | double precision |
limitd | double precision |
deductible | double precision |
riskadd | character varying(100) |
riskcity | character varying(50) |
riskstate | character varying(50) |
riskzip | character varying(50) |
secinjury | character varying(10) |
searchtext | character varying(32) |
lossreserves | double precision |
expensereserves | double precision |
notes | character varying(512) |
active | integer | default 1
policyexpiredate | timestamp with time zone |
deductible2 | double precision |
salvage | integer |
siu | integer |
policynum | character varying(32) |
groupnumber | integer |
stormgroup_id | integer |
printed | integer |
severitycode | character varying(32) |
otherreserves | double precision |
personalreserves | double precision |
stamp | timestamp without time zone |
emp_id | integer |
ins_lname | character varying(50) |
ins_fname | character varying(100) |
ins_mi | character varying(50) |
ins_add1 | character varying(50) |
ins_add2 | character varying(50) |
ins_city | character varying(50) |
ins_state | character varying(50) |
ins_zip | character varying(50) |
ins_phone | character varying(50) |
ins_altphone | character varying(50) |
ins_cell | character varying(50) |
ins_pager | character varying(50) |
ins_fax | character varying(50) |
ins_email | character varying(256) |
Indexes:
"tbl_claim_pkey" PRIMARY KEY, btree (claim_id)
"idx_claim_claimnum" btree (claimnum)
"idx_tbl_claim_comaster_id" btree (comaster_id)
"idx_tbl_claim_createby" btree (createby)
"idx_tbl_claim_insured_id" btree (insured_id)
"idx_tbl_claim_storm_id" btree (storm_id)
"tbl_claim_agent_id" btree (agent_id)
Foreign-key constraints:
"fk_tbl_claim_tbl_stormgroup_stormgroup_id" FOREIGN KEY
(stormgroup_id) REFERENCES tbl_stormgroup(stormgroup_id) MATCH FULL
"fk_tbl_claim_emp_id" FOREIGN KEY (emp_id) REFERENCES
tbl_employee(emp_id) MATCH FULL
Triggers:
"RI_ConstraintTrigger_23354821" AFTER INSERT OR UPDATE ON tbl_claim
FROM tbl_agents NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE
PROCEDURE "RI_FKey_check_ins"('fk_tbl_claim_tbl_agents_fk', 'tbl_claim',
'tbl_agents', 'UNSPECIFIED', 'agent_id', 'agent_id')
"RI_ConstraintTrigger_23354824" AFTER INSERT OR UPDATE ON tbl_claim
FROM tbl_claimtype NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW
EXECUTE PROCEDURE "RI_FKey_check_ins"('fk_tbl_claim_tbl_claimtype_fk',
'tbl_claim', 'tbl_claimtype', 'UNSPECIFIED', 'clmtype_id', 'clmtype_id')
"RI_ConstraintTrigger_23354827" AFTER INSERT OR UPDATE ON tbl_claim
FROM tbl_clntmaster NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW
EXECUTE PROCEDURE "RI_FKey_check_ins"('fk_tbl_claim_tbl_clntmaster_fk',
'tbl_claim', 'tbl_clntmaster', 'UNSPECIFIED', 'clntmaster_id',
'clntmaster_id')
"RI_ConstraintTrigger_23354830" AFTER INSERT OR UPDATE ON tbl_claim
FROM tbl_insured NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE
PROCEDURE "RI_FKey_check_ins"('fk_tbl_claim_tbl_insured_fk',
'tbl_claim', 'tbl_insured', 'UNSPECIFIED', 'insured_id', 'insured_id')
"RI_ConstraintTrigger_23354833" AFTER INSERT OR UPDATE ON tbl_claim
FROM tbl_peril NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE
PROCEDURE "RI_FKey_check_ins"('fk_tbl_claim_tbl_peril_fk', 'tbl_claim',
'tbl_peril', 'UNSPECIFIED', 'peril_id', 'peril_id')
"RI_ConstraintTrigger_23354836" AFTER INSERT OR UPDATE ON tbl_claim
FROM tbl_foocomstr NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW
EXECUTE PROCEDURE "RI_FKey_check_ins"('fk_tbl_claim_tbl_foocomst_fk',
'tbl_claim', 'tbl_foocomstr', 'UNSPECIFIED', 'comaster_id', 'comaster_id')
"RI_ConstraintTrigger_23354846" AFTER DELETE ON tbl_claim FROM
tbl_claimactivity NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW
EXECUTE PROCEDURE
"RI_FKey_noaction_del"('fk_tbl_claimactivity_tbl_cla_fk',
'tbl_claimactivity', 'tbl_claim', 'UNSPECIFIED', 'claim_id', 'claim_id')
"RI_ConstraintTrigger_23354847" AFTER UPDATE ON tbl_claim FROM
tbl_claimactivity NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW
EXECUTE PROCEDURE
"RI_FKey_noaction_upd"('fk_tbl_claimactivity_tbl_cla_fk',
'tbl_claimactivity', 'tbl_claim', 'UNSPECIFIED', 'claim_id', 'claim_id')
"RI_ConstraintTrigger_23354858" AFTER DELETE ON tbl_claim FROM
tbl_claimchecklog NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW
EXECUTE PROCEDURE "RI_FKey_noaction_del"('fk_tbl_claimtpa_tbl_claim_fk',
'tbl_claimchecklog', 'tbl_claim', 'UNSPECIFIED', 'claim_id', 'claim_id')
"RI_ConstraintTrigger_23354859" AFTER UPDATE ON tbl_claim FROM
tbl_claimchecklog NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW
EXECUTE PROCEDURE "RI_FKey_noaction_upd"('fk_tbl_claimtpa_tbl_claim_fk',
'tbl_claimchecklog', 'tbl_claim', 'UNSPECIFIED', 'claim_id', 'claim_id')
"RI_ConstraintTrigger_23354861" AFTER DELETE ON tbl_claim FROM
tbl_claimclaimant NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW
EXECUTE PROCEDURE
"RI_FKey_noaction_del"('fk_tbl_claimclaimant_tbl_cla_fk',
'tbl_claimclaimant', 'tbl_claim', 'UNSPECIFIED', 'claim_id', 'claim_id')
"RI_ConstraintTrigger_23354862" AFTER UPDATE ON tbl_claim FROM
tbl_claimclaimant NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW
EXECUTE PROCEDURE
"RI_FKey_noaction_upd"('fk_tbl_claimclaimant_tbl_cla_fk',
'tbl_claimclaimant', 'tbl_claim', 'UNSPECIFIED', 'claim_id', 'claim_id')
"RI_ConstraintTrigger_23354873" AFTER DELETE ON tbl_claim FROM
tbl_claimdocument NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW
EXECUTE PROCEDURE
"RI_FKey_noaction_del"('fk_tbl_claimdocument_tbl_cla_fk',
'tbl_claimdocument', 'tbl_claim', 'UNSPECIFIED', 'claim_id', 'claim_id')
"RI_ConstraintTrigger_23354874" AFTER UPDATE ON tbl_claim FROM
tbl_claimdocument NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW
EXECUTE PROCEDURE
"RI_FKey_noaction_upd"('fk_tbl_claimdocument_tbl_cla_fk',
'tbl_claimdocument', 'tbl_claim', 'UNSPECIFIED', 'claim_id', 'claim_id')
"RI_ConstraintTrigger_23354876" AFTER DELETE ON tbl_claim FROM
tbl_claimwitness NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE
PROCEDURE "RI_FKey_noaction_del"('fk_tbl_claimwitness_tbl_clai_fk',
'tbl_claimwitness', 'tbl_claim', 'UNSPECIFIED', 'claim_id', 'claim_id')
"RI_ConstraintTrigger_23354877" AFTER UPDATE ON tbl_claim FROM
tbl_claimwitness NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE
PROCEDURE "RI_FKey_noaction_upd"('fk_tbl_claimwitness_tbl_clai_fk',
'tbl_claimwitness', 'tbl_claim', 'UNSPECIFIED', 'claim_id', 'claim_id')
"RI_ConstraintTrigger_23354948" AFTER DELETE ON tbl_claim FROM
tbl_invoice NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE
PROCEDURE "RI_FKey_noaction_del"('fk_tbl_invoice_tbl_claim_fk',
'tbl_invoice', 'tbl_claim', 'UNSPECIFIED', 'claim_id', 'claim_id')
"RI_ConstraintTrigger_23354949" AFTER UPDATE ON tbl_claim FROM
tbl_invoice NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE
PROCEDURE "RI_FKey_noaction_upd"('fk_tbl_invoice_tbl_claim_fk',
'tbl_invoice', 'tbl_claim', 'UNSPECIFIED', 'claim_id', 'claim_id')
