"Copy from" fails silently

[mthompsonsf]

I'm using Postgres' "COPY FROM STDIN" functionality to bulkload some data. The entire process fails if a single row fails, and the result of the process is "PGRES_FATAL_ERROR". Is there a way to a) capture the error for the failing row, and b) allow a single row to fail without causing the entire process to fail?  I've attached a version of the sample copyfrom.rb file that includes one good row, and one failing row. The failure is reported in the postgres logs, but not the script output:

2013-08-12 11:47:44 PDT ERROR:  22P04: extra data after last expected column
2013-08-12 11:47:44 PDT CONTEXT:  COPY logs, line 2: ""127.0.0.1","","30/Aug/2010:08:21:24 -0700","GET /favicon.ico HTTP/1.1",404,209,"

Script output:

Opening database connection ...
Running COPY command with data ...
  sending 157 bytes of data...
Result of COPY is: PGRES_FATAL_ERROR

Zero rows are copied.

[Lars Kanis]

Am 12.08.2013 20:58, schrieb mthompsonsf:
> I'm using Postgres' "COPY FROM STDIN" functionality to bulkload some
> data. The entire process fails if a single row fails, and the result
> of the process is "PGRES_FATAL_ERROR". Is there a way to a) capture
> the error for the failing row,

Yes, I've done some minor modifications to your file in the attached
diff, that should raise a proper exception in case of errors both on
database side and on client side.

> and b) allow a single row to fail without causing the entire process
> to fail?

No, the entire copy process is running in a single transaction.

Regards,
Lars

[Lars Kanis]

Am 12.08.2013 22:21, schrieb Lars Kanis:
> Am 12.08.2013 20:58, schrieb mthompsonsf:
>> I'm using Postgres' "COPY FROM STDIN" functionality to bulkload some
>> data. The entire process fails if a single row fails, and the result
>> of the process is "PGRES_FATAL_ERROR". Is there a way to a) capture
>> the error for the failing row,
> Yes, I've done some minor modifications to your file in the attached
> diff, that should raise a proper exception in case of errors both on
> database side and on client side.
>

The copy process is not as simple as it should be, so I added proper
usage examples to the gem documentation:
https://bitbucket.org/ged/ruby-pg/commits/995900317

@ged: Maybe we should add two convenience methods, that take a block for
the data handling and cares about proper error handling around this
block. Could be named PG::Connection#copy_put and #copy_get ?

Regards,
Lars