Re: Why do I need to set UPDATE permissions for fkey check?

On Fri, Jul 21, 2000 at 02:00:00PM -0700, Stephan Szabo wrote:
>
> It's a known problem in the foreign key code. The reason is that
> the fk triggers use SELECT FOR UPDATE to select the matching
> rows that it is checking and the reason for using FOR UPDATE is
> to lock those rows so that someone cannot delete/change them out
> from under your nose while you're looking at them. However,
> SELECT FOR UPDATE is asking for update permissions because it
> grabs that row lock.

Oh, okay, I understand your explanation, and it fits with what I am
seeing.

But...

...this is a READ ONLY table! Maybe it would be possible to have the fkey
triggers look to see if the table is read-only, and then simply use SELECT
instead of SELECT FOR UPDATE and then not perform the row locking? Since
this is a read-only table, there would be no risk of deleting/changing any
of the data. Yeah, I realize that with this solution, you cannot
guarantee that the table doesn't become 'writable' sometime during the
fkey lookup.

It would seem to me that this is a serious problem. I absolutely cannot
have my data table be writable, and I need to maintain fkey integrity.
Urg.... this is very bad, the fkey integrity check is the reason I
installed Pg v7. I would think that keeping read-only static data table
would be a common database occurance, any suggestions on how to get around
this issue? Possibly with a (gulp) permissions switching trigger (gulp)?

--

-**-*-*---*-*---*-*---*-----*-*-----*---*-*---*-----*-----*-*-----*---
Jon Lapham
Centro Nacional de Ressonancia Magnetica Nuclear de Macromoleculas
Universidade Federal do Rio de Janeiro (UFRJ) - Brasil
email: jlapham(at)gandalf(dot)bioqmed(dot)ufrj(dot)br
***-*--*----*-------*------------*--------------------*---------------