public inbox for [email protected]
help / color / mirror / Atom feedFrom: SATYANARAYANA NARLAPURAM <[email protected]>
To: Dean Rasheed <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: [BUG]: WHERE CURRENT OF cursor fail on tables that have virtual generated columns
Date: Sun, 19 Apr 2026 17:32:55 -0700
Message-ID: <CAHg+QDe3e-KOseG0SfumkX=Suu06YFZhchnv4c-7V_uWzDdmHA@mail.gmail.com> (raw)
In-Reply-To: <CAEZATCUyKUrL_4pDZ=pqL68BcWznoyuCqGS3-7sm-Lc_UxS9HQ@mail.gmail.com>
References: <CAHg+QDc_TwzSgb=B_QgNLt3mvZdmRK23rLb+RkanSQkDF40GjA@mail.gmail.com>
<CAEZATCUyKUrL_4pDZ=pqL68BcWznoyuCqGS3-7sm-Lc_UxS9HQ@mail.gmail.com>
HI,
On Sun, Apr 19, 2026 at 3:42 AM Dean Rasheed <[email protected]>
wrote:
> On Fri, 17 Apr 2026 at 21:04, SATYANARAYANA NARLAPURAM
> <[email protected]> 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
Attachments:
[application/octet-stream] v3-0001-vgc-where-current-of-fix.patch (5.3K, 3-v3-0001-vgc-where-current-of-fix.patch)
download | inline diff:
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index 84deed9a..d9845358 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -595,6 +595,14 @@ transformDeleteStmt(ParseState *pstate, DeleteStmt *stmt)
ACL_DELETE);
nsitem = pstate->p_target_nsitem;
+ /* disallow DELETE ... WHERE CURRENT OF on a view */
+ if (stmt->whereClause &&
+ IsA(stmt->whereClause, CurrentOfExpr) &&
+ pstate->p_target_relation->rd_rel->relkind == RELKIND_VIEW)
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("WHERE CURRENT OF on a view is not implemented"));
+
/* there's no DISTINCT in DELETE */
qry->distinctClause = NIL;
@@ -2868,6 +2876,14 @@ transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt)
true,
ACL_UPDATE);
+ /* disallow UPDATE ... WHERE CURRENT OF on a view */
+ if (stmt->whereClause &&
+ IsA(stmt->whereClause, CurrentOfExpr) &&
+ pstate->p_target_relation->rd_rel->relkind == RELKIND_VIEW)
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("WHERE CURRENT OF on a view is not implemented"));
+
if (stmt->forPortionOf)
qry->forPortionOf = transformForPortionOfClause(pstate,
qry->resultRelation,
diff --git a/src/backend/rewrite/rewriteManip.c b/src/backend/rewrite/rewriteManip.c
index 4bf4aa0d..9aa7ef60 100644
--- a/src/backend/rewrite/rewriteManip.c
+++ b/src/backend/rewrite/rewriteManip.c
@@ -1514,25 +1514,6 @@ replace_rte_variables_mutator(Node *node,
}
/* otherwise fall through to copy the var normally */
}
- else if (IsA(node, CurrentOfExpr))
- {
- CurrentOfExpr *cexpr = (CurrentOfExpr *) node;
-
- if (cexpr->cvarno == context->target_varno &&
- context->sublevels_up == 0)
- {
- /*
- * We get here if a WHERE CURRENT OF expression turns out to apply
- * to a view. Someday we might be able to translate the
- * expression to apply to an underlying table of the view, but
- * right now it's not implemented.
- */
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("WHERE CURRENT OF on a view is not implemented")));
- }
- /* otherwise fall through to copy the expr normally */
- }
else if (IsA(node, Query))
{
/* Recurse into RTE subquery or not-yet-planned sublink subquery */
diff --git a/src/test/regress/expected/generated_virtual.out b/src/test/regress/expected/generated_virtual.out
index fc41c480..58cdf310 100644
--- a/src/test/regress/expected/generated_virtual.out
+++ b/src/test/regress/expected/generated_virtual.out
@@ -1723,3 +1723,57 @@ select * from gtest33 where b is null;
reset constraint_exclusion;
drop table gtest33;
+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);
+-- UPDATE via cursor
+begin;
+declare cur1 cursor for select * from gtest_cursor order by id for update;
+fetch 1 from cur1;
+ id | a | b
+----+----+----
+ 1 | 10 | 20
+(1 row)
+
+update gtest_cursor set a = 99 where current of cur1;
+select * from gtest_cursor order by id;
+ id | a | b
+----+----+-----
+ 1 | 99 | 198
+ 2 | 20 | 40
+ 3 | 30 | 60
+(3 rows)
+
+commit;
+-- DELETE via cursor
+begin;
+declare cur2 cursor for select * from gtest_cursor order by id for update;
+fetch 1 from cur2;
+ id | a | b
+----+----+-----
+ 1 | 99 | 198
+(1 row)
+
+delete from gtest_cursor where current of cur2;
+select * from gtest_cursor order by id;
+ id | a | b
+----+----+----
+ 2 | 20 | 40
+ 3 | 30 | 60
+(2 rows)
+
+commit;
+-- WHERE CURRENT OF via a view on a table with VGC (should fail)
+create view gtest_cursor_view as select * from gtest_cursor;
+begin;
+declare cur3 cursor for select * from gtest_cursor_view for update;
+fetch 1 from cur3;
+ id | a | b
+----+----+----
+ 2 | 20 | 40
+(1 row)
+
+update gtest_cursor_view set a = 55 where current of cur3; -- fail
+ERROR: WHERE CURRENT OF on a view is not implemented
+rollback;
+drop view gtest_cursor_view;
+drop table gtest_cursor;
diff --git a/src/test/regress/sql/generated_virtual.sql b/src/test/regress/sql/generated_virtual.sql
index 9b32413e..48daf31a 100644
--- a/src/test/regress/sql/generated_virtual.sql
+++ b/src/test/regress/sql/generated_virtual.sql
@@ -906,3 +906,33 @@ select * from gtest33 where b is null;
reset constraint_exclusion;
drop table gtest33;
+
+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);
+
+-- UPDATE via cursor
+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;
+
+-- DELETE via cursor
+begin;
+declare cur2 cursor for select * from gtest_cursor order by id for update;
+fetch 1 from cur2;
+delete from gtest_cursor where current of cur2;
+select * from gtest_cursor order by id;
+commit;
+
+-- WHERE CURRENT OF via a view on a table with VGC (should fail)
+create view gtest_cursor_view as select * from gtest_cursor;
+begin;
+declare cur3 cursor for select * from gtest_cursor_view for update;
+fetch 1 from cur3;
+update gtest_cursor_view set a = 55 where current of cur3; -- fail
+rollback;
+drop view gtest_cursor_view;
+
+drop table gtest_cursor;
view thread (6+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: [BUG]: WHERE CURRENT OF cursor fail on tables that have virtual generated columns
In-Reply-To: <CAHg+QDe3e-KOseG0SfumkX=Suu06YFZhchnv4c-7V_uWzDdmHA@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox