Thread: 9.0.X FOR UPDATE|SHARE on Sub-Query Causes "cannot extract system attribute from virtual tuple" if Sub-Query Returns Records (BUG)
9.0.X FOR UPDATE|SHARE on Sub-Query Causes "cannot extract system attribute from virtual tuple" if Sub-Query Returns Records (BUG)
I actually posted a more detail posting on this issue but I have a lot of extra information that jumbles things up.
More simply if you run any query of the form:
SELECT subquerycolumn
FROM (
SELECT subquerycolumn FROM table WHERE [condition] FOR UPDATE -- WHERE is optional but obviously useful; FOR SHARE also causes this behavior
) intermediate
The error “cannot extract system attribute from virtual tuple” is thrown IIF the sub-query returns one or more records. My prior posting on this topic made it seem as if some environments worked because the query I was testing had a sub-query that was not returning any records. I have tested on both Linux and Windows (both 64-bit) versions of 9.0.3 and get this behavior.
Anyway, the above query form works in both 8.2 and 8.4 but fails in 9.0.3 (at least, through probably all 9.0.X releases)
I’ll ask in the other thread (once it appears) for suggestions as to possible workarounds (and maybe better performing) methods to accomplish my goal but figured the simple query form shown above should readily re-produce the exception on any 9.0.X system.
If there is a BUG (or when one is opened) responding to this message with a BUG# would be welcomed.
Thanks,
David J
Re: 9.0.X FOR UPDATE|SHARE on Sub-Query Causes "cannot extract system attribute from virtual tuple" if Sub-Query Returns Records (BUG)
"David Johnston" <polobo@yahoo.com> writes: > More simply if you run any query of the form: > SELECT subquerycolumn > FROM ( > SELECT subquerycolumn FROM table WHERE [condition] FOR UPDATE -- WHERE > is optional but obviously useful; FOR SHARE also causes this behavior > ) intermediate > The error "cannot extract system attribute from virtual tuple" is thrown IIF > the sub-query returns one or more records. Fixed, thanks for the report! http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=d5478c3391f8f1a243abbc3d9253aac3d6d3538e regards, tom lane
Re: 9.0.X FOR UPDATE|SHARE on Sub-Query Causes "cannot extract system attribute from virtual tuple" if Sub-Query Returns Records (BUG)
Tom, 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. David J -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Tom Lane Sent: Wednesday, February 09, 2011 11:37 PM To: David Johnston Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] 9.0.X FOR UPDATE|SHARE on Sub-Query Causes "cannot extract system attribute from virtual tuple" if Sub-Query Returns Records (BUG) "David Johnston" <polobo@yahoo.com> writes: > More simply if you run any query of the form: > SELECT subquerycolumn > FROM ( > SELECT subquerycolumn FROM table WHERE [condition] FOR UPDATE -- > WHERE is optional but obviously useful; FOR SHARE also causes this > behavior > ) intermediate > The error "cannot extract system attribute from virtual tuple" is > thrown IIF the sub-query returns one or more records. Fixed, thanks for the report! http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=d5478c3391f 8f1a243abbc3d9253aac3d6d3538e regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: 9.0.X FOR UPDATE|SHARE on Sub-Query Causes "cannot extract system attribute from virtual tuple" if Sub-Query Returns Records (BUG)
"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