How to SELECT FOR SHARE in PostgreSQL dialect?

[utkonos]

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

[Michael Bayer]

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

--
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 sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

[Michael Bayer]

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

particularly suspicious is that the paramstyle above, known as "named" (i.e. :param), is not the paramstyle used by the current Postgresql DBAPIs - psycopg2 uses %(pyformat)s and pg8000 defaults to "format".   this suggests the above is just the result of saying "print statement".   What do you see actually being sent to the database ?