Skip site navigation (1) Skip section navigation (2)

Re: RI oddness

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 (view raw or flat)
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


In response to

  • RI oddness at 2001-04-23 19:55:01 from Jan Wieck

Responses

pgsql-hackers by date

Next:From: Mauricio BreternitzDate: 2001-04-24 13:41:05
Subject: Re: concurrent Postgres on NUMA - howto ?
Previous:From: The Hermit HackerDate: 2001-04-24 12:06:29
Subject: OUTER JOIN vs UNION ... faster?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group