Обсуждение: [GENERAL] Logical Replication - test_decoding - unchanged-toast-datum
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.
On 26 September 2017 at 05:01, Abhinav Singh <abhinav.thegame@gmail.com> wrote:
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')
...
3. So now I started my replication.
This is one of the MANY reasons test_decoding isn't suitable as the base for a replication solution. It has "test" in its name for a reason.
Your replication model, whatever it is, is broken, since it's not handling special cases like unchanged TOASTed values in UPDATEs. This is a bug in your replication tool.
On 2017-09-28 08:19:08 +0800, Craig Ringer wrote: > This is one of the MANY reasons test_decoding isn't suitable as the base > for a replication solution. It has "test" in its name for a reason. FWIW, I don't see why the unchanged toast stuff is that. It's clearly discernible from actual datums, so ... I agree that test_decoding isn't a great base of a replication tool, but I don't think it's completely unsuitable, and I also think that ship has sailed. Greetings, Andres Freund -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general