| From: | SATYANARAYANA NARLAPURAM <satyanarlapuram(at)gmail(dot)com> |
|---|---|
| To: | PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
| Subject: | [BUG]: WHERE CURRENT OF cursor fail on tables that have virtual generated columns |
| Date: | 2026-04-17 20:03:36 |
| Message-ID: | CAHg+QDc_TwzSgb=B_QgNLt3mvZdmRK23rLb+RkanSQkDF40GjA@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
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
| Attachment | Content-Type | Size |
|---|---|---|
| v1-0001-vgc-where-current-of-fix.patch | application/octet-stream | 11.9 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Jelte Fennema-Nio | 2026-04-17 20:10:12 | Re: Add editorconfig support for Postgres spec files |
| Previous Message | Jeff Davis | 2026-04-17 19:30:37 | small cleanup patches for collation code |