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.
