Re: foreign key locks, 2nd attempt

>
>
> Insert, Update and Delete don't take locks they simply mark the tuples
> they change with an xid. Anybody else wanting to "wait on the lock"
> just waits on the xid. We do insert a lock row for each xid, but not
> one per row changed.
>
I mean the foreign key checks here. They take a Select for Share Lock
right. That's what we are trying to optimize here. Or am i missing
something? So by following the suggested methodology, the foreign key
checks won't take any locks.

> It's worked that way for 5 years, so its too late to modify it now and
> this patch won't change that.
>
> The way we do RI locking is designed to prevent holding that in memory
> and then having the lock table overflow, which then either requires us
> to revert to the current design or upgrade to table level locks to
> save space in the lock table - which is a total disaster, if you've
> ever worked with DB2.
>
> What you're suggesting is that we store the locks in memory only as a
> way of avoiding updating the row.
>
> But that memory would be consumed, only when someone updates the
referenced column( which will usually be the primary key of the referenced
table). Any normal database programmer knows that updating primary key is
not good for performance. So we go by the same logic.

> No, updates of referenced columns are exactly the same as now when no
> RI checks happening.
>
> If the update occurs when an RI check takes place there is more work
> to do, but previously it would have just blocked and done nothing. So
> that path is relatively heavyweight but much better than nothing.
>
> As i have already said, that path is definitely heavy weight( like how
Robert has made the DDL path heavy weight). If we assume that DDLs are
going to be a rare phenomenon, then we can also assume that update of
primary keys is a rare phenomenon in a normal database.

>
> The most useful way to help with this patch right now is to run
> performance investigations and see if there are non-optimal cases. We
> can then see how the patch handles those. Theory is good, but it needs
> to drive experimentation, as I myself re-discover continually.
>
> I understand. I just wanted to know, whether the developer considered that
line of thought.

Thanks,
Gokul.