[
https://issues.apache.org/jira/browse/CALCITE-2395?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16533064#comment-16533064
]
Julian Hyde commented on CALCITE-2395:
--------------------------------------
Even if "FOR UPDATE" (i.e. a boolean) is sufficient for your purposes, there is
a fair chance that someone in future would need "FOR UPDATE OF ..." (i.e. a
List of SqlIdentifier). It depends somewhat on what the standard says, but I
would go for a list. "FOR UPDATE" would be represented by an empty list; if
"FOR UPDATE" is missing, the list would be null.
As for how to represent this in the planner. How about a variant of TableModify
with a new TableModify.Operation value LOCK? Its output would be the same as
its input, but it would have the side effect of taking a lock on each row. For
example,
{code}SELECT *
FROM Emp
WHERE deptno = 10
FOR UPDATE{code}
would become
{noformat}
Filter(deptno=10)
TableModify(LOCK, EMP, EMPNO)
Scan(EMP)
{noformat}
You might some rules: e.g. in the above query, you would want to push Filter
through this TableModify, to lock as few rows as possible.
With a join query,
{code}SELECT *
FROM Emp JOIN Dept USING (deptno)
WHERE sal > 100000
FOR UPDATE OF (Emp, Dept){code}
the lock operators occur after the join (so as to lock as few rows as
possible), and therefore have to specify both which table they are locking and
which column(s) is the primary key:
{noformat}
TableModify(LOCK, EMP, EMP.EMPNO)
TableModify(LOCK, DEPT, DEPT.DEPTNO)
Join(EMP.DEPTNO = DEPT.DEPTNO)
Filter(SAL > 100000)
Scan(EMP)
Scan(DEPT)
{noformat}
> Support SELECT xxx FROM TABLE FOR UPDATE syntax
> -----------------------------------------------
>
> Key: CALCITE-2395
> URL: https://issues.apache.org/jira/browse/CALCITE-2395
> Project: Calcite
> Issue Type: New Feature
> Components: core
> Affects Versions: 1.16.0
> Reporter: Enrico Olivelli
> Assignee: Julian Hyde
> Priority: Major
>
> I am using Calcite SQL Parser and Volcano Planner.
> I need to support SQL syntax
> SELECT ... FROM table FOR UPDATE
>
> see for instance PostGre docs
> [https://www.postgresql.org/docs/9.5/static/sql-select.html.]
>
> I would like at least to support this syntax at SQL Parser level, the 'for
> update' spec should be reported by the RelNode so that the system can take it
> into account and perform explicit locking.
>
> Linked downstream project issue:
> https://github.com/diennea/herddb/issues/228
>
>
>
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
