On Fri, Jun 2, 2017 at 5:18 PM, Frank Millman <fr...@chagford.com> wrote: > As I said, I cannot prove this, but the theory fits the observed behaviour > perfectly, so I have proceeded on the assumption that it is true. Therefore > I now always run every SQL command or block of commands within a context > manager, which always calls conn.commit() or conn.rollback() on exit, and I > have not had any more problems. I use exactly the same code for sqlite3 and > for Sql Server/pyodbc, and it has not caused any problems there either.
+1. A bit more info: When you perform read-only queries against a PostgreSQL database, you still have transactional integrity, just as you would with mutating transactions. Two SELECT statements in the same transaction will see a consistent view of the underlying database. To accomplish this, the database creates low-grade locks, so it knows which things you're using. (It's not quite that simple, since Postgres uses MVCC, but broadly speaking it's so.) Thus transactions are just as important for SELECT statements as they are for INSERT or UPDATE... or, for that matter, ALTER TABLE (this is a point on which not all DBMSes agree - transactional DDL is one of the features I love about Postgres). Always using a context manager is good practice and great for code clarity. I would be inclined to mandate it in a style guide, if I were in charge of any good-sized psycopg2-based project. ChrisA -- https://mail.python.org/mailman/listinfo/python-list