can't reproduce:
from sqlalchemy import *
m = MetaData()
t = Table('t', m, Column('x', Integer))
s = select([t], for_update=read)
from sqlalchemy.dialects import postgresql
print s.compile(dialect=postgresql.dialect())
e = create_engine(postgresql://scott:tiger@localhost/test, echo=True)
with e.begin() as conn:
m.create_all(conn)
conn.execute(s)
the output shows that both as a standalone compile as well as within a
Postgresql conversation, we get FOR SHARE. Tested in 0.7.9 and 0.8.0b2. If
you can provide a full runnable test case, that might shed more light.
SELECT t.x
FROM t FOR SHARE
2012-12-20 23:53:33,670 INFO sqlalchemy.engine.base.Engine select version()
2012-12-20 23:53:33,671 INFO sqlalchemy.engine.base.Engine {}
2012-12-20 23:53:33,672 INFO sqlalchemy.engine.base.Engine select
current_schema()
2012-12-20 23:53:33,672 INFO sqlalchemy.engine.base.Engine {}
2012-12-20 23:53:33,674 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2012-12-20 23:53:33,674 INFO sqlalchemy.engine.base.Engine select relname from
pg_class c join pg_namespace n on n.oid=c.relnamespace where
n.nspname=current_schema() and relname=%(name)s
2012-12-20 23:53:33,675 INFO sqlalchemy.engine.base.Engine {'name': u't'}
2012-12-20 23:53:33,676 INFO sqlalchemy.engine.base.Engine SELECT t.x
FROM t FOR SHARE
2012-12-20 23:53:33,676 INFO sqlalchemy.engine.base.Engine {}
2012-12-20 23:53:33,676 INFO sqlalchemy.engine.base.Engine COMMIT
On Dec 20, 2012, at 9:01 PM, utkonos wrote:
I am trying SELECT FOR SHARE a set of rows in a table so that they are locked
until the end of the transaction. I am using SQLAlchemy 0.7.9 to do this in a
PostgreSQL 9.1.6 database. This is the python code in question:
NUM_TERMS = 10
conn = engine.connect()
get_terms = select([search_terms.c.term_id, search_terms.c.term],
and_(search_terms.c.lock==False,
search_terms.c.status==False),
order_by=search_terms.c.term,
limit=NUM_TERMS, for_update=read)
trans = conn.begin()
try:
search_terms = conn.execute(get_terms).fetchall()
for term in search_terms:
lock_terms = update(search_terms).\
where(search_terms.c.term_id==term.term_id).\
values(lock=True)
conn.execute(lock_terms)
if trans.commit():
do things with the search terms
except:
trans.rollback()
The problem is the SQL query generated by the select code above is not FOR
SHARE, it's FOR UPDATE:
SELECT search_terms.term_id, search_terms.term
FROM search_terms
WHERE search_terms.lock = :lock_1 AND search_terms.status = :status_1
ORDER BY search_terms.term
LIMIT :param_1 FOR UPDATE
According to the SQLAlchemy API docs, under the for_update parameter
description:
With the Postgresql dialect, the values “read” and read_nowait translate to
FOR SHARE and FOR SHARE NOWAIT, respectively.
According to the above, the compiled SQL statement should be FOR SHARE, but
it is not. Where is the error in my code?
If you have a good answer, please also answer it on stackoverflow
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/wmFM_7UILRoJ.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
