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. #3573
Description
Migrated issue, originally created by Bernard Yeh (@bernardy)
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
Attachments: for_update_bug.py | for_update_bug.patch