Logical Replication - test_decoding - unchanged-toast-datum

From: Abhinav Singh <abhinav(dot)thegame(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Logical Replication - test_decoding - unchanged-toast-datum
Date: 2017-09-25 21:01:51
Message-ID: CALm8xzhWcz1BKv6K_+kd4kj1Xxj10zTdNoGc9SVJsOuUHM=P-g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,

I am currently using PostgreSQL Community version 9.4.9 and then using this
instance, I am doing logical replication(using replication slots). I have
created the replication slots using the following query:

SELECT xlog_position FROM
pg_create_logical_replication_slot('cjkimqvfuvixqyjd_00016389_e6f7c975_a311_4067_bcf1_a6accb57ab37',
'test_decoding')

So the issue that I am facing is because of the updates that are being done
to my table. I was able to reproduce the same issue again using the
following sample:
_____________________________________________________________________________________________________________________________________
1. Table on the source(which is RDS PostgreSQL):

CREATE TABLE public.toast_test1
(
id SERIAL PRIMARY KEY NOT NULL,
is_not_toast INT,
is_toast VARCHAR(32767)
);
CREATE UNIQUE INDEX toast_test_id_uindex1 ON public.toast_test1 (id);

2. Insert some values:

INSERT INTO public.toast_test1
(is_not_toast, is_toast) VALUES
(0, (SELECT string_agg(series::text, ',')
FROM generate_series(1, 1000) AS series));

So basically, every time you execute the above query, a new row will be
inserted. So execute the same for 4-5 times.

3. So now I started my replication.

4. If for example, I am doing an update using the below mentioned query on
my source instance:

UPDATE public.toast_test SET is_not_toast = 1;

5. On the target, when I do a select * and see that the column with
character varying() datatype has changed to 'unchanged-toast-datum'.

6. So on further checking the replication slot at the time, when I issued
an update, I can see this:

postgres2(at)t1=> SELECT * FROM
pg_logical_slot_get_changes('cjkimqvfuvixqyjd_00016389_e6f7c975_a311_4067_bcf1_a6accb57ab37',
NULL, NULL);
location | xid |
data
-------------+-------+---------------------------------------------------------------------------------------------------------------------------
3D/95003D58 | 17974 | BEGIN 17974
3D/950049D0 | 17974 | table public.toast_test1: UPDATE: id[integer]:1
is_not_toast[integer]:1 is_toast[character varying]:unchanged-toast-datum
3D/95004A78 | 17974 | COMMIT 17974
(3 rows)

-----------------------------------------------------------------------------------------------------------

Even after setting the REPLICA IDENTITY to FULL for this table did not
help.
_____________________________________________________________________________________________________________________________________

Kindly review and please share your comments on this matter.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Lukasz Kaminski 2017-09-26 07:21:40 Partition check constraint is ignored if parameter is passed from SQL function
Previous Message Tomas Vondra 2017-09-25 17:11:24 Re: shared_buffers smaller than max_wal_size