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":..
