Thread: BUG #13865: PQresultStatus returns PGRES_COPY_OUT instead of PGRES_FATAL_ERROR for certain bad COPY statement

The following bug has been logged on the website:

Bug reference:      13865
Logged by:          Septian Hari
Email address:      septian.hari@gmail.com
PostgreSQL version: 9.4.5
Operating system:   ubuntu 14.04
Description:

We notice that for certain bad COPY statement, PQresultStatus will return
PGRES_COPY_OUT instead of PGRES_FATAL_ERROR, and subsequently hide the error
when using ruby pg gem.

Steps to reproduce:

    $ psql postgres
    psql (9.5.0, server 9.4.5)
    Type "help" for help.

    postgres=# create table xxx (properties json);
    CREATE TABLE
    postgres=# insert into xxx values ('{"a": "1\u000023"}');
    INSERT 0 1
    postgres=# select properties->'a' from xxx;
    ERROR:  unsupported Unicode escape sequence
    DETAIL:  \u0000 cannot be converted to text.
    CONTEXT:  JSON data, line 1: {"a":...

    $ pry
    [1] pry(main)> require 'pg'
    => true
    [2] pry(main)> conn = PG.connect(dbname: 'postgres')
    => #<PG::Connection:0x007fbac577ee08>
    [3] pry(main)> conn.exec "copy (select properties->'a' from xxx) to
stdout"
    => #<PG::Result:0x007fbac5855cf0 status=PGRES_COPY_OUT ntuples=0
nfields=1 cmd_tuples=0>
    [4] pry(main)> conn.get_copy_data
    => nil

    $ PSYCOPG_DEBUG=1 ipython
    ...
    In [5]: cur.copy_expert("COPY (SELECT properties->'a' FROM xxx) TO
STDOUT", sys.stdout)
    [4480] curs_execute: pg connection at 0x2eb9bb0 OK
    [4480] pq_begin_locked: pgconn = 0x2eb9bb0, autocommit = 0, status = 1
    [4480] pq_execute_command_locked: pgconn = 0x2eb9bb0, query = BEGIN
    [4480] pq_execute: executing SYNC query: pgconn = 0x2eb9bb0
    [4480]     COPY (SELECT properties->'a' FROM xxx) TO STDOUT
    [4480] pq_execute: entering synchronous DBAPI compatibility mode
    [4480] pq_fetch: pgstatus = PGRES_COPY_OUT
    [4480] pq_fetch: data from a COPY TO (no tuples)
    [4480] psycopg_is_text_file: importing io.TextIOBase
    [4480] _read_rowcount: PQcmdTuples =
    [4480] pq_raise: PQresultErrorMessage: err=ERROR:  unsupported Unicode
escape sequence
    DETAIL:  \u0000 cannot be converted to text.
    CONTEXT:  JSON data, line 1: {"a":...

    [4480] pq_raise: err2=unsupported Unicode escape sequence
    DETAIL:  \u0000 cannot be converted to text.
    CONTEXT:  JSON data, line 1: {"a":...

    [4480] pq_fetch: fetching done; check for critical errors

---------------------------------------------------------------------------
    DataError                                 Traceback (most recent call
last)
    <ipython-input-5-65d68a7d89bd> in <module>()
    ----> 1 cur.copy_expert("COPY (SELECT properties->'a' FROM xxx) TO
STDOUT", sys.stdout)

    DataError: unsupported Unicode escape sequence
    DETAIL:  \u0000 cannot be converted to text.
    CONTEXT:  JSON data, line 1: {"a":..

septian.hari@gmail.com writes:
> We notice that for certain bad COPY statement, PQresultStatus will return
> PGRES_COPY_OUT instead of PGRES_FATAL_ERROR, and subsequently hide the error
> when using ruby pg gem.

The example seems perfectly fine to me: the error is detected at runtime,
so the COPY operation will start first, and that's when PGRES_COPY_OUT is
reported.  I'm not sure what you mean by "hide the error" --- the error
certainly seems to be getting reported in the trace you show.

If there is any problem here, you probably need to discuss it with the
author of the Ruby pg gem.  This list is mostly for reporting errors in
the core server.

            regards, tom lane