On Wed, 2002-09-04 at 22:49, Tom Lane wrote: > Scott Shattuck <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
