Thread: BUG #18838: Missing characters in replication slot when bytea_output is set to "escape"

The following bug has been logged on the website:

Bug reference:      18838
Logged by:          Pratik Chavan
Email address:      pratik.chavan@veritas.com
PostgreSQL version: 17.4
Operating system:   Red Hat Enterprise Linux 8.6 (Ootpa)
Description:

When bytea_output parameter is set to the value 'escape', and you do DML
operations such as insert/update on a table where Replication slot is
created and capturing these queries, Values are missing starting 2
characters in the replication slot.
Providing below the steps and outputs.
Steps to reproduce the issue :-
1] Enable logical replication i.e. wal_level = logical
2] Create Replication slot for a DB
3] Set parameter 'bytea' to value 'escape'
4] Do few Insert operations on a table within this DB
5] Check replication slot using "pg_logical_slot_peek_changes"
6] Starting 2 characters can be seen missing in replication slot

## Queries and their outputs :-
db2=> set bytea_output = 'escape';
SET
db2=> show bytea_output;
 bytea_output
--------------
 escape
(1 row)
db2=> select * from binary_data;
 id |    data
----+-------------
  4 | Hello World
  5 | Hello World
  6 | Hello World
(3 rows)

db2=> INSERT INTO binary_data (data) VALUES (decode('DEADBEEF', 'hex'));
INSERT 0 1
db2=> INSERT INTO binary_data (data) VALUES
(decode('48656C6C6F20576F726C64', 'hex'));
INSERT 0 1
db2=> select * from binary_data;
 id |       data
----+------------------
  4 | Hello World
  5 | Hello World
  6 | Hello World
  7 | \336\255\276\357
  8 | Hello World
(5 rows)
*****Here, posting only last 2 outputs below query as the output is large
db2=> SELECT * from pg_logical_slot_peek_changes('hello2', NULL,
NULL,'format-version','2');

 3/310      | 1142 | {"action":"B"}
 3/378      | 1142 |

{"action":"I","schema":"public","table":"binary_data","columns":[{"name":"id","type":"integer","value":7},{"name":"data","type":"by
tea","value":"36\\255\\276\\357"}]}
 3/4F8      | 1142 | {"action":"C"}
 3/4F8      | 1143 | {"action":"B"}
 3/4F8      | 1143 |

{"action":"I","schema":"public","table":"binary_data","columns":[{"name":"id","type":"integer","value":8},{"name":"data","type":"by
tea","value":"llo World"}]}
 3/5B0      | 1143 | {"action":"C"}
(34 rows)

===> Here you can see inserted values are "\336\255\276\357" and "Hello
World", But in the Replication slot, only "36\255\276\357" and "llo World"
values are seen, missing first 2 characters.


Please, Let us know if this bug is being fixed or is there any other
alternative.
Thank you!
- Pratik

PG Bug reporting form <noreply@postgresql.org> writes:
> When bytea_output parameter is set to the value 'escape', and you do DML
> operations such as insert/update on a table where Replication slot is
> created and capturing these queries, Values are missing starting 2
> characters in the replication slot.

You have omitted a lot of critical details in this report.  For
starters, what output plugin are you using?  It does not seem
to be the one we supply (pgoutput), because that does not
recognize any such option as 'format-version'.

For the moment I plan to write this off as "somebody else's bug".
If you think it is a core-Postgres bug, please supply a complete
script for reproducing it.

            regards, tom lane