On Fri, Jul 30, 2021 at 10:21 AM Amit Kapila <amit.kapil...@gmail.com> wrote: > > This problem seems to be from the time Logical > Replication has been introduced, so adding others (who are generally > involved in this area) to see what they think about this bug? I think > people might not be using toasted columns for Replica Identity due to > which this problem has been reported yet but I feel this is quite a > fundamental issue and we should do something about this. > > Let me summarize the problem for the ease of others. > > The logical replica can go out of sync for UPDATES when there is a > toast column as part of REPLICA IDENTITY. In such cases, updates are > not replicated if the key column doesn't change because we don't log > the actual key value for the unchanged toast key. It is neither logged > as part of old_key_tuple nor for new tuple due to which we are not > able to find the tuple to be updated on the subscriber-side and the > update is ignored on the subscriber-side. We log this in DEBUG1 mode > but I don't think the user can do anything about this and the replica > will go out-of-sync. This works when the replica identity column value > is not toasted because then it will be part of the new tuple and we > use that to fetch the tuple on the subscriber. >
It seems to me this problem exists from the time we introduced wal_level = logical in the commit e55704d8b2 [1], or another possibility is that logical replication commit didn't consider something to make it work. Andres, Robert, Petr, can you guys please comment because otherwise, we might miss something here. [1] - commit e55704d8b2fe522fbc9435acbb5bc59033478bd5 Author: Robert Haas <rh...@postgresql.org> Date: Tue Dec 10 18:33:45 2013 -0500 Add new wal_level, logical, sufficient for logical decoding. When wal_level=logical, we'll log columns from the old tuple as configured by the REPLICA IDENTITY facility added in commit 07cacba983ef79be4a84fcd0e0ca3b5fcb85dd65. This makes it possible a properly-configured logical replication solution to correctly follow table updates even if they change the chosen key columns, or, with REPLICA IDENTITY FULL, even if the table has no key at all. Note that updates which do not modify the replica identity column won't log anything extra, making the choice of a good key (i.e. one that will rarely be changed) important to performance when wal_level=logical is configured. .. Andres Freund, reviewed in various versions by myself, Heikki Linnakangas, KONDO Mitsumasa, and many others. -- With Regards, Amit Kapila.
