From: | Max Khon <fjoe(at)iclub(dot)nsu(dot)ru> |
---|---|
To: | Jan Wieck <JanWieck(at)Yahoo(dot)com> |
Cc: | PostgreSQL HACKERS <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: RI oddness |
Date: | 2001-04-24 12:34:08 |
Message-ID: | Pine.BSF.4.21.0104241933380.60754-100000@iclub.nsu.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
hi, there!
On Mon, 23 Apr 2001, Jan Wieck wrote:
> I just got trapped by one of my own features in the
> referential integrity area.
>
> The problem is, that the trigger run on the FK row at UPDATE
> allways checks and locks the referenced PK, even if the FK
> attributes didn't change. That's because if there'd be an ON
> DELETE SET DEFAULTS and someone deletes a PK consisting of
> all the FK's column defaults, we wouldn't notice and let it
> pass through.
>
> The bad thing on it is now, if I have one XACT that locks the
> PK row first, then locks the FK row, and I have another XACT
> that just want's to update another field in the FK row, that
> second XACT must lock the PK row in the first place or this
> entire thing leads to deadlocks. If one table has alot of FK
> constraints, this causes not really wanted lock contention.
>
> The clean way to get out of it would be to skip non-FK-change
> events in the UPDATE trigger and do alot of extra work in the
> SET DEFAULTS trigger. Actually it'd be to check if we're
> actually deleting the FK defaults values from the PK table,
> and if so we'd have to check if references exist by doing
> another NO ACTION kinda test.
>
> Any other smart idea?
read-write locks?
/fjoe
From | Date | Subject | |
---|---|---|---|
Next Message | Mauricio Breternitz | 2001-04-24 13:41:05 | Re: concurrent Postgres on NUMA - howto ? |
Previous Message | The Hermit Hacker | 2001-04-24 12:06:29 | OUTER JOIN vs UNION ... faster? |