I am running a Postgres DB and using the COPY command in ruby to load data into a temporary table.
It looks something like this:
@conn = PG.connect(dbname: 'load_test')
res = @conn.async_exec <<-QUERY
COPY tmp_inventory FROM '#{infile_location}' CSV HEADER DELIMITER '|'
QUERY
There is an INFO log message with the total number of rows added. How could I get this number programmatically from within ruby?
You can do this inside Postgres or get it from the Postgres interface in Ruby directly:
More specifically the class PGresult.
I am not a Ruby expert, but studying the documentation here and here, it seems the method cmd_tuples only works for INSERT|DELETE|UPDATE|MOVE|FETCH.
There are also the constants PGRES_COPY_OUT or PGRES_COPY_IN (for the case at hand). It seems the method result_status returns the respective value:
Returns the status of the query. The status value is one of:
PGRES_EMPTY_QUERY PGRES_COMMAND_OK PGRES_TUPLES_OK PGRES_COPY_OUT PGRES_COPY_IN PGRES_BAD_RESPONSE PGRES_NONFATAL_ERROR PGRES_FATAL_ERROR PGRES_COPY_BOTH
So, in your example you should get the desired value with:
res.result_status
You would use a PL/pgSQL function to access the number of rows processed by COPY programmatically (version 9.2+ required):
Erwin Brandstetter was on the right track. Despite what the Ruby API documentation currently says (it needs updating), PG::Result#cmd_tuples does work with COPY commands, at least as of PostgreSQL 9.6.3. It probably didn't when the question was asked, but the libpq documentation for PQcmdTuples now includes COPY, so I'll update the Ruby API's docs to reflect that.
This is a modified version of one of the examples in the source's sample/ directory that illustrates this using the PG::Connection#copy_data wrapper method around libpq's COPY API:
require 'pg'
require 'stringio'
$stderr.puts "Opening database connection ..."
conn = PG.connect( dbname: 'test' )
conn.exec( <<END_SQL )
DROP TABLE IF EXISTS logs;
CREATE TABLE logs (
client_ip inet,
username text,
ts timestamp,
request text,
status smallint,
bytes int
);
END_SQL
csv_io = StringIO.new( <<"END_DATA" )
"127.0.0.1","","30/Aug/2010:08:21:24 -0700","GET /manual/ HTTP/1.1",404,205
"127.0.0.1","","30/Aug/2010:08:21:24 -0700","GET /favicon.ico HTTP/1.1",404,209
"127.0.0.1","","30/Aug/2010:08:21:24 -0700","GET /favicon.ico HTTP/1.1",404,209
"127.0.0.1","","30/Aug/2010:08:22:29 -0700","GET /manual/ HTTP/1.1",200,11094
"127.0.0.1","","30/Aug/2010:08:22:38 -0700","GET /manual/index.html HTTP/1.1",200,725
"127.0.0.1","","30/Aug/2010:08:27:56 -0700","GET /manual/ HTTP/1.1",200,11094
"127.0.0.1","","30/Aug/2010:08:27:57 -0700","GET /manual/ HTTP/1.1",200,11094
"127.0.0.1","","30/Aug/2010:08:28:06 -0700","GET /manual/index.html HTTP/1.1",200,7709
"127.0.0.1","","30/Aug/2010:08:28:06 -0700","GET /manual/images/feather.gif HTTP/1.1",200,6471
"127.0.0.1","","30/Aug/2010:08:28:06 -0700","GET /manual/images/left.gif HTTP/1.1",200,60
"127.0.0.1","","30/Aug/2010:08:28:06 -0700","GET /manual/style/css/manual.css HTTP/1.1",200,18674
"127.0.0.1","","30/Aug/2010:08:28:06 -0700","GET /manual/style/css/manual-print.css HTTP/1.1",200,13200
"127.0.0.1","","30/Aug/2010:08:28:06 -0700","GET /manual/images/favicon.ico HTTP/1.1",200,1078
"127.0.0.1","","30/Aug/2010:08:28:06 -0700","GET /manual/style/css/manual-loose-100pc.css HTTP/1.1",200,3065
"127.0.0.1","","30/Aug/2010:08:28:14 -0700","OPTIONS * HTTP/1.0",200,0
"127.0.0.1","","30/Aug/2010:08:28:15 -0700","OPTIONS * HTTP/1.0",200,0
"127.0.0.1","","30/Aug/2010:08:28:47 -0700","GET /manual/mod/directives.html HTTP/1.1",200,33561
"127.0.0.1","","30/Aug/2010:08:28:53 -0700","GET /manual/mod/mpm_common.html HTTP/1.1",200,67683
"127.0.0.1","","30/Aug/2010:08:28:53 -0700","GET /manual/images/down.gif HTTP/1.1",200,56
"127.0.0.1","","30/Aug/2010:08:28:53 -0700","GET /manual/images/up.gif HTTP/1.1",200,57
"127.0.0.1","","30/Aug/2010:09:19:58 -0700","GET /manual/mod/mod_log_config.html HTTP/1.1",200,28307
"127.0.0.1","","30/Aug/2010:09:20:19 -0700","GET /manual/mod/core.html HTTP/1.1",200,194144
"127.0.0.1","","30/Aug/2010:16:02:56 -0700","GET /manual/ HTTP/1.1",200,11094
"127.0.0.1","","30/Aug/2010:16:03:00 -0700","GET /manual/ HTTP/1.1",200,11094
"127.0.0.1","","30/Aug/2010:16:06:16 -0700","GET /manual/mod/mod_dir.html HTTP/1.1",200,10583
"127.0.0.1","","30/Aug/2010:16:06:44 -0700","GET /manual/ HTTP/1.1",200,7709
END_DATA
### You can test the error case from the database side easily by
### changing one of the numbers at the end of one of the above rows to
### something non-numeric like "-".
$stderr.puts "Running COPY command with data ..."
buf = ''
conn.transaction do
res = conn.copy_data( "COPY logs FROM STDIN WITH csv" ) do
$stderr.print "Sending lines... "
csv_io.each_line.with_index do |buf, i|
$stderr.print "#{i + 1} "
conn.put_copy_data( buf )
end
$stderr.puts "done."
end
$stderr.puts "Result of COPY is: %s" % [ res.res_status(res.result_status) ]
$stderr.puts " tuples copied: %p" % [ res.cmd_tuples ]
end
conn.finish
When it's run it outputs:
$ ruby sample/copyfrom.rb
Opening database connection ...
Running COPY command with data ...
Sending lines... 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 done.
Result of COPY is: PGRES_COMMAND_OK
tuples copied: 26
The PG::Result#result_status method just returns an integer result status code, and PGRES_COPY_OUT/PGRES_COPY_IN are used by the low level COPY API so they're not very useful in the normal case.
