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

foreign keys and RI triggers

From: Neil Conway <neilc(at)samurai(dot)com>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: foreign keys and RI triggers
Date: 2005-05-26 07:56:30
Message-ID: 4295812E.7090601@samurai.com (view raw or flat)
Thread:
Lists: pgsql-hackers
I spent a little while looking into a performance issue with a large 
UPDATE on a table with foreign keys. A few questions:

(1) When a PK table is updated, we skip firing the per-row UPDATE RI 
triggers if none of the referenced columns in the PK table have been 
modified. However, AFAICS we do not apply a similar optimization for 
updates of foreign key tables: if a user does not modify the foreign key 
column, we needn't check for the presence of the FK column value in the 
primary key table. Is there a reason we don't implement this?

(2) For per-row RI triggers of all kinds, we save the trigger under 
CurTransactionContext and invoke it at the end of the current query. 
There is not even overflow to disk (the report that prompted me to look 
into this was someone's database crashing because they kept running OOM 
when doing an UPDATE of a large table with FKs on a pretty lowend 
machine). While avoiding consuming a lot of memory for queued trigger 
execution is worth doing anyway, ISTM we needn't queue RI triggers in 
the first place. Is there a reason we can't just invoke after-row RI 
triggers immediately?

(Hmm, I suppose we would need to defer firing the trigger until the 
command ID is incremented if the foreign key references its own table. 
But even so, this should not be an issue for non-self-referential 
foreign keys.)

(3) This is minor, but AFAICS RI_FKey_check_upd() is not used -- 
RI_FKey_check_ins() is used to validate both inserts and updates on 
tables with foreign keys (see tablecmds.c circa 4423). Both functions 
are just wrappers over RI_FKey_check() anyway. This is rather confusing; 
would anyone object if I removed both functions and made RI_FKey_check() 
public?

-Neil


Responses

pgsql-hackers by date

Next:From: ITAGAKI TakahiroDate: 2005-05-26 08:04:01
Subject: O_DIRECT for WAL writes
Previous:From: Qingqing ZhouDate: 2005-05-26 05:59:04
Subject: Can we simplify win32 threading code

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