Re: [HACKERS] Logical Replication - test_decoding - unchanged-toast-datum
Hello Euler, Thank you very for your prompt response. I am really very sorry that I didn't mention about my exact question. So as you can see from my previous email, in the replication slot I see the value '*unchanged-toast-datum*' for the column of dataytpe '*character varying*'. Based on this, when I query my target PostgreSQL instance after the *UPDATE *statement is run, the value in this column gets changed to ' *unchanged-toast-datum*'. The steps that I gave is a simple reproduction and I would request you to please give that a try as well. Hence, my question is how can workaroud this issue or is there a resolution for this problem. Kindly review and please share your comments. Thanks and Regards, Abhinav Singh On Tue, Sep 26, 2017 at 6:25 PM, Euler Taveirawrote: > 2017-09-26 1:11 GMT-03:00 Abhinav Singh : > > 5. On the target, when I do a select * and see that the column with > > character varying() datatype has changed to 'unchanged-toast-datum'. > > > The column "is_toast" didn't change its value to > "unchanged-toast-datum". It is just a test_decoding convention that > means that the value is stored in a TOAST table and it was not > changed. test_decoding doesn't show TOAST values to avoid performance > problems and to be brief. Try a SELECT in the table and you will see > that the value is already there. > > You didn't write an explicit question but I believe it was your doubt, > didn't it? > > > -- >Euler Taveira Timbira - > http://www.timbira.com.br/ >PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento >
Re: [HACKERS] Logical Replication - test_decoding - unchanged-toast-datum
2017-09-26 21:46 GMT-03:00 Abhinav Singh: > Based on this, when I query my target PostgreSQL instance after the UPDATE > statement is run, the value in this column gets changed to > 'unchanged-toast-datum'. The steps that I gave is a simple reproduction and > I would request you to please give that a try as well. > You didn't understand what i said. If RDS shows a different output maybe they hack the test_decoding plugin. euler@vostro:~$ psql -A psql (9.6.2) Digite "help" para ajuda. euler=# SELECT xlog_position FROM pg_create_logical_replication_slot('cjkimqvfuvixqyjd_00016389_e6f7c975_a311_4067_bcf1_a6accb57ab37', 'test_decoding'); xlog_position 1/7DF15D68 (1 registro) euler=# CREATE TABLE public.toast_test1 (id SERIAL PRIMARY KEY NOT NULL, is_not_toast INT, is_toast VARCHAR(32767)); CREATE TABLE euler=# CREATE UNIQUE INDEX toast_test_id_uindex1 ON public.toast_test1 (id); CREATE INDEX euler=# INSERT INTO public.toast_test1 (is_not_toast, is_toast) VALUES(0, (SELECT string_agg(series::text, ',') FROM generate_series(1, 1000) AS series)); INSERT 0 1 euler=# UPDATE public.toast_test1 SET is_not_toast = 1; UPDATE 1 euler=# SELECT * FROM pg_logical_slot_get_changes('cjkimqvfuvixqyjd_00016389_e6f7c975_a311_4067_bcf1_a6accb57ab37', NULL, NULL); location|xid|data 1/7DF15DD0|295590|BEGIN 295590 1/7DF20910|295590|COMMIT 295590 1/7DF20940|295591|BEGIN 295591 1/7DF21180|295591|COMMIT 295591 1/7DF211B8|295592|BEGIN 295592 1/7DF22330|295592|table public.toast_test1: INSERT: id[integer]:1 is_not_toast[integer]:0 is_toast[character
Re: [HACKERS] Logical Replication - test_decoding - unchanged-toast-datum
2017-09-26 1:11 GMT-03:00 Abhinav Singh: > 5. On the target, when I do a select * and see that the column with > character varying() datatype has changed to 'unchanged-toast-datum'. > The column "is_toast" didn't change its value to "unchanged-toast-datum". It is just a test_decoding convention that means that the value is stored in a TOAST table and it was not changed. test_decoding doesn't show TOAST values to avoid performance problems and to be brief. Try a SELECT in the table and you will see that the value is already there. You didn't write an explicit question but I believe it was your doubt, didn't it? -- Euler Taveira Timbira - http://www.timbira.com.br/ PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers