On Fri, 2007-06-01 at 20:06 +0200, Florian G. Pflug wrote: > Simon Riggs wrote: > > On Mon, 2007-05-28 at 19:56 -0400, Bruce Momjian wrote: > >> Added to TODO: > >> > >> * Fix self-referential UPDATEs seeing inconsistent row versions in > >> read-committed mode > >> > >> http://archives.postgresql.org/pgsql-hackers/2007-05/msg00507.php > >> > > > > I'm sorry guys but I don't agree this is a TODO item. > > Maybe the TODO suggested has a too narrow focus, but I think that > that *something* has to be done about this. > > > IMHO this follows documented behaviour, even if y'all are shocked. > Yes, but documented != sensible && documented != intuitive && > documented != logical.
I've regrettably spent too many years with locking databases, so to me the behaviour is perfectly logical, and intuitive, because I didn't expect a lock avoiding strategy to be "free". We have a way of bypassing locks, but there is no way to bypass the need to think about what the answer will be if you do chose to bypass them. This is just a classic database problem. Many books and courses bypass these issues because they appear to be solved. Every couple of years people go ballistic when they discover this kind of stuff exists; its made the papers and SIGMOD too. I don't mean to dismiss everybody's concerns, but it does seem I have a different perspective on them. > > In the case of concurrent UPDATEs the second UPDATE will normally > > perform the subSELECT then hang waiting to perform the UPDATE. If you > > use FOR SHARE the query will hang on the subSELECT (i.e. slightly > > earlier), which makes the second query return zero rows, as some of you > > were expecting. > > Sure, but with a similar argument you could question the whole > update-in-read-committed-mode logic. After all, you wouldn't need > that logic if you always obtained a share lock on the rows to be updated > *before* you started updating them. Document it better if you will, or add an option that would allow you to set the default to always use read locks, but there's just nothing actually wrong with the current behaviour. BTW, DB2 has just such an option, IIRC, but the equivalent "Repeatable Read" (doesn't mean same thing as the ANSI phrase) mode is not recommended for normal use. You'll then run up against the need to optimise the locking further and end up with "Cursor Stability" mode. Thats a lot of work for something few people will use in practice. Read Committed == Show me the data, whether or not its been updated Bear in mind this has nothing to do with self-referencing joins, its a problem-of-misunderstanding of any UPDATE or DELETE with a sub-select that references a table that can be updated concurrently. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match