3 ms·

def with_synchronous_commit_off(&block) ActiveRecord::Base.connection.exec_query("SET synchronous_commit = off") yield ensure ActiveRecord

def with_synchronous_commit_off(&block) ActiveRecord::Base.connection.exec_query("SET synchronous_commit = off") yield ensure ActiveRecord::Base.connection.exec_query("SET synchronous_commit = on") end I'm not a Rails expert: does this reliably give you the same connection from the pool? If not, you could be setting `synchronous_commit = off` on one "physical" connection, and clearing it on another. This is setting it at the Postgres session level, so it will persist for the lifetime of the connection. If it's not managed reliably, it could lead to some very confusing bugs (only triggered in a very small window when Postgres shuts down unexpectedly while the transaction is still unsynced to durable storage, making it extra fun to debug). In general, though, this is a very handy feature.

cyberax 2y ago
Yes, the connection is "sticky" for the duration of the request. The middleware also automatically starts a transaction for you when you check out the connection.
- shayonj 2y ago
  There is something to be said about using SET LOCAL SESSION maybe. Esp. when using pgbouncer in between rails app and PostgreSQL and using session var
drdaeman 2y ago
In your code, in the `ensure` part you want `SET synchronous_commit TO DEFAULT` instead of the explicit `on` (https://www.postgresql.org/docs/current/sql-set.html https://www.postgresql.org/docs/current/sql-set.html) Then, I suspect you may probably rather want to use `SET LOCAL` rather than just `SET`, so it applies to a single transaction only. So maybe something more like def asynchronous_transaction(&block) ActiveRecord::Base.connection.transaction do ActiveRecord::Base.connection.execute("SET LOCAL synchronous_commit = off") yield end end Please note that I don't really know Rails (in particular, I've no idea whenever `Base.connection` is guaranteed to be exactly the same throughout this function's lifecycle or if e.g. there's a connection pool underneath), so I could be introducing some subtle bugs here.

cyberax 2y ago

Yes, the connection is "sticky" for the duration of the request. The middleware also automatically starts a transaction for you when you check out the connection.

shayonj 2y ago
There is something to be said about using SET LOCAL SESSION maybe. Esp. when using pgbouncer in between rails app and PostgreSQL and using session var

drdaeman 2y ago

In your code, in the `ensure` part you want `SET synchronous_commit TO DEFAULT` instead of the explicit `on` (https://www.postgresql.org/docs/current/sql-set.html https://www.postgresql.org/docs/current/sql-set.html) Then, I suspect you may probably rather want to use `SET LOCAL` rather than just `SET`, so it applies to a single transaction only. So maybe something more like def asynchronous_transaction(&block) ActiveRecord::Base.connection.transaction do ActiveRecord::Base.connection.execute("SET LOCAL synchronous_commit = off") yield end end Please note that I don't really know Rails (in particular, I've no idea whenever `Base.connection` is guaranteed to be exactly the same throughout this function's lifecycle or if e.g. there's a connection pool underneath), so I could be introducing some subtle bugs here.