PG Error: PG::UnableToSend #83
Comments
|
Uhh nice, was never able to reproduce this crazyness on mysql :) |
|
Maybe try the reconnect inside the block or in various other places |
|
Tried all 6 combinations (each/map, in, after, in/after). Couldn't get any different result. What should the output of |
|
Last time I tried it was nil, so I added the Not sure how to fix that, maybe there is some more general answer out there Last time we had this problem we loaded everything before forking :D On Thu, Oct 10, 2013 at 6:22 AM, Tyler DeWitt [email protected]:
|
|
This issue was a bear. My use case for this gem is importing data from a legacy Oracle database to a new postgres database. I want the import to run as fast as possible and forking concurrent processes was an obvious way to speed things up. There are a variety of import functions that all call a private process function that (initially) looked something like: def process (collection, options={})
return if collection.size == 0
options[:title] ||= collection.first.class.to_s.titleize.split('/').last.pluralize
options[:parallel] = true unless options.has_key?(:parallel)
progress_bar = ProgressBar.create(title: options[:title], total: collection.size, format: '%t: |%B| %c/%C (%P%%)%E')
if options[:parallel]
Parallel.each(collection, finish: lambda{|item, index, result| progress_bar.increment}) do |item|
yield item
end
else
collection.each do |item|
yield item
progress_bar.increment
end
end
progress_bar.finish
endThis worked great until one of the import functions I wrote caused the issue mentioned here. Everything up to that point worked fine, but the next interaction with postgres would reliably fail. I tried the suggestions here and in the readme everywhere I could think of, but got nowhere. Running everything without parallel worked fine so I thought 'Okay, parallel must be doing something funky... I'll try something simple', which brought me to something like this: def process (collection, options={})
return if collection.size == 0
options[:title] ||= collection.first.class.to_s.titleize.split('/').last.pluralize
options[:parallel] = true unless options.has_key?(:parallel)
progress_bar = ProgressBar.create(title: options[:title], total: collection.size, format: '%t: |%B| %c/%C (%P%%)%E')
if options[:parallel]
pids = []
num_processes = Parallel.processor_count
collection.in_groups(num_processes, false) do |group|
pids << Process.fork do
group.each do |item|
yield item
progress_bar.increment
end
end
end
pids.each do |pid|
Process.waitpid(pid)
end
else
collection.each do |item|
yield item
progress_bar.increment
end
end
progress_bar.finish
endBut that also failed reliably at the same point that using parallel did. I really didn't want to give up on parallel processing for this, so I eventually came upon #62 (which I think is the same issue as this) and the comment from @LFDM. Finally I had an implementation that would get past the point it was failing with: def process (collection, options={})
return if collection.size == 0
options[:title] ||= collection.first.class.to_s.titleize.split('/').last.pluralize
options[:parallel] = true unless options.has_key?(:parallel)
progress_bar = ProgressBar.create(title: options[:title], total: collection.size, format: '%t: |%B| %c/%C (%P%%)%E')
if options[:parallel]
Parallel.each(collection, finish: lambda{|item, index, result| progress_bar.increment}) do |item|
yield item
end
begin
ActiveRecord::Base.connection.reconnect!
rescue
ActiveRecord::Base.connection.reconnect!
end
else
collection.each do |item|
yield item
progress_bar.increment
end
end
progress_bar.finish
endKind of a long post, but the fact that I could reproduce the same problem without parallel (but not without forking processes) indicates that there is some kind of issue when forking processes with activerecord and postgres and that this is not a problem caused by parallel. A hackaround is better than nothing and I'm glad I found @LFDM's post in #62 - I never would have thought to retry reconnecting a second time (have I ever told you the definition of insanity?), but it would still be nice to know what the heck is going on. Hope that helps someone. Maybe it's worth considering adding @LFDM's suggestion to the readme since it actually works? |
|
@jschroeder9000 Thanks for the detailed breakdown. This helped me a lot! |
|
any way we can make this work better out of the box ? |
|
fyi you can now use |
|
I'm running into this issue following a MySQL -> Postgres conversion. I use Parallel extensively in rendering views by silently generating fragment caches with Parallel then a normal pass to render them out of memcached. I use this pattern enough I wrote a gem for it. Here's the relevant helper: https://github.com/duffyjp/duffy/blob/master/lib/duffy/beast_mode_helper.rb This all works fine in MySQL, but in Postgres with the same application and same data I get the errors listed here and the behavior in #62. I've tried the three README workarounds, and even the quite hideous ... and nothing has worked. Has there been any progress on this issue in the last couple years?
|
|
status afaik is that there are various workarounds for mysql ... not sure which one is best ... |
The funny thing is with mysql I've never once needed to use any of those workarounds. It's always worked for me out of the box. Same with Oracle. I even do both at the same time, reading from Oracle, writing to mysql, 16 threads at once. Do you have any gut feeling where the problem might be? |
|
I started writing an issue on the pg gem's bitbucket page, but following their guide I think it might be more productive to start here again. Here's what I found: postgres + thread = okay I built some minimal test scripts following the pg's issue tracker guide which I'll post here: pg_thread_test.rb#!/usr/bin/env ruby
require 'pg'
conn = PG.connect( :dbname => 'test', user: 'test', password: 'test' )
$stderr.puts '---',
RUBY_DESCRIPTION,
PG.version_string( true ),
"Server version: #{conn.server_version}",
"Client version: #{PG.respond_to?( :library_version ) ? PG.library_version : 'unknown'}",
'---'
result1 = conn.exec( "SELECT * FROM people LIMIT 1;" )
$stderr.puts "Non Threaded:"
$stderr.puts %Q{Expected this to return: ["1"]}
p result1.field_values('id')
t = Thread.new do
result2 = conn.exec( "SELECT * FROM people LIMIT 1;" )
puts "In the Thread:"
puts %Q{Expected this to return: ["1"]}
p result2.field_values('id')
end
t.join()
result3 = conn.exec( "SELECT * FROM people LIMIT 1;" )
$stderr.puts "Repeat Non Threaded:"
$stderr.puts %Q{Expected this to return: ["1"]}
p result3.field_values('id')Output: |
pg_ar_thread_test.rb#!/usr/bin/env ruby
require 'pg'
require 'active_record'
$stderr.puts '---',
RUBY_DESCRIPTION,
PG.version_string( true ),
"ActiveRecord version: #{ActiveRecord.version}",
"Client version: #{PG.respond_to?( :library_version ) ? PG.library_version : 'unknown'}",
'---'
class Person < ActiveRecord::Base
establish_connection(
adapter: "postgresql",
host: "localhost",
username: "test",
password: "test",
database: "test"
)
end
puts "Non Threaded:"
puts "Expect: 1"
puts Person.first.id
t = Thread.new do
puts "In the Thread:"
puts "Expect: 1"
puts Person.first.id
end
t.join()
puts "Repeat Non Threaded:"
puts "Expect: 1"
puts Person.first.idOutput: |
pg_ar_parallel_test.rb#!/usr/bin/env ruby
require 'pg'
require 'active_record'
require 'parallel'
$stderr.puts '---',
RUBY_DESCRIPTION,
PG.version_string( true ),
"ActiveRecord version: #{ActiveRecord.version}",
"Parallel version: #{Parallel::VERSION}",
"Client version: #{PG.respond_to?( :library_version ) ? PG.library_version : 'unknown'}",
'---'
class Person < ActiveRecord::Base
establish_connection(
adapter: "postgresql",
host: "localhost",
username: "test",
password: "test",
database: "test"
)
end
puts "Non Threaded:"
puts "Expect: 1"
puts Person.first.id
puts "Parallel Gem:"
puts "Expect 1,2,3 in any order"
Parallel.each(Person.limit(3), in_processes: 3) do |person|
puts person.id
end
puts "Repeat Non Threaded:"
puts "Expect: 1"
puts Person.first.idOutput: |
|
Multithreading might work in newer mysql versions, did not need to use We could start by having a few tests that show how it does work (with On Thu, Mar 24, 2016 at 6:59 AM, Jacob Duffy [email protected]
|
|
Okay, I didn't expect this: I tried the last file, but with mysql instead, and it fails... Even though similar statements work just peachy in my full-stack Rails app. Maybe there's another component at play. mysql_ar_parallel_test.rb#!/usr/bin/env ruby
require 'mysql2'
require 'active_record'
require 'parallel'
$stderr.puts '---',
RUBY_DESCRIPTION,
"MySQL version: #{}",
"ActiveRecord version: #{ActiveRecord.version}",
"Parallel version: #{Parallel::VERSION}",
'---'
class Person < ActiveRecord::Base
establish_connection(
adapter: "mysql2",
host: "localhost",
username: "test",
password: "test",
database: "test"
)
end
puts "Non Threaded:"
puts "Expect: 1"
puts Person.first.id
puts "Parallel Gem:"
puts "Expect 1,2,3 in any order"
Parallel.each(Person.limit(3), in_processes: 3) do |person|
puts person.id
end
puts "Repeat Non Threaded:"
puts "Expect: 1"
puts Person.first.idOutput: Edit:Workaround # 1 does work for this Parallel.each(Person.limit(3), in_processes: 3) do |person|
puts person.id
end
ActiveRecord::Base.connection.reconnect!I'll try in Postgres this afternoon. |
|
I think this is because the connection is taken into the fork and then On Thu, Mar 24, 2016 at 9:00 AM, Jacob Duffy [email protected]
|
|
Had same issue. From the PSQL manual:
Source: https://www.postgresql.org/docs/9.0/static/libpq-connect.html |
|
Given this, here's the way I've dealt with using PSQL databases with multiple processes: (works perfectly) |
|
just wanted to say that I've been debugging something related for hours and this...
...was a huge aha moment for me. yay! 😹 |
workmaster2n/parallel@1d5eae3
When I run
I get back:
It appears that something is going awry with postgres.
The text was updated successfully, but these errors were encountered: