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
