Re: [BUG]Update Toast data failure in logical replication - Mailing list pgsql-hackers

On Sun, Feb 6, 2022 at 5:04 AM Andres Freund <andres@anarazel.de> wrote:
>
> On 2022-02-04 17:45:36 +0530, Amit Kapila wrote:
> > diff --git a/contrib/test_decoding/expected/toast.out b/contrib/test_decoding/expected/toast.out
> > index cd03e9d..a757e7d 100644
> > --- a/contrib/test_decoding/expected/toast.out
> > +++ b/contrib/test_decoding/expected/toast.out
> > @@ -77,7 +77,7 @@ SELECT substr(data, 1, 200) FROM pg_logical_slot_get_changes('regression_slot',
> >   table public.toasted_key: INSERT: id[integer]:1
toasted_key[text]:'1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123
> >   COMMIT
> >   BEGIN
> > - table public.toasted_key: UPDATE: id[integer]:1 toasted_key[text]:unchanged-toast-datum
toasted_col1[text]:unchanged-toast-datumtoasted_col2[text]:'987654321098765432109876543210987654321098765432109 
> > + table public.toasted_key: UPDATE: old-key:
toasted_key[text]:'123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678
>
> Hm. This looks weird. What happened to the the change to toasted_col2 that was
> in the "removed" line?
>
> This corresponds to the following statement I think:
> -- test update of a toasted key without changing it
> UPDATE toasted_key SET toasted_col2 = toasted_col1;
> which previously was inserted as:
> INSERT INTO toasted_key(toasted_key, toasted_col1) VALUES(repeat('1234567890', 200), repeat('9876543210', 200));
>
> so toasted_col2 should have changed from NULL to repeat('9876543210', 200)
>

Right, and it is getting changed. We are just printing the first 200
characters (by using SQL [1]) from the decoded tuple so what is shown
in the results is the initial 200 bytes. The complete decoded data
after the patch is as follows:

 table public.toasted_key: UPDATE: old-key:

toasted_key[text]:'12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890'
new-tuple: id[integer]:2 toasted_key[text]:unchanged-toast-datum
toasted_col1[text]:unchanged-toast-datum

toasted_col2[text]:'98765432109876543210987654321098765432109876543210987654321098765432109876543210987654321098765432109876543210987654321098765432109876543210987654321098765432109876543210987654321098765432109876543210987654321098765432109876543210987654321098765432109876543210987654321098765432109876543210987654321098765432109876543210987654321098765432109876543210987654321098765432109876543210987654321098765432109876543210987654321098765432109876543210987654321098765432109876543210987654321098765432109876543210987654321098765432109876543210987654321098765432109876543210987654321098765432109876543210987654321098765432109876543210987654321098765432109876543210987654321098765432109876543210987654321098765432109876543210987654321098765432109876543210987654321098765432109876543210987654321098765432109876543210987654321098765432109876543210987654321098765432109876543210987654321098765432109876543210987654321098765432109876543210987654321098765432109876543210987654321098765432109876543210987654321098765432109876543210987654321098765432109876543210987654321098765432109876543210987654321098765432109876543210987654321098765432109876543210987654321098765432109876543210987654321098765432109876543210987654321098765432109876543210987654321098765432109876543210987654321098765432109876543210987654321098765432109876543210987654321098765432109876543210987654321098765432109876543210987654321098765432109876543210987654321098765432109876543210987654321098765432109876543210987654321098765432109876543210987654321098765432109876543210987654321098765432109876543210987654321098765432109876543210987654321098765432109876543210987654321098765432109876543210987654321098765432109876543210987654321098765432109876543210987654321098765432109876543210987654321098765432109876543210987654321098765432109876543210987654321098765432109876543210987654321098765432109876543210987654321098765432109876543210987654321098765432109876543210987654321098765432109876543210987654321098765432109876543210987654321098765432109876543210'

So, in the result, the initial 200 bytes contain data of old-key which
is what we expect.

[1] - SELECT substr(data, 1, 200) FROM
pg_logical_slot_get_changes('regression_slot', NULL, NULL,
'include-xids', '0', 'skip-empty-xacts', '1');

--
With Regards,
Amit Kapila.