we shouldn't store bytes in text columns in the database

[richvdh]

in certain places (eg, the data column of pushers), we store bytes values in a text column. This is a dicey thing to do: it doesn't work at all for data which isn't printable ascii, and even for that, it relies on bytea_output being set (see #4473)

[richvdh]

A note largely for my own reference since I end up figuring this out from first principles each time I look at it:

A naive reading of the documentation would have you believe that bytea_output only affects the way that bytea values are returned from the database to a postgres client app; however it also affects the way that bytea values are converted to text.

Thus:

synapse=# create temporary table bytea_test (escape text, data text);
CREATE TABLE
synapse=# insert into bytea_test values('hex', 'test'::bytea);
INSERT 0 1
synapse=# set bytea_output='escape';
SET
synapse=# insert into bytea_test values('escape', 'test'::bytea);
INSERT 0 1

synapse=# select * from bytea_test;
 escape |    data    
--------+------------
 hex    | \x74657374
 escape | test

(once the conversion to text has happened the value of bytea_output is immaterial: that is a real \ and a real x in the column itself.)

[richvdh]

AFAICT the only thing that can cause \x-encoded values in text columns is an incorrect bytea_output setting when the value was written. Non-ascii data results in \ooo octal encoding:

synapse=# insert into bytea_test values('hex2', E'\\x6162E282EC'::bytea);
INSERT 0 1
synapse=# select * from bytea_test;
 escape |      data      
--------+----------------
 hex2   | ab\342\202\354

[richvdh]

for the record, afaik pushers is the only table thus afflicted (at least since #6185 landed), though that doesn't really explain #4921.