PG connection is in transaction but PostgreSQLAdapter in_transaction? method returns false

Instead of opening transaction through rails way , if I begin transaction using pg connection directly PostgreSQLAdapter#in_transaction? method returns false.

For example

adapter = ActiveRecord::Base.connection
con = adapter.raw_connection 
con.query("BEGIN")
adapter.in_transaction? #returns false

Reason because in_transaction? not actually checks out connection transaction status . Could we modify logic to check the connection transaction status ?

Something like this

def in_transaction?
   open_transactions > 0 || @connection.transaction_status == PG::PQTRANS_INTRANS
end
1 Like

Is there a real-world issue caused by the current implementation of in_transaction? The reason I ask is because in_transaction? is private to Rails which means it shouldn’t be used by applications directly and current implementation is sufficient for Rails needs since Rails controls in what context the method is being used.

Also the example is not executable as-is as it fails with

NoMethodError: private method `in_transaction?' called for #<ActiveRecord::ConnectionAdapters::PostgreSQLAdapter

We could use .send to have a working reproduction script but it’s just another sign that the method is private and not supposed to be used by an application.

Though speaking about hypothetical fix in case if in_transaction? was a public method I’d prefer open_transactions counter behavior to be changed rather than in_transaction? itself. Otherwise we may end up in situation where open_transactions is 0 while in_transaction returns true which doesn’t make sense

1 Like

Thanks for your quick response.

One scenario I could think of is Rails 7 load_async loads query in the foreground thread if connection is in transaction(instead of scheduling it in the background).

If in case I am executing begin command directly using pg connection, load_async method unable to identify connection in transaction state and as a result it schedules query in the background thread.

con = adapter.raw_connection
con.query("BEGIN")
p = Post.where("id is not null").load_async # Executes in background thread

Although this scenario is not linked to in_transaction? method, root cause is same as it doesn’t checks for connection’s transaction status. Thought like having this check would give us more confident on saying the transaction status.

1 Like

I’m afraid this example still doesn’t seem like something an application will do. First of all raw_connection is an instance of PG::Connection which Rails has very little control over. So the query will have to at least go through the Rails connection like adapter.query("BEGIN") But even this doesn’t seem to be very welcoming to be used so ideally the “begin transaction” and “end transaction” actions should be abstracted on the adapter, so the code will look like:

adapter.begin_transaction
p = Post.where("id is not null").load_async
adapter.end_transaction

But then it makes it hard to argue why this new API would be better than existing adapter.transaction do end syntax