On 14/12/16 15:35, Peter Eisentraut wrote: > On 12/12/16 8:28 PM, Petr Jelinek wrote: >> On 13/12/16 02:00, Andres Freund wrote: >>> On 2016-12-13 01:57:01 +0100, Petr Jelinek wrote: >>>> I mentioned that as possible solution upthread, I am only worried that >>>> the failure scenario is basically infinite loop. >>> >>> I don't see the problem with that. If you're really concerned you can >>> set a statement timeout. >>> >> >> Okay in case we decide it's the right way to go attached patch does >> that. I also added some more tests based on your feedback while I am at it. > > This looks mostly reasonable to me, but the location and xid output from > pg_logical_slot_get_changes() is not portable/repeatable, so it should > be omitted from the output. >
Sigh, yes you are right. -- Petr Jelinek http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
>From d2c80460d082824e11868cb60b807419da44df63 Mon Sep 17 00:00:00 2001 From: Petr Jelinek <[email protected]> Date: Thu, 15 Dec 2016 09:42:37 +0100 Subject: [PATCH 1/2] Various temporary slots test improvements --- contrib/test_decoding/expected/slot.out | 55 ++++++++++++++++++++++++++------- contrib/test_decoding/sql/slot.sql | 32 ++++++++++++++++--- 2 files changed, 71 insertions(+), 16 deletions(-) diff --git a/contrib/test_decoding/expected/slot.out b/contrib/test_decoding/expected/slot.out index c9171ff..6dee143 100644 --- a/contrib/test_decoding/expected/slot.out +++ b/contrib/test_decoding/expected/slot.out @@ -22,19 +22,27 @@ SELECT 'init' FROM pg_create_logical_replication_slot('regression_slot_p', 'test init (1 row) +SELECT 'init' FROM pg_create_logical_replication_slot('regression_slot_t2', 'test_decoding', true); + ?column? +---------- + init +(1 row) + -- here we want to start a new session and wait till old one is gone select pg_backend_pid() as oldpid \gset \c - do 'declare c int = 0; begin - while (select count(*) from pg_stat_activity where pid = ' + while (select count(*) from pg_replication_slots where active_pid = ' :'oldpid' - ') > 0 loop c := c + 1; perform pg_stat_clear_snapshot(); end loop; + ') > 0 loop c := c + 1; perform pg_sleep(0.01); end loop; raise log ''slot test looped % times'', c; end'; --- should fail because the temporary slot was dropped automatically +-- should fail because the temporary slots were dropped automatically SELECT pg_drop_replication_slot('regression_slot_t'); ERROR: replication slot "regression_slot_t" does not exist +SELECT pg_drop_replication_slot('regression_slot_t2'); +ERROR: replication slot "regression_slot_t2" does not exist -- permanent slot has survived SELECT pg_drop_replication_slot('regression_slot_p'); pg_drop_replication_slot @@ -49,19 +57,44 @@ SELECT 'init' FROM pg_create_logical_replication_slot('regression_slot1', 'test_ init (1 row) +CREATE TABLE replication_example(id SERIAL PRIMARY KEY, somedata int, text varchar(120)); +BEGIN; +INSERT INTO replication_example(somedata, text) VALUES (1, 1); +INSERT INTO replication_example(somedata, text) VALUES (1, 2); +COMMIT; SELECT 'init' FROM pg_create_logical_replication_slot('regression_slot2', 'test_decoding', true); ?column? ---------- init (1 row) -SELECT * FROM pg_logical_slot_get_changes('regression_slot1', NULL, NULL); - location | xid | data -----------+-----+------ -(0 rows) +INSERT INTO replication_example(somedata, text) VALUES (1, 3); +SELECT data FROM pg_logical_slot_get_changes('regression_slot1', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1'); + data +--------------------------------------------------------------------------------------------------------- + BEGIN + table public.replication_example: INSERT: id[integer]:1 somedata[integer]:1 text[character varying]:'1' + table public.replication_example: INSERT: id[integer]:2 somedata[integer]:1 text[character varying]:'2' + COMMIT + BEGIN + table public.replication_example: INSERT: id[integer]:3 somedata[integer]:1 text[character varying]:'3' + COMMIT +(7 rows) -SELECT * FROM pg_logical_slot_get_changes('regression_slot2', NULL, NULL); - location | xid | data -----------+-----+------ -(0 rows) +SELECT data FROM pg_logical_slot_get_changes('regression_slot2', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1'); + data +--------------------------------------------------------------------------------------------------------- + BEGIN + table public.replication_example: INSERT: id[integer]:3 somedata[integer]:1 text[character varying]:'3' + COMMIT +(3 rows) +DROP TABLE replication_example; +-- error +SELECT 'init' FROM pg_create_logical_replication_slot('regression_slot1', 'test_decoding', true); +ERROR: replication slot "regression_slot1" already exists +-- both should error as they should be dropped on error +SELECT pg_drop_replication_slot('regression_slot1'); +ERROR: replication slot "regression_slot1" does not exist +SELECT pg_drop_replication_slot('regression_slot2'); +ERROR: replication slot "regression_slot2" does not exist diff --git a/contrib/test_decoding/sql/slot.sql b/contrib/test_decoding/sql/slot.sql index 5d6d97a..7ca83fe 100644 --- a/contrib/test_decoding/sql/slot.sql +++ b/contrib/test_decoding/sql/slot.sql @@ -4,25 +4,47 @@ SELECT 'init' FROM pg_create_logical_replication_slot('regression_slot_t', 'test SELECT pg_drop_replication_slot('regression_slot_p'); SELECT 'init' FROM pg_create_logical_replication_slot('regression_slot_p', 'test_decoding', false); +SELECT 'init' FROM pg_create_logical_replication_slot('regression_slot_t2', 'test_decoding', true); + -- here we want to start a new session and wait till old one is gone select pg_backend_pid() as oldpid \gset \c - do 'declare c int = 0; begin - while (select count(*) from pg_stat_activity where pid = ' + while (select count(*) from pg_replication_slots where active_pid = ' :'oldpid' - ') > 0 loop c := c + 1; perform pg_stat_clear_snapshot(); end loop; + ') > 0 loop c := c + 1; perform pg_sleep(0.01); end loop; raise log ''slot test looped % times'', c; end'; --- should fail because the temporary slot was dropped automatically +-- should fail because the temporary slots were dropped automatically SELECT pg_drop_replication_slot('regression_slot_t'); +SELECT pg_drop_replication_slot('regression_slot_t2'); -- permanent slot has survived SELECT pg_drop_replication_slot('regression_slot_p'); -- test switching between slots in a session SELECT 'init' FROM pg_create_logical_replication_slot('regression_slot1', 'test_decoding', true); + +CREATE TABLE replication_example(id SERIAL PRIMARY KEY, somedata int, text varchar(120)); +BEGIN; +INSERT INTO replication_example(somedata, text) VALUES (1, 1); +INSERT INTO replication_example(somedata, text) VALUES (1, 2); +COMMIT; + SELECT 'init' FROM pg_create_logical_replication_slot('regression_slot2', 'test_decoding', true); -SELECT * FROM pg_logical_slot_get_changes('regression_slot1', NULL, NULL); -SELECT * FROM pg_logical_slot_get_changes('regression_slot2', NULL, NULL); + +INSERT INTO replication_example(somedata, text) VALUES (1, 3); + +SELECT data FROM pg_logical_slot_get_changes('regression_slot1', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1'); +SELECT data FROM pg_logical_slot_get_changes('regression_slot2', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1'); + +DROP TABLE replication_example; + +-- error +SELECT 'init' FROM pg_create_logical_replication_slot('regression_slot1', 'test_decoding', true); + +-- both should error as they should be dropped on error +SELECT pg_drop_replication_slot('regression_slot1'); +SELECT pg_drop_replication_slot('regression_slot2'); -- 2.7.4
-- Sent via pgsql-committers mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-committers
