Upgrade to python-sql 1.4.1 breaks tryton-worker in Trytond 5.0.38

The upgrade of python-sql from 1.4.0 to 1.4.1 causes the trytond-worker command to fail with the error:

Traceback (most recent call last):
  File "/usr/local/bin/trytond-worker", line 26, in <module>
  File "/usr/local/lib/python3.8/site-packages/trytond/worker.py", line 68, in work
    task_id, next_ = queue.pull(options.name)
  File "/usr/local/lib/python3.8/site-packages/trytond/worker.py", line 33, in pull
    return Queue.pull(self.database, self.connection, name=name)
  File "/usr/local/lib/python3.8/site-packages/trytond/ir/queue.py", line 118, in pull
  File "/usr/local/lib/python3.8/site-packages/trytond/backend/postgresql/database.py", line 66, in execute
    cursor.execute(self, sql, args)
psycopg2.errors.UndefinedTable: relation "a" does not exist
LINE 1: ... "b")) RETURNING "id", (SELECT "a"."seconds" FROM "a" AS "a"...

The failing DB query is:

WITH "c" ("id", "scheduled_at", "expected_at") AS (SELECT "a"."id", "a"."scheduled_at", "a"."expected_at" FROM "ir_queue" AS "a" WHERE (true AND ("a"."dequeued_at" IS NULL)) ORDER BY "a"."scheduled_at" NULLS FIRST, "a"."expected_at" NULLS FIRST), "b" ("id") AS (SELECT "c"."id" FROM "c" AS "c" WHERE ((("c"."scheduled_at" <= CURRENT_TIMESTAMP) OR ("c"."scheduled_at" IS NULL)) AND pg_try_advisory_xact_lock("c"."id")) ORDER BY "c"."scheduled_at" NULLS FIRST, "c"."expected_at" NULLS FIRST LIMIT 1), "d" ("seconds") AS (SELECT MIN(EXTRACT(\'EPOCH\' FROM ("c"."scheduled_at" - CURRENT_TIMESTAMP))) FROM "c" AS "c" WHERE ("c"."scheduled_at" >= CURRENT_TIMESTAMP)) UPDATE "ir_queue" AS "a" SET "dequeued_at" = CURRENT_TIMESTAMP WHERE ("a"."id" = (SELECT "b"."id" FROM "b" AS "b")) RETURNING "id", (SELECT "a"."seconds" FROM "a" AS "a")

Rollback to python-sql version 1.4.0 fixes the issue.

This is happening with a Trytond instance pinned to version 5.0.38. Please advise if the recommended fix is to upgrade Trytond.

Thanks, in advance, for your attention.

1 Like

Please report a bug.

I reopened Do not use alias in returning expression (#86) · Issues · Tryton / python-sql · GitLab

I created this issue report, Upgrade to python-sql 1.4.1 breaks tryton-worker in Trytond 5.0.38 (#12334) · Issues · Tryton / Tryton · GitLab. (Hopefully, this was the right place. I couldn’t find where to report python-sql issues, rather than tryton issues.)