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
Stringifying SQL constructs is described in the FAQ at
https://docs.sqlalchemy.org/en/13/faq/sqlexpressions.html#stringifying-for-specific-databases
>
>
> 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
>
> http://www.sqlalchemy.org/
>
> 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+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/6f85d3e5-9d1f-4553-bc0e-7156bf129b87%40googlegroups.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/6f85d3e5-9d1f-4553-bc0e-7156bf129b87%40googlegroups.com?utm_medium=email&utm_source=footer>.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/0b20460e-a2f8-4634-9c61-3f7685dc967a%40www.fastmail.com.
