BUG #15295: invalid byte sequence for encoding "UTF8": 0x9e - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      15295
Logged by:          Tom Tignor
Email address:      tptignor@yahoo.com
PostgreSQL version: 9.5.10
Operating system:   Ubuntu 16.04.2 LTS
Description:

We are running a Slony-I v.2.2.6 cluster with five postgresql v9.5.10 DBs.
In our configuration, four "replica" hosts replicate the same two table sets
from a single "primary" host, with each host running the same postgresql and
slony1 software. Slony-I replicas (subscribers) regularly query changelog
data from the primary (provider) and apply to the local DB using COPY...TO
STDOUT and COPY...FROM STDIN operations.

On/Around 2018-07-11 22:00:00 UTC, COPY FROM STDIN operations on all
replicas began failing with errors of the form: 'ERROR:  invalid byte
sequence for encoding "UTF8": 0x9e'. The slon daemon on each replica
recurringly produced these failures for about an hour before they
disappeared and replication continued. Other replication problems which are
likely related occurred afterwards, but we do not need to pursue those
here.

On 2018-07-18, an operator ran our custom utility to rebuild a portion of
the replica DB from scratch. This utility uses different COPY...TO STDOUT
operations paired with COPY...FROM <file> operations to load data. Our
utility failed in a COPY FROM <file> operation with the same error: 'ERROR:
invalid byte sequence for encoding "UTF8": 0x9e'. Multiple reruns of the
same utility yesterday (2018-07-24) completed successfully and did not
reproduce the error. We have been unable to locate any 0x9e bytes in our
data. However, we still have the 11MB data file produced by COPY TO STDOUT
associated with this error and it still reproduces the error when run with
COPY FROM <file>. We can provide the data file if needed.

Below are samples of the Slony-I and custom utility COPY TO STDOUT
operations associated with these errors. Please let us know what more info
we can provide, and any thoughts on detecting and mitigating future
problems. Thanks in advance.

---- Slony-I ----
/a/logs/run_slony1.log.old/run_slony1.log.20f0t-8pxsb-oa22v-7fd1p-m3312.9.1531297028.1531375315.gz:2018-07-11
22:00:12 UTC [13590] ERROR  remoteWorkerThread_1
_1: error at end of COPY IN: ERROR:  invalid byte sequence for encoding
"UTF8": 0x9e
/a/logs/run_slony1.log.old/run_slony1.log.20f0t-8pxsb-oa22v-7fd1p-m3312.9.1531297028.1531375315.gz:2018-07-11
22:00:12 UTC [13590] ERROR  remoteWorkerThread_1
_1: failed SYNC's log selection query was 'COPY ( select log_origin,
log_txid, NULL::integer, log_actionseq, NULL::text, NULL::text, log_cmdtype,
NULL::intege
r, log_cmdargs from "_ams_cluster".sl_log_script where log_origin = 1 and
log_txid >= "pg_catalog".txid_snapshot_xmax('88266665:88266665:') and
log_txid < '88
267025' and "pg_catalog".txid_visible_in_snapshot(log_txid,
'88267025:88267025:') union all select log_origin, log_txid, NULL::integer,
log_actionseq, NULL::t
ext, NULL::text, log_cmdtype, NULL::integer, log_cmdargs from
"_ams_cluster".sl_log_script where log_origin = 1 and log_txid in (select *
from "pg_catalog".txid_snapshot_xip('88266665:88266665:') except select *
from "pg_catalog".txid_snapshot_xip('88267025:88267025:') ) union all select
log_origin, log_txid, log_tableid, log_actionseq, log_tablenspname,
log_tablerelname, log_cmdtype, log_cmdupdncols, log_cmdargs from
"_ams_cluster".sl_log_1 where log_origin = 1 and log_tableid in
(14,15,16,17,18) and log_txid >= '88266665' and log_txid < '88267025' and
"pg_catalog".txid_visible_in_snapshot(log_txid, '88267025:88267025:') union
all select log_origin, log_txid, log_tableid, log_actionseq,
log_tablenspname, log_tablerelname, log_cmdtype, log_cmdupdncols,
log_cmdargs from "_ams_cluster".sl_log_1 where log_origin = 1 and
log_tableid in (14,15,16,17,18) and log_txid in (select * from
"pg_catalog".txid_snapshot_xip('88266665:88266665:') except select * from
"pg_catalog".txid_snapshot_xip('88267025:88267025:') ) union all select
log_origin, log_txid, log_tableid, log_actionseq, log_tablenspname,
log_tablerelname, log_cmdtype, log_cmdupdncols, log_cmdargs from
"_ams_cluster".sl_log_2 where log_origin = 1 and log_tableid in
(14,15,16,17,18) and log_txid >= '88266665' and log_txid < '88267025' and
"pg_catalog".txid_visible_in_snapshot(log_txid, '88267025:88267025:') union
all select log_origin, log_txid, log_tableid, log_actionseq,
log_tablenspname, log_tablerelname, log_cmdtype, log_cmdupdncols,
log_cmdargs from "_ams_cluster".sl_log_2 where log_origin = 1 and
log_tableid in (14,15,16,17,18) and log_txid in (select * from
"pg_catalog".txid_snapshot_xip('88266665:88266665:') except select * from
"pg_catalog".txid_snapshot_xip('88267025:88267025:') ) union all select
log_origin, log_txid, log_tableid, log_actionseq, log_tablenspname,
log_tablerelname, log_cmdtype, log_cmdupdncols, log_cmdargs from
"_ams_cluster".sl_log_1 where log_origin = 1 and log_tableid in
(1,2,3,5,6,7,8,9,10,11,12,13,19,20,21) and log_txid >= '88266665' and
log_txid < '88267025' and "pg_catalog".txid_visible_in_snapshot(log_txid,
'88267025:88267025:') union all select log_origin, log_txid, log_tableid,
log_actionseq, log_tablenspname, log_tablerelname, log_cmdtype,
log_cmdupdncols, log_cmdargs from "_ams_cluster".sl_log_1 where log_origin =
1 and log_tableid in (1,2,3,5,6,7,8,9,10,11,12,13,19,20,21) and log_txid in
(select * from "pg_catalog".txid_snapshot_xip('88266665:88266665:') except
select * from "pg_catalog".txid_snapshot_xip('88267025:88267025:') ) union
all select log_origin, log_txid, log_tableid, log_actionseq,
log_tablenspname, log_tablerelname, log_cmdtype, log_cmdupdncols,
log_cmdargs from "_ams_cluster".sl_log_2 where log_origin = 1 and
log_tableid in (1,2,3,5,6,7,8,9,10,11,12,13,19,20,21) and log_txid >=
'88266665' and log_txid < '88267025' and
"pg_catalog".txid_visible_in_snapshot(log_txid, '88267025:88267025:') union
all select log_origin, log_txid, log_tableid, log_actionseq,
log_tablenspname, log_tablerelname, log_cmdtype, log_cmdupdncols,
log_cmdargs from "_ams_cluster".sl_log_2 where log_origin = 1 and
log_tableid in (1,2,3,5,6,7,8,9,10,11,12,13,19,20,21) and log_txid in
(select * from "pg_catalog".txid_snapshot_xip('88266665:88266665:') except
select * from "pg_catalog".txid_snapshot_xip('88267025:88267025:') ) order
by log_actionseq) TO STDOUT'

---- custom utility ----
COPY (WITH instanceids AS (SELECT alert_instance_id FROM
ams.alert_instance_hist WHERE stop_active_date >= '2018-07-11 18:07:45' AND
stop_active_date < '2018-07-11 22:07:45') SELECT * FROM
ams.alert_attribute_hist    WHERE alert_instance_id IN (SELECT * FROM
instanceids)) TO STDOUT WITH (FORMAT binary)