Re: locking of referenced table during constraint

On Wed, 2002-09-04 at 22:49, Tom Lane wrote:
> Scott Shattuck <ss(at)technicalpursuit(dot)com> writes:
> > ... I don't understand why an exclusive table-level lock is being
> > taken out to add a trigger.
>
> Well, that's a schema change; it makes sense to me to forbid access
> while we're changing a table's schema.
>

No. In my book a schema change would alter the data a query would see --
as in drop column, or add column, etc. This is simply a "don't let a
delete/update get past this trigger from this point forward". That's not
a bar-the-gates kind of scenario to me. More like "for any DML operating
after the current version stamp make sure this trigger runs." Why lock
anything?

One scenario I can see. A delete starting at T0 doesn't see a trigger.
The alter occurs at T1 but, due to ACID, the delete doesn't see it. The
delete tries to commit at T2. Unfortunately, in that scenario you can
envision an issue since it would seem the delete should fail since the
alter is done, but the delete's transaction shouldn't be able to be
affected by things starting after it does. So, a conflict. But only for
a delete or update. Selects already have transaction isolation
levels...why don't they allow the selects to read through adding a
constraint?

I have other serious issues with locking and FK constraints as it is.
They often cause us serious performance problems. Sadly, the longer I
use PG and get hammered by locking issues surrounding the FK constraint
implementation the less I find myself likely to suggest PG for similar
customers in the future.

> I think this discussion may just be a miscommunication: it's not clear
> to me whether you're complaining about adding a trigger or just firing
> a trigger. The former is not a time-critical task in my book ...
>

It becomes time critical when the table has 3 million user account
entries and the lock blocks people from having their login name
verified, causing what's supposed to be a 24x7 e-commerce site to
essentially go offline to users for 5 minutes or more just so you can
add a constraint to a new table with no rows. Sorry, but that sucks.

ss