Re: constraint triggers - Mailing list pgsql-admin
From | Maria L. Wilson |
---|---|
Subject | Re: constraint triggers |
Date | |
Msg-id | 4E849BAD.1090405@nasa.gov Whole thread Raw |
In response to | Re: constraint triggers (Craig Ringer <ringerc@ringerc.id.au>) |
List | pgsql-admin |
thanks Craig - we are doing some testing with some of your ideas now.... Hopefully we can get this solved so using triggers doesn't cause so much contention. I also understand that foreign keys can also cause some of these issues. Do you have any thoughts on that? thanks again, Maria Wilson On 9/28/11 10:39 PM, Craig Ringer wrote: > On 09/28/2011 08:54 PM, Maria L. Wilson wrote: > >> UPDATE dataset >> SET gracount = gracount + 1 >> WHERE dataset.inv_id = NEW.inv_id; > That'll serialize all transactions that touch the same inv_id, so only > one may run at once. The second and subsequent ones will block waiting > for an update lock on the `dataset' tuple for `inv_id'. > > When you think about it that's necessary to prevent a wrong result when > transaction A then B run this statement, transaction B commits, and > transaction A rolls back. What's the correct answer? > > To fix this you'll need to change how you maintain your `dataset' table. > Exactly how depends on your requirements. > > You can trade read performance off against write performance by > INSERTing new rows instead of UPDATEing them, so you do a: > > SELECT count(inv_id) FROM dataset WHERE dataset.inv_id = ? > > instead of a > > SELECT gracount FROM dataset WHERE dataset.inv_id = ? > > to retrieve your count. > > You can have both fast reads and fast writes if you accept potentially > out-of-date `gracount' data, maintaining `gracount' as a materialized > view that you refresh using LISTEN/NOTIFY *after* a transaction commits. > It's possible for it to be a little out of date, but writers no longer > interfere with each other and readers no longer have to re-do the > counting/aggregation work. > > You can live with serializing writes like you currently do in exchange > for the greater read performance of maintaining counts. This may not be > so bad once you understand what's happening and can keep transactions > that trigger this problem short, preventing them from blocking others > while they do unrelated work. > > In the end, this looks like a simple problem but it isn't when you > consider the possibility of transactions rolling back. > >> Our first try to solve this problem has been to convert these triggers >> into a constraint trigger which allows for DEFERRABLE INITIALLY DEFERRED >> flags. This, we are finding, is forcing the trigger function to run >> after the triggering transaction is completed. We believe this will fix >> our locking problem and hopefully speed up our inserts again. > That should help, but it's a form of trading timeliness off against > performance. Queries within the same transaction won't see the updated > `dataset' values, so if you're relying on them for correct operation > later in the transaction you could have issues. > > -- > Craig Ringer
pgsql-admin by date: