Will updating a row with identical values create entries for logical replication

Question

I am using Postgresql 14 and it is set up for logical replication. Trying to stream changes with debezium version 1.8. Actually, everything seems OK. Every insert, update, delete is streamed except the updates that update a value with the same. According to this great answer, this kind of update creates a WAL record but is it actually tried to replicate over publication?

Also need to mention that I asked the same question in debezium chat but they asked me back these two questions :

1. The change is propagated to the replication slot
2. The change is propagated into publication

I would be happy if you can provide ways to validate those two.

Answer 1

Yes. Every update will be transferred, even with the same value.

The pg_recvlogical (builtin program) can be used to prove behavior:

pg_recvlogical 
    --start 
    -f /tmp/recvlog 
    --slot=logical1 
    --create-slot 
    -d postgres 
    --plugin=pgoutput 
    --if-not-exists 
    --option='proto_version=2' 
    --option='publication_names=am_pub' 
    --option='binary=false'

(multiline formatting for a better looking response)

Repeated invocation of the query update am set k = 'hi' where n = 2; with the same arguments will write the passed data to /tmp/recvlog, for example:

[email protected]
CQNQN0z����

Note that publishing uses the binary data protocol, and pg_recvlogical does not decode specific messages. But it's enough to check if data is being sent through the publication on updates of identical values.