1

I am using SQLAlchemy and pg8000 to connect to a Postgres database.

I have checked table pg_stat_activity, which shows me a few select queries in 'idle in transaction' state, many of those. But the application much more reads than writes, that is, inserts are few and far between.

I suspect that a new transaction is created for each query, even for simple select statements.

Is it possible to run a read-only query without the need for a transaction? So that it does not need to be committed/rolled back?

Currently, the app runs its queries with method sqlalchemy.engine.Engine.execute for CRUD operations and cursors for calling stored procedures. How should I update these method calls to indicate I want some of them not to start transactions?

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
Leonel
  • 28,541
  • 26
  • 76
  • 103
  • 1
    SQLAlchemy versions prior to 1.4 did offer some ways to specify "autocommit" as an execution option, but they are deprecated in 1.4. The recommended approach is to execute statements inside of a `with` block (context manager) so the transaction will automatically be committed or rolled back on exit from that block. – Gord Thompson Jan 19 '21 at 19:16

0 Answers0