Re: [HACKERS] Logical Replication - test_decoding - unchanged-toast-datum

[Abhinav Singh]

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 Taveira  wrote:

> 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

[Euler Taveira]

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

[Euler Taveira]

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

[HACKERS] Logical Replication - test_decoding - unchanged-toast-datum

[Abhinav Singh]

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.