Closed
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
Metadata
Metadata
Assignees
Labels
Type
Projects
Relationships
Development
No branches or pull requests
Participants
Activity
sqlalchemy-bot commentedon Nov 3, 2015
Bernard Yeh (@bernardy) wrote:
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.
sqlalchemy-bot commentedon Nov 3, 2015
Changes by Bernard Yeh (@bernardy):
sqlalchemy-bot commentedon Nov 3, 2015
Changes by Bernard Yeh (@bernardy):
sqlalchemy-bot commentedon Nov 3, 2015
Michael Bayer (@zzzeek) wrote:
this is straightforward if you'd like to submit a pull request w/ tests that would expedite
sqlalchemy-bot commentedon Nov 3, 2015
Changes by Michael Bayer (@zzzeek):
sqlalchemy-bot commentedon Nov 3, 2015
Bernard Yeh (@bernardy) wrote:
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).
sqlalchemy-bot commentedon Nov 24, 2015
diana clarke wrote:
Pull request with unit test here: zzzeek/sqlalchemy#216
sqlalchemy-bot commentedon Nov 30, 2015
Michael Bayer (@zzzeek) wrote:
For example, this query:
should actually be:
fixes 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
→ fd47fea
sqlalchemy-bot commentedon Nov 30, 2015
Michael Bayer (@zzzeek) wrote:
→ f4cb3f8
sqlalchemy-bot commentedon Nov 30, 2015
Michael Bayer (@zzzeek) wrote:
For example, this query:
should actually be:
fixes 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
(cherry picked from commit fd47fea)
→ 104cfe1
sqlalchemy-bot commentedon Nov 30, 2015
Michael Bayer (@zzzeek) wrote:
(cherry picked from commit f4cb3f8)
→ fb22efc
sqlalchemy-bot commentedon Nov 30, 2015
Changes by Michael Bayer (@zzzeek):
2 remaining items