Horton
Menu

Thread

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Fix UPDATE/DELETE ... WHERE CURRENT OF on a table with virtual columns.

  1. [BUG]: WHERE CURRENT OF cursor fail on tables that have virtual generated columns

    SATYANARAYANA NARLAPURAM <satyanarlapuram@gmail.com> — 2026-04-17T20:03:36Z

    Hi hackers,

UPDATE and DELETE with WHERE CURRENT OF cursor fail on tables that have
virtual generated columns, erroring with "WHERE CURRENT OF on a view is not
implemented" even though the target is a regular table, not a view.

Repro:

create table gtest_cursor (id int primary key, a int, b int generated
always as (a * 2) virtual);
insert into gtest_cursor values (1, 10), (2, 20), (3, 30);

begin;
declare cur1 cursor for select * from gtest_cursor order by id for update;
fetch 1 from cur1;
update gtest_cursor set a = 99 where current of cur1;
select * from gtest_cursor order by id;
commit;

Analysis:
The bug stems from replace_rte_variables_mutator() in rewriteManip.c, which
unconditionally errors on any CurrentOfExpr referencing the target
relation. This appears to a check designed for view rewriting, where WHERE
CURRENT OF cannot be translated through a view. However, virtual generated
column (VGC) expansion also routes through this mutator. The rewriter's
expand_generated_columns_internal() calls ReplaceVarsFromTargetList(), and
the planner's expand_virtual_generated_columns() calls
pullup_replace_vars(), which calls replace_rte_variables(). Since virtual
generated columns use same mutator, while expanding virtual generated
columns returns the same error even though the table is not a view and the
cursor position is perfectly valid.

The fix adds bool error_on_current_of to replace_rte_variables_context. The
existing replace_rte_variables() is refactored into a static
replace_rte_variables_internal() that accepts the flag, with two public
wrappers: replace_rte_variables() (passes true, preserving existing
behavior) and replace_rte_variables_ext() (exposes the flag). The same
pattern is applied to ReplaceVarsFromTargetList() /
ReplaceVarsFromTargetListExtended(). In replace_rte_variables_mutator(),
the CurrentOfExpr error is now conditional on context->error_on_current_of.
The two VGC expansion call sites pass false; all other callers pass true.
The down side of this approach is that it is adding additional public API.

Alternative considered: RTE-lookup approach. Instead of a flag, the mutator
could look up the target RTE in the query's range table and check
rte->rtekind, if it is RTE_RELATION, skip the error. Since the mutator
doesn't have access to the range table and threading an RTE or range table
pointer through the context would be equally invasive I didn't pursue this
further. Went with the flag approach because it is simpler, explicit, and
keeps the mutator's contract clean.

Thoughts or any other ideas how to fix this?

Thanks,
Satya

  2. Re: [BUG]: WHERE CURRENT OF cursor fail on tables that have virtual generated columns

    Dean Rasheed <dean.a.rasheed@gmail.com> — 2026-04-19T10:42:28Z

    On Fri, 17 Apr 2026 at 21:04, SATYANARAYANA NARLAPURAM
<satyanarlapuram@gmail.com> wrote:
>
> Hi hackers,
>
> UPDATE and DELETE with WHERE CURRENT OF cursor fail on tables that have virtual generated columns, erroring with "WHERE CURRENT OF on a view is not implemented" even though the target is a regular table, not a view.
>

Nice catch!

> Analysis:
> The bug stems from replace_rte_variables_mutator() in rewriteManip.c, which unconditionally errors on any CurrentOfExpr referencing the target relation. This appears to a check designed for view rewriting, where WHERE CURRENT OF cannot be translated through a view. However, virtual generated column (VGC) expansion also routes through this mutator. The rewriter's expand_generated_columns_internal() calls ReplaceVarsFromTargetList(), and the planner's expand_virtual_generated_columns() calls pullup_replace_vars(), which calls replace_rte_variables(). Since virtual generated columns use same mutator, while expanding virtual generated columns returns the same error even though the table is not a view and the cursor position is perfectly valid.
>
> The fix adds bool error_on_current_of to replace_rte_variables_context. The existing replace_rte_variables() is refactored into a static replace_rte_variables_internal() that accepts the flag, with two public wrappers: replace_rte_variables() (passes true, preserving existing behavior) and replace_rte_variables_ext() (exposes the flag). The same pattern is applied to ReplaceVarsFromTargetList() / ReplaceVarsFromTargetListExtended(). In replace_rte_variables_mutator(), the CurrentOfExpr error is now conditional on context->error_on_current_of. The two VGC expansion call sites pass false; all other callers pass true. The down side of this approach is that it is adding additional public API.
>

Hmm, it seems to me that a much simpler fix is to check for use of
WHERE CURRENT OF on a view at parse time, and throw the error there.
Then the problematic rewriter check can simply be removed, as in the
attached v2 patch.

Regards,
Dean

  3. Re: [BUG]: WHERE CURRENT OF cursor fail on tables that have virtual generated columns

    SATYANARAYANA NARLAPURAM <satyanarlapuram@gmail.com> — 2026-04-20T00:32:55Z

    HI,

On Sun, Apr 19, 2026 at 3:42 AM Dean Rasheed <dean.a.rasheed@gmail.com>
wrote:

> On Fri, 17 Apr 2026 at 21:04, SATYANARAYANA NARLAPURAM
> <satyanarlapuram@gmail.com> wrote:
> >
> > Hi hackers,
> >
> > UPDATE and DELETE with WHERE CURRENT OF cursor fail on tables that have
> virtual generated columns, erroring with "WHERE CURRENT OF on a view is not
> implemented" even though the target is a regular table, not a view.
> >
>
> Nice catch!
>
> > Analysis:
> > The bug stems from replace_rte_variables_mutator() in rewriteManip.c,
> which unconditionally errors on any CurrentOfExpr referencing the target
> relation. This appears to a check designed for view rewriting, where WHERE
> CURRENT OF cannot be translated through a view. However, virtual generated
> column (VGC) expansion also routes through this mutator. The rewriter's
> expand_generated_columns_internal() calls ReplaceVarsFromTargetList(), and
> the planner's expand_virtual_generated_columns() calls
> pullup_replace_vars(), which calls replace_rte_variables(). Since virtual
> generated columns use same mutator, while expanding virtual generated
> columns returns the same error even though the table is not a view and the
> cursor position is perfectly valid.
> >
> > The fix adds bool error_on_current_of to replace_rte_variables_context.
> The existing replace_rte_variables() is refactored into a static
> replace_rte_variables_internal() that accepts the flag, with two public
> wrappers: replace_rte_variables() (passes true, preserving existing
> behavior) and replace_rte_variables_ext() (exposes the flag). The same
> pattern is applied to ReplaceVarsFromTargetList() /
> ReplaceVarsFromTargetListExtended(). In replace_rte_variables_mutator(),
> the CurrentOfExpr error is now conditional on context->error_on_current_of.
> The two VGC expansion call sites pass false; all other callers pass true.
> The down side of this approach is that it is adding additional public API.
> >
>
> Hmm, it seems to me that a much simpler fix is to check for use of
> WHERE CURRENT OF on a view at parse time, and throw the error there.
>

This patch looks simple and neat, is there any reason why it was done
differently earlier?



> Then the problematic rewriter check can simply be removed, as in the
> attached v2 patch.
>

I reviewed the patch, and it addresses the original bug and other existing
tests.
I verified it rejects the view with WHERE CURRENT OF on update and delete.

Updated the patch to include a test case to reject view update with WHERE
CURRENT OF.

Thanks,
Satya

  4. Re: [BUG]: WHERE CURRENT OF cursor fail on tables that have virtual generated columns

    Dean Rasheed <dean.a.rasheed@gmail.com> — 2026-04-20T07:56:23Z

    On Mon, 20 Apr 2026 at 01:33, SATYANARAYANA NARLAPURAM
<satyanarlapuram@gmail.com> wrote:
>
> On Sun, Apr 19, 2026 at 3:42 AM Dean Rasheed <dean.a.rasheed@gmail.com> wrote:
>>
>> Hmm, it seems to me that a much simpler fix is to check for use of
>> WHERE CURRENT OF on a view at parse time, and throw the error there.
>
> This patch looks simple and neat, is there any reason why it was done differently earlier?
>

I'm not sure, but possibly because it used to be possible to turn a
table into a view by defining a SELECT rule on it, which could have
rendered a parse-time check insufficient. That's no longer the case
though, and we now have similar parse-time relkind tests elsewhere
(e.g., for MERGE).

> Updated the patch to include a test case to reject view update with WHERE CURRENT OF.
>

That's already tested in portals.sql, which seems like a better place
for that test, since it's not related to virtual generated columns. I
don't think another test is necessary -- admittedly portals.sql only
tests DELETE, but the UPDATE code is the same, so I think the existing
test is sufficient. We don't obsessively try to achieve 100% coverage
in our tests.

Regards,
Dean

  5. Re: [BUG]: WHERE CURRENT OF cursor fail on tables that have virtual generated columns

    SATYANARAYANA NARLAPURAM <satyanarlapuram@gmail.com> — 2026-04-20T08:03:18Z

    Hi

On Mon, Apr 20, 2026 at 12:56 AM Dean Rasheed <dean.a.rasheed@gmail.com>
wrote:

> On Mon, 20 Apr 2026 at 01:33, SATYANARAYANA NARLAPURAM
> <satyanarlapuram@gmail.com> wrote:
> >
> > On Sun, Apr 19, 2026 at 3:42 AM Dean Rasheed <dean.a.rasheed@gmail.com>
> wrote:
> >>
> >> Hmm, it seems to me that a much simpler fix is to check for use of
> >> WHERE CURRENT OF on a view at parse time, and throw the error there.
> >
> > This patch looks simple and neat, is there any reason why it was done
> differently earlier?
> >
>
> I'm not sure, but possibly because it used to be possible to turn a
> table into a view by defining a SELECT rule on it, which could have
> rendered a parse-time check insufficient. That's no longer the case
> though, and we now have similar parse-time relkind tests elsewhere
> (e.g., for MERGE).
>
> > Updated the patch to include a test case to reject view update with
> WHERE CURRENT OF.
> >
>
> That's already tested in portals.sql, which seems like a better place
> for that test, since it's not related to virtual generated columns. I
> don't think another test is necessary -- admittedly portals.sql only
> tests DELETE, but the UPDATE code is the same, so I think the existing
> test is sufficient. We don't obsessively try to achieve 100% coverage
> in our tests.
>

Sounds good. Thanks for letting me know.

  6. Re: [BUG]: WHERE CURRENT OF cursor fail on tables that have virtual generated columns

    Dean Rasheed <dean.a.rasheed@gmail.com> — 2026-04-22T11:07:20Z

    On Mon, 20 Apr 2026 at 09:03, SATYANARAYANA NARLAPURAM
<satyanarlapuram@gmail.com> wrote:
>
>> > Updated the patch to include a test case to reject view update with WHERE CURRENT OF.
>>
>> That's already tested in portals.sql, which seems like a better place
>> for that test, since it's not related to virtual generated columns. I
>> don't think another test is necessary -- admittedly portals.sql only
>> tests DELETE, but the UPDATE code is the same, so I think the existing
>> test is sufficient. We don't obsessively try to achieve 100% coverage
>> in our tests.
>

I changed my mind on that and decided to add another test to
portals.sql, just in case, since it is a separate code branch.

I also spent some time looking to see whether there were any other
ways that replace_rte_variables() could have been called (e.g.,
subquery pullup) that would fall foul of this, but I couldn't find
any, so I think this bug only affects virtual generated columns.

Pushed and back-patched to v18. Thanks for the report!

Regards,
Dean