Re: Queries never returning...

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')