Re: 9.0.X FOR UPDATE|SHARE on Sub-Query Causes "cannot extract system attribute from virtual tuple" if Sub-Query Returns Records (BUG) - Mailing list pgsql-general

"David Johnston" <polobo@yahoo.com> writes:
>> From your commit notes:

>  "This wasn't a problem before 9.0 because we didn't support FOR UPDATE
> below the top query level..."

> FWIW I had been using a sub-query FOR UPDATE in one of my key queries (one
> that was called multiple times per second) and relied upon the FOR UPDATE to
> avoid having the same record "dispatched" multiple times.  It worked just
> fine in 8.2.X and 8.4.X - supported or not.

Yeah, what that actually meant was that we didn't support FOR UPDATE
below the top level of the query *as executed*.  The optimizer used to
flatten subqueries containing FOR UPDATE if it could (and fail if it
couldn't).  9.0 changes that behavior because it led to FOR UPDATE
locking getting applied in unexpected/unpredictable ways in more complex
queries, eg joins.

            regards, tom lane