- attached for_update_bug.patch
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)
-
reporter -
reporter - changed component to postgres
-
repo owner - changed milestone to 1.0.xx
this is straightforward if you'd like to submit a pull request w/ tests that would expedite
-
reporter Might not be until this weekend before I can put together a pull request. (Patch above is because I needed this for my own work right away).
-
Pull request with unit test here: https://github.com/zzzeek/sqlalchemy/pull/216
-
repo owner - changed status to resolved
- Postgres: Do not prefix table with schema in: "FOR UPDATE of <table>"
For example, this query:
SELECT s1.users.name FROM s1.users FOR UPDATE OF s1.users
should actually be:
SELECT s1.users.name FROM s1.users FOR UPDATE OF users
fixes
#3573→ <<cset fd47fea6fbb1>>
-
repo owner → <<cset f4cb3f8cacaf>>
-
repo owner - Postgres: Do not prefix table with schema in: "FOR UPDATE of <table>"
For example, this query:
SELECT s1.users.name FROM s1.users FOR UPDATE OF s1.users
should actually be:
SELECT s1.users.name FROM s1.users FOR UPDATE OF users
fixes
#3573(cherry picked from commit fd47fea6fbb11ee84b7eea5772f40855703ebe47)
→ <<cset 104cfe149ab7>>
-
repo owner (cherry picked from commit f4cb3f8cacaf96f064c2c7e4c1e579c7cf6544da)
→ <<cset fb22efc9de8d>>
- Log in to comment
I've attached a patch file (unified diff format) to fix this issue. 3 lines changed in 2 files. Basically added another keyword parameter to suppress schema output when outputting table names. Patch is on version 1.0.9.