SqlAlchemy generates bad SQL for Postgresql SELECT...FOR UPDATE OF, because Postgresql requires the table parameter to NOT be schema qualified, but SqlAlchemy schema-qualifies the parameter anyway.

Basically, using the SELECT ... FOR UPDATE OF table on a schema qualified table is a syntax error in Postgresql. The schema name has to be omitted, i.e. instead of:

SELECT * FROM schema.table JOIN <other tables> FOR UPDATE OF schema.table

it needs to be

SELECT * FROM schema.table JOIN <other tables> FOR UPDATE OF table

If a schema is specified in the table object, SqlAlchemy always schema qualifies table names in its code generation, so this construct generates code that causes a syntax error in postgresql.

# users is Table object invoked with schema='s1'
s = select([users.c.name]).with_for_update(nowait=True, of=users)

generates code:

SELECT s1.users.name 
FROM s1.users FOR UPDATE OF s1.users NOWAIT

with traceback result:

#!
$ python3 for_update_bug.py

Traceback (most recent call last):
  File "/home/byeh/python3/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
    context)
  File "/home/byeh/python3/lib/python3.4/site-packages/sqlalchemy/engine/default.py", line 450, in do_execute
    cursor.execute(statement, parameters)
psycopg2.ProgrammingError: FOR UPDATE must specify unqualified relation names
LINE 2: FROM s1.users FOR UPDATE OF s1.users NOWAIT
                                    ^

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "update_for_bug.py", line 25, in <module>
    conn.execute(s)
  File "/home/byeh/python3/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 914, in execute
    return meth(self, multiparams, params)
  File "/home/byeh/python3/lib/python3.4/site-packages/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/home/byeh/python3/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 1010, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/home/byeh/python3/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 1146, in _execute_context
    context)
  File "/home/byeh/python3/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 1341, in _handle_dbapi_exception
    exc_info
  File "/home/byeh/python3/lib/python3.4/site-packages/sqlalchemy/util/compat.py", line 188, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=exc_value)
  File "/home/byeh/python3/lib/python3.4/site-packages/sqlalchemy/util/compat.py", line 181, in reraise
    raise value.with_traceback(tb)
  File "/home/byeh/python3/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
    context)
  File "/home/byeh/python3/lib/python3.4/site-packages/sqlalchemy/engine/default.py", line 450, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) FOR UPDATE must specify unqualified relation names

Tested on SqlAlchemy 1.0.9, Python 3.4.3, Ubuntu 14.04, Postgresql 9.3

Attached file contains test case: for_update_bug.py

Comments (9)