Runtime error during COPY out get silently ignored

Issue #234 wontfix
Sok Ann Yap
created an issue

Please refer to http://www.postgresql.org/message-id/20160114040719.25839.34626@wrigleys.postgresql.org (originally, we thought it was a bug in libpq, thus posted to pgsql-bug mailing list)

Another example:

postgres=# create table a (duration bigint);
CREATE TABLE
postgres=# insert into a values (1111111);
INSERT 0 1
postgres=# insert into a values (1111111111);
INSERT 0 1
postgres=# insert into a values (111111111111111);
INSERT 0 1
[1] pry(main)> require 'pg'
=> true
[2] pry(main)> conn = PG.connect
=> #<PG::Connection:0x007f4fff7a6740>
[3] pry(main)> conn.exec "copy (select duration::int from a) to stdout"
=> #<PG::Result:0x007f4fff693768 status=PGRES_COPY_OUT ntuples=0 nfields=1 cmd_tuples=0>
[4] pry(main)> conn.get_copy_data
=> "1111111\n"
[5] pry(main)> conn.get_copy_data
=> "1111111111\n"
[6] pry(main)> conn.get_copy_data
=> nil
# In postgres log
2016-01-19 09:42:39 WIB ERROR:  integer out of range
2016-01-19 09:42:39 WIB STATEMENT:  copy (select duration::int from a) to stdout

Comments (4)

  1. Lars Kanis

    @Sok Ann Yap I strongly recommend you to use PG::Connection#copy_data in order to import or export bulk data. This should handle all the possible error cases the right way.

    Short story: We do a lot of bulk imports in my company, so COPY always was an essential tool for us. However it turned out, that not a single implementation, that made use of COPY, did the error handling right (not even the one I recommended to use). That was why I added #copy_data and a bunch of test cases into pg.gem, that make sure, that all corner cases are handled smoothly.

    So your example should look like the following. I also added a CopyRow decoder and a type map for casing the values to Array<Integer> , but this is optional and depends on your later use of the data.

    tm = PG::TypeMapByColumn.new([PG::TextDecoder::Integer.new])
    deco = PG::TextDecoder::CopyRow.new type_map: tm
    conn.copy_data("copy (select duration::int from a) to stdout", deco) do
      while l=conn.get_copy_data
        p l
      end
    end
    

    The output is like this (with the error message in German):

    [1111111]
    [1111111111]
    PG::NumericValueOutOfRange: FEHLER:  integer ist außerhalb des gültigen Bereichs
    
  2. Lars Kanis

    OK, yet another broken COPY implementation. Could you please open a ticket on sequel? It can be switched to PG::Connection#copy_data easily (at least when no en/decoder or type casting shall be used).

  3. Log in to comment