On Wed, 2007-01-24 at 14:54 +1100, John Bartlett wrote: > The reason for those 5 options is to consider different means to cover the > Prepared Stmt requirement where the different stages of processing are > actually in different transactions.
John, Thanks for explaining. Wow! I've never come across such a requirement before, personally and hadn't even imagined anybody would want to do this. ISTM the main use for positioned UPDATE/DELETE is for a single transaction to first open a cursor and then loop around doing FETCH and then positioned UPDATE/DELETE on that cursor. It would make the implementation considerably easier to limit the initial implementation to only work using WITHOUT HOLD cursors (the default). This will allow you to cache the ctid, rather than re-seeking via the index, so will offer considerably better performance also. That is also the safe thing to do, since PostgreSQL's implementation of WITH HOLD cursors doesn't leave the rows locked. That can lead to the rows being deleted from under the cursor, for which the standard is unclear as to whether that is acceptable, or not. AFAICS the SQL Standard also requires that the positioned Update/Delete also effect only a single row. When using WITH HOLD cursors the desired row's ctid may have changed. Re-executing the original WHERE condition might easily reveal more than one row where previously there was only one. The cursor itself provides no mechanism for telling rows apart in that circumstance when no Primary Key is defined on the table. We can surround that with various checks, maybe. ISTM that even allowing this using WITH HOLD cursors seems likely to be both a poor-performing and fragile application programming technique. I'd suggest we add the combination of WITH HOLD cursors and positioned updates to the small pile of SQL standard items we don't really want to support for practical reasons. At very least, I'd suggest we do the straightforward part of this for 8.3 and see whether we want a more full implementation in later releases. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
