Is “with_connection” necessary with “exec_query”?
up vote
4
down vote
favorite
I'm occasionally getting PG::TRDeadlockDetected on a Rails with PostgreSQL app (served via Passenger/Nginx) and traced it down to places in the code which use ActiveRecord::Base.connection.exececute.
In order to fix this, I'm replacing .execute with .exec_query or .exec_update as per what the docs recommend. However, it's not really made clear whether ActiveRecord::Base.connection.exec_query gets the connection from the connection pool or not.
- When using
ActiveRecord::Base.connection.exec_queryin a Rails controller, does it have to be wrapped withActiveRecord::Base.connection_pool.with_connection? (my guess: yes)
- When using
ActiveRecord::Base.connection.exec_queryoutside of the context of a request (say a Rake tasks executed as cronjob), does it have to be wrapped withActiveRecord::Base.connection_pool.with_connection? (my guess: yes)
And if wrapping is necessary, are there shorter alternatives compared to:
ActiveRecord::Base.connection_pool.with_connection do |connection|
connection.exec_update "REINDEX INDEX my_complex_index"
end
(my guess: no)
Thanks for your hints!
ruby-on-rails ruby postgresql activerecord
asked Nov 17 at 9:50
svoop
2,0241528
add a comment |
up vote
4
down vote
favorite
I'm occasionally getting PG::TRDeadlockDetected on a Rails with PostgreSQL app (served via Passenger/Nginx) and traced it down to places in the code which use ActiveRecord::Base.connection.exececute.
In order to fix this, I'm replacing .execute with .exec_query or .exec_update as per what the docs recommend. However, it's not really made clear whether ActiveRecord::Base.connection.exec_query gets the connection from the connection pool or not.
- When using
ActiveRecord::Base.connection.exec_queryin a Rails controller, does it have to be wrapped withActiveRecord::Base.connection_pool.with_connection? (my guess: yes)
- When using
ActiveRecord::Base.connection.exec_queryoutside of the context of a request (say a Rake tasks executed as cronjob), does it have to be wrapped withActiveRecord::Base.connection_pool.with_connection? (my guess: yes)
And if wrapping is necessary, are there shorter alternatives compared to:
ActiveRecord::Base.connection_pool.with_connection do |connection|
connection.exec_update "REINDEX INDEX my_complex_index"
end
(my guess: no)
Thanks for your hints!
ruby-on-rails ruby postgresql activerecord
asked Nov 17 at 9:50
svoop
2,0241528
Interesting, got any source for that? I'm experiencing the same issue with the same stack. Was not too sure about what causes it and just realized it started occuring after I added model methods that call raw sql with.execute.
– mkrl
Nov 17 at 11:23
Seems threading related to me
– Dorian
Nov 17 at 11:34
@mkrl Theexecutevsexec_queryis in fact unrelated to the deadlock but should assure proper cleanup.
– svoop
Nov 17 at 15:02
@mkrl As for the connection pool, I've plowed through the AR source, but I'm not 100% sure I got things right (and therefore ask here). My guess is, the deadlock occurs when different threads pick up the same connection and create circular locks. The pooling of connections should prevent this.
– svoop
Nov 17 at 15:07
@svoop yes, I was thinking of that. Not sure about your case, but I'm using the open-source version. As far as I know, only passenger enterprise is able to spawn additional threads.
– mkrl
Nov 17 at 15:12
add a comment |
up vote
4
down vote
favorite
up vote
4
down vote
favorite
I'm occasionally getting PG::TRDeadlockDetected on a Rails with PostgreSQL app (served via Passenger/Nginx) and traced it down to places in the code which use ActiveRecord::Base.connection.exececute.
In order to fix this, I'm replacing .execute with .exec_query or .exec_update as per what the docs recommend. However, it's not really made clear whether ActiveRecord::Base.connection.exec_query gets the connection from the connection pool or not.
- When using
ActiveRecord::Base.connection.exec_queryin a Rails controller, does it have to be wrapped withActiveRecord::Base.connection_pool.with_connection? (my guess: yes)
- When using
ActiveRecord::Base.connection.exec_queryoutside of the context of a request (say a Rake tasks executed as cronjob), does it have to be wrapped withActiveRecord::Base.connection_pool.with_connection? (my guess: yes)
And if wrapping is necessary, are there shorter alternatives compared to:
ActiveRecord::Base.connection_pool.with_connection do |connection|
connection.exec_update "REINDEX INDEX my_complex_index"
end
(my guess: no)
Thanks for your hints!
ruby-on-rails ruby postgresql activerecord
asked Nov 17 at 9:50
svoop
2,0241528
I'm occasionally getting PG::TRDeadlockDetected on a Rails with PostgreSQL app (served via Passenger/Nginx) and traced it down to places in the code which use ActiveRecord::Base.connection.exececute.
In order to fix this, I'm replacing .execute with .exec_query or .exec_update as per what the docs recommend. However, it's not really made clear whether ActiveRecord::Base.connection.exec_query gets the connection from the connection pool or not.
- When using
ActiveRecord::Base.connection.exec_queryin a Rails controller, does it have to be wrapped withActiveRecord::Base.connection_pool.with_connection? (my guess: yes)
- When using
ActiveRecord::Base.connection.exec_queryoutside of the context of a request (say a Rake tasks executed as cronjob), does it have to be wrapped withActiveRecord::Base.connection_pool.with_connection? (my guess: yes)
And if wrapping is necessary, are there shorter alternatives compared to:
ActiveRecord::Base.connection_pool.with_connection do |connection|
connection.exec_update "REINDEX INDEX my_complex_index"
end
(my guess: no)
Thanks for your hints!
ruby-on-rails ruby postgresql activerecord
ruby-on-rails ruby postgresql activerecord
asked Nov 17 at 9:50
svoop
2,0241528
asked Nov 17 at 9:50
svoop
2,0241528
edited Nov 17 at 10:50
asked Nov 17 at 9:50
svoop
2,0241528
asked Nov 17 at 9:50
svoop
2,0241528
asked Nov 17 at 9:50
svoop
2,0241528
2,0241528
Interesting, got any source for that? I'm experiencing the same issue with the same stack. Was not too sure about what causes it and just realized it started occuring after I added model methods that call raw sql with.execute.
– mkrl
Nov 17 at 11:23
Seems threading related to me
– Dorian
Nov 17 at 11:34
@mkrl Theexecutevsexec_queryis in fact unrelated to the deadlock but should assure proper cleanup.
– svoop
Nov 17 at 15:02
@mkrl As for the connection pool, I've plowed through the AR source, but I'm not 100% sure I got things right (and therefore ask here). My guess is, the deadlock occurs when different threads pick up the same connection and create circular locks. The pooling of connections should prevent this.
– svoop
Nov 17 at 15:07
@svoop yes, I was thinking of that. Not sure about your case, but I'm using the open-source version. As far as I know, only passenger enterprise is able to spawn additional threads.
– mkrl
Nov 17 at 15:12
add a comment |
Interesting, got any source for that? I'm experiencing the same issue with the same stack. Was not too sure about what causes it and just realized it started occuring after I added model methods that call raw sql with.execute.
– mkrl
Nov 17 at 11:23
Seems threading related to me
– Dorian
Nov 17 at 11:34
@mkrl Theexecutevsexec_queryis in fact unrelated to the deadlock but should assure proper cleanup.
– svoop
Nov 17 at 15:02
@mkrl As for the connection pool, I've plowed through the AR source, but I'm not 100% sure I got things right (and therefore ask here). My guess is, the deadlock occurs when different threads pick up the same connection and create circular locks. The pooling of connections should prevent this.
– svoop
Nov 17 at 15:07
@svoop yes, I was thinking of that. Not sure about your case, but I'm using the open-source version. As far as I know, only passenger enterprise is able to spawn additional threads.
– mkrl
Nov 17 at 15:12
Interesting, got any source for that? I'm experiencing the same issue with the same stack. Was not too sure about what causes it and just realized it started occuring after I added model methods that call raw sql with
.execute.– mkrl
Nov 17 at 11:23
Interesting, got any source for that? I'm experiencing the same issue with the same stack. Was not too sure about what causes it and just realized it started occuring after I added model methods that call raw sql with
.execute.– mkrl
Nov 17 at 11:23
Seems threading related to me
– Dorian
Nov 17 at 11:34
Seems threading related to me
– Dorian
Nov 17 at 11:34
@mkrl The
execute vs exec_query is in fact unrelated to the deadlock but should assure proper cleanup.– svoop
Nov 17 at 15:02
@mkrl The
execute vs exec_query is in fact unrelated to the deadlock but should assure proper cleanup.– svoop
Nov 17 at 15:02
@mkrl As for the connection pool, I've plowed through the AR source, but I'm not 100% sure I got things right (and therefore ask here). My guess is, the deadlock occurs when different threads pick up the same connection and create circular locks. The pooling of connections should prevent this.
– svoop
Nov 17 at 15:07
@mkrl As for the connection pool, I've plowed through the AR source, but I'm not 100% sure I got things right (and therefore ask here). My guess is, the deadlock occurs when different threads pick up the same connection and create circular locks. The pooling of connections should prevent this.
– svoop
Nov 17 at 15:07
@svoop yes, I was thinking of that. Not sure about your case, but I'm using the open-source version. As far as I know, only passenger enterprise is able to spawn additional threads.
– mkrl
Nov 17 at 15:12
@svoop yes, I was thinking of that. Not sure about your case, but I'm using the open-source version. As far as I know, only passenger enterprise is able to spawn additional threads.
– mkrl
Nov 17 at 15:12
add a comment |
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53350039%2fis-with-connection-necessary-with-exec-query%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Interesting, got any source for that? I'm experiencing the same issue with the same stack. Was not too sure about what causes it and just realized it started occuring after I added model methods that call raw sql with
.execute.– mkrl
Nov 17 at 11:23
Seems threading related to me
– Dorian
Nov 17 at 11:34
@mkrl The
executevsexec_queryis in fact unrelated to the deadlock but should assure proper cleanup.– svoop
Nov 17 at 15:02
@mkrl As for the connection pool, I've plowed through the AR source, but I'm not 100% sure I got things right (and therefore ask here). My guess is, the deadlock occurs when different threads pick up the same connection and create circular locks. The pooling of connections should prevent this.
– svoop
Nov 17 at 15:07
@svoop yes, I was thinking of that. Not sure about your case, but I'm using the open-source version. As far as I know, only passenger enterprise is able to spawn additional threads.
– mkrl
Nov 17 at 15:12