query limit and with_for_update of clause(oracle) v1.1.02b

[Сергей Трошин]

When i use LIMIT and FOR UPDATE OF, I got the invalid sql query

1. If I use only with_for_update(of=column), then i get valid sql.

For example, query:

rs.query(table)\
.select_from(schema.SyncFlags)
.outerjoin(table, schema.SyncFlags.row_id ==table.__table__.primary_key.columns.values()[0])
.filter(
    schema.SyncFlags.cache_group_id == cg_id,
    schema.SyncFlags.table_name == table.__tablename__
)\
.with_for_update(nowait=True, of=schema.SyncFlags.sync, skip_locked=True)

valid SQL:

SELECT user_account.id_user_account AS user_account_id_user_acc_1, user_account.id_type AS user_account_id_type, user_account.id_user AS user_account_id_user, user_account.id_predpr AS user_account_id_predpr 
FROM cg_sync_flags LEFT OUTER JOIN user_account ON cg_sync_flags.row_id = user_account.id_user_account 
WHERE cg_sync_flags.cache_group_id = :cache_group_id_1 AND cg_sync_flags.table_name = :table_name_1 FOR UPDATE OF cg_sync_flags.sync NOWAIT SKIP LOCKED

2. When I add LIMIT clause, then query builder add parent query and my FOR UPDATE OF column reference become invalid. It is valid in subquery, but invalid in parent query.

Example:

rs.query(table)\
.select_from(schema.SyncFlags)\
.outerjoin(table, schema.SyncFlags.row_id==table.__table__.primary_key.columns.values()[0])\
.filter(
    schema.SyncFlags.cache_group_id == cg_id,
    schema.SyncFlags.table_name == table.__tablename__
)\
.with_for_update(nowait=True, of=schema.SyncFlags.sync, skip_locked=True)\
.limit(1000)

Invalid SQL:

SELECT user_account_id_user_acc_1, user_account_id_type, user_account_id_user, user_account_id_predpr 
FROM (SELECT user_account.id_user_account AS user_account_id_user_acc_1, user_account.id_type AS user_account_id_type, user_account.id_user AS user_account_id_user, user_account.id_predpr AS user_account_id_predpr 
FROM cg_sync_flags LEFT OUTER JOIN user_account ON cg_sync_flags.row_id = user_account.id_user_account 
WHERE cg_sync_flags.cache_group_id = :cache_group_id_1 AND cg_sync_flags.table_name = :table_name_1) 
WHERE ROWNUM <= :param_1 FOR UPDATE OF cg_sync_flags.sync NOWAIT SKIP LOCKED

3. Work around(not portable): use filter criteria:

rs.query(table)
.select_from(schema.SyncFlags)
.outerjoin(table, schema.SyncFlags.row_id == table.__table__.primary_key.columns.values()[0])
.filter(
    schema.SyncFlags.cache_group_id == cg_id,
    schema.SyncFlags.table_name == table.__tablename__,
    text("rownum <= :rownum").bindparams(bindparam('rownum', value=5, type_=Integer))
)
.with_for_update(nowait=True, of=schema.SyncFlags.sync, skip_locked=True)

Valid SQL:

SELECT user_account.id_user_account AS user_account_id_user_acc_1, user_account.id_type AS user_account_id_type, user_account.id_user AS user_account_id_user, user_account.id_predpr AS user_account_id_predpr 
FROM cg_sync_flags LEFT OUTER JOIN user_account ON cg_sync_flags.row_id = user_account.id_user_account 
WHERE cg_sync_flags.cache_group_id = :cache_group_id_1 AND cg_sync_flags.table_name = :table_name_1 AND rownum <= :rownum FOR UPDATE OF cg_sync_flags.sync NOWAIT SKIP LOCKED

[Mike Bayer]

It is valid in subquery, but invalid in parent query.

can you clarify this please? As I was not given a model here to work with, can you please test if the "FOR UPDATE" clause can now be specified in subqueries ? Oracle has not allowed this in the past. thanks.

[Mike Bayer]

nope

for_update_clause

The FOR UPDATE clause lets you lock the selected rows so that other users cannot lock or update the rows until you end your transaction. You can specify this clause only in a top-level SELECT statement, not in subqueries.

[Mike Bayer]

https://gerrit.sqlalchemy.org/#/q/Ia71b5fc4df6d326e73863f8ae9f96e8f1a5acfc1

[Сергей Трошин]

On orale-10.2, for update do not permitted in subquery.

Second work around:

select needed_columns..., column_for_update_inner_alias

from (

select needed_columns..., column_for_update as column_for_update_inner_alias

from ....

)

where rownum <= 1000

for update of column_for_update_inner_alias

On Sqlalchemy:

.with_for_update(of=column_for_update_inner_alias???)

[Сергей Трошин]

Thanks

[Mike Bayer]

Adapt "FOR UPDATE OF" with Oracle limit/offset

This modifies the Oracle ROWNUM limit/offset approach to accommodate for the "OF" clause in a "FOR UPDATE" phrase. The column expressions must be added to the selected subquery if necessary and adapted on the outside.

Change-Id: Ia71b5fc4df6d326e73863f8ae9f96e8f1a5acfc1 Fixes: #3741

→ <<cset f2ee514c757f>>

[Mike Bayer]

Adapt "FOR UPDATE OF" with Oracle limit/offset

This modifies the Oracle ROWNUM limit/offset approach to accommodate for the "OF" clause in a "FOR UPDATE" phrase. The column expressions must be added to the selected subquery if necessary and adapted on the outside.

Change-Id: Ia71b5fc4df6d326e73863f8ae9f96e8f1a5acfc1 Fixes: #3741 (cherry picked from commit d23797943786914cced58ed71ffb7b749e64c449)

→ <<cset d99ec752c752>>

[Mike Bayer]

Adapt "FOR UPDATE OF" with Oracle limit/offset

This modifies the Oracle ROWNUM limit/offset approach to accommodate for the "OF" clause in a "FOR UPDATE" phrase. The column expressions must be added to the selected subquery if necessary and adapted on the outside.

Change-Id: Ia71b5fc4df6d326e73863f8ae9f96e8f1a5acfc1 Fixes: #3741

→ <<cset d23797943786>>

[Mike Bayer]

Adapt "FOR UPDATE OF" with Oracle limit/offset

This modifies the Oracle ROWNUM limit/offset approach to accommodate for the "OF" clause in a "FOR UPDATE" phrase. The column expressions must be added to the selected subquery if necessary and adapted on the outside.

Change-Id: Ia71b5fc4df6d326e73863f8ae9f96e8f1a5acfc1 Fixes: #3741 (cherry picked from commit d23797943786914cced58ed71ffb7b749e64c449)

→ <<cset 271d4e9e712b>>

[Сергей Трошин]

Great! Thank you, Michael!