Robert Haas <robertmh...@gmail.com> wrote: > Kevin Grittner <kevin.gritt...@wicourts.gov> wrote: >> Well, personally I have a hard time calling READ COMMITTED >> behavior sensible. Consider this: >> >> [ gigantic pile of fail ] > > Yeah, that's bad all right. I think it's hard to argue that the > current behavior is sensible; the trick is to figure out something > that's better but doesn't impose too much additional overhead. I > wonder if it's possible to use SSI (or some stripped-down > mechanism along similar lines) to track these kinds of write > conflicts, rather than cluttering the system catalogs with lots > more TransactionId fields. Like, when we TRUNCATE a table, we > could essentially make a note in memory someplace recording the > write conflict. Potential additional uses of the predicate locking developed for SSI keep surfacing. At some point we should probably pick a couple of them and try to fashion an API for the non-blocking predicate locking logic that serves them and SSI. Since this predicate locking system is explicitly interested only in read-write conflicts, it does seem like it could work for SELECT FOR UPDATE versus writes. As mentioned once or twice before, it was pretty clear that while predicate locking is required for SSI and is probably 80% of the C code in the patch, it is really a separate thing -- we just didn't want to try to create a "generalized" API based on the one initial usage. I think that an API based on registering and listening would be the ticket. > Unfortunately, the full-blown SSI machinery seems too expensive to > use for this, especially on all-read workloads where there are no > actual conflicts but lots of conflict checks. In an all-read workload, if you actually declare the transactions to be read-only SSI should not introduce much overhead. If there's much overhead showing up there at the moment, it would probably be pretty easy to eliminate. When there are any read-write transactions active, it's a different story. > But that could probably be optimized. Undoubtedly. It's disappointing that neither Dan nor I could find the round tuits to make the kinds of changes in the SSI locking that you made in some other areas for 9.2. I'm not really sure how the performance impact breaks down between predicate locking and SSI proper, although I would tend to start from the assumption that, like the lines of code, it's 80% in the predicate locking. > The attraction of using something like an in-memory conflict > manager is that it would probably be quite general. We could fix > problems of this type with no on-disk format changes whenever we > discover them (as we will certainly continue to do) just by adding > the appropriate flag-a-conflict calls to the right places in the > code. Assuming that the problems could be expressed in terms of read-write conflicts, that's probably largely true. I'm not sure that holds for some of the funny READ COMMITTED behaviors, though. I think the only real "cure" there would be to make subtransactions cheap enough that we could wrap execution of each SELECT and DML statement in a subtransaction and roll back for another try with a new snapshot on conflict. If you want to track something other than read-write conflicts and/or you want blocking when a conflict is found, you might be better off looking to bend the heavyweight locks to your purposes. -Kevin
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
