Missing "SKIP_LOCKED" from inner subquery of CTE

19 views
Skip to first unread message

Joel Gibson

unread,
Aug 12, 2019, 9:23:13 PM8/12/19
to sqlalchemy
Hi all,

I've got a queue in postgres that currently uses a raw sql query to dequeue items. The queue has multiple "topics" that the metadata/selector uses to dequeue specific items

DELETE FROM queue
WHERE id = (
SELECT id
FROM queue
WHERE :selector = ANY (metadata)
ORDER BY queue_date
FOR UPDATE SKIP LOCKED
LIMIT 1
)
RETURNING *;

I'm trying to put this into sqlalchemy, and have the following code.

search = (
session.query(Queue.id)
.filter(Queue.metadata.any_() == selector)
.order_by(Queue.queue_date)
.with_for_update(skip_locked=True)
.limit(1)
.cte("search")
)

delete_returning = (
Queue.__table__.delete()
.where(search == Queue.id)
.returning(literal("*"))
)

However the SQL generated by SQLalchmey is missing the SKIP_LOCKED statement, which is quite important for the efficiency of the queue. 

Generated SQL: 

DELETE FROM queue WHERE queue.id = (SELECT queue.id
FROM queue
WHERE :param_1 = ANY (queue.metadata) ORDER BY queue.queue_date
 LIMIT :param_2 FOR UPDATE) RETURNING :param_3 AS anon_1


This is strange to me as printing the string from just the search subquery (if you take out the CTE) includes the SKIP_LOCKED statement

SELECT queue.id AS queue
FROM queue
WHERE %(param_1)s = ANY (queue.metadata) ORDER BY queue.queue_date
 LIMIT %(param_2)s FOR UPDATE SKIP LOCKED

I'm using SQLAlchemy 1.2.0, python 3.6 on OSX, psycopg2==2.7.1 and postgres 9.6.8

The other way of solving this would be just to keep using raw SQL and convert the resulting RowProxy to the ORM object, so if anyone knows what the proper way of going about that, it would also be appreciated :) 

Thanks, Joel

Mike Bayer

unread,
Aug 12, 2019, 10:01:02 PM8/12/19
to noreply-spamdigest via sqlalchemy


On Mon, Aug 12, 2019, at 9:23 PM, Joel Gibson wrote:
Hi all,

I've got a queue in postgres that currently uses a raw sql query to dequeue items. The queue has multiple "topics" that the metadata/selector uses to dequeue specific items

DELETE FROM queue
WHERE id = (
SELECT id
FROM queue
WHERE :selector = ANY (metadata)
ORDER BY queue_date
FOR UPDATE SKIP LOCKED
LIMIT 1
)
RETURNING *;


so, that above is not a CTE.   You should be able to replicate that SQL exactly by using a scalar subquery.








I'm trying to put this into sqlalchemy, and have the following code.

search = (
session.query(Queue.id)
.filter(Queue.metadata.any_() == selector)
.order_by(Queue.queue_date)
.with_for_update(skip_locked=True)
.limit(1)
.cte("search")
)

delete_returning = (
Queue.__table__.delete()
.where(search == Queue.id)
.returning(literal("*"))
)



so the CTE use here is being discarded, since a CTE is intended to be used as a FROM clause, not as a scalar expression.    In 1.4, the above use will raise an error:

SQL expression element or literal value expected, got <sqlalchemy.sql.selectable.CTE at 0x7f40de2c62e8; search>.

in 1.3, the query is being converted to a scalar subquery implicitly, which is creating confusion in that you are led to believe you are using a CTE properly.  

It doesn't matter for now, but ideally you'd want to use as_scalar(), not cte():

....limit(1).as_scalar()



However the SQL generated by SQLalchmey is missing the SKIP_LOCKED statement, which is quite important for the efficiency of the queue. 

Generated SQL: 

DELETE FROM queue WHERE queue.id = (SELECT queue.id
FROM queue
WHERE :param_1 = ANY (queue.metadata) ORDER BY queue.queue_date
 LIMIT :param_2 FOR UPDATE) RETURNING :param_3 AS anon_1

I see the paramstyle is :named, which here would indicate you are just running str(statement) and not telling SQLAlchemy that you'd like to see PostgreSQL's version of SQL which supports SKIP LOCKED.  SKIP LOCKED is not in the SQL standard so is not emitted by the default dialect.  For  a Postgresql string, normally the statement is invoked by the connection which will use the correct SQL dialect.  But if you are stringifying and not executing, set up the correct dialect:

from sqlalchemy.dialects import postgresql

print(delete_returning.compile(dialect=postgresql.dialect()))

output:

DELETE FROM queue WHERE queue.id = (SELECT queue.id
FROM queue
WHERE %(param_1)s = ANY (queue.metadata) ORDER BY queue.queue_date
LIMIT %(param_2)s FOR UPDATE SKIP LOCKED) RETURNING %(param_3)s AS anon_1







This is strange to me as printing the string from just the search subquery (if you take out the CTE) includes the SKIP_LOCKED statement

SELECT queue.id AS queue
FROM queue
WHERE %(param_1)s = ANY (queue.metadata) ORDER BY queue.queue_date
 LIMIT %(param_2)s FOR UPDATE SKIP LOCKED

I'm using SQLAlchemy 1.2.0, python 3.6 on OSX, psycopg2==2.7.1 and postgres 9.6.8

The other way of solving this would be just to keep using raw SQL and convert the resulting RowProxy to the ORM object, so if anyone knows what the proper way of going about that, it would also be appreciated :) 

Thanks, Joel


--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
 
 
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

Joel Gibson

unread,
Aug 13, 2019, 11:47:57 AM8/13/19
to sqlalchemy
Yup, converting to a scalar and using the postgres compiler I now see the correct output. The one other thing I had to fix was `column_literal(*)` vs `literal(*)` Thanks for the detailed reply.
Reply all
Reply to author
Forward
0 new messages