Search Postgresql Archives

Logical Replication - test_decoding - unchanged-toast-datum

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@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.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux