Re: Queries never returning... - Mailing list pgsql-general

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