From: Petr Jelinek <petr.jelinek@2ndquadrant.com>
To: Andres Freund <andres@anarazel.de>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Peter Eisentraut <peter_e@gmx.net>
Cc: pgsql-committers@postgresql.org
Subject: Re: pgsql: Add support for temporary replication slots
Date: Tue, 13 Dec 2016 02:28:26 +0100
Message-ID: <a6b63bc4-ed38-2908-ef8d-1d96f2012d57@2ndquadrant.com> (raw)
In-Reply-To: <20161213010059.dn64rrb3u7zxnnwk@alap3.anarazel.de>
References: <27274.1481557380@sss.pgh.pa.us>
<27833.1481558144@sss.pgh.pa.us>
<62935e6f-4f1b-c433-e0fa-7f936a38b3e5@2ndquadrant.com>
<22907.1481571177@sss.pgh.pa.us>
<15713.1481585953@sss.pgh.pa.us>
<314cae39-2e7d-f5a3-d226-cfb80cf80481@2ndquadrant.com>
<18684.1481589637@sss.pgh.pa.us>
<48b2c017-695d-b5ac-2ff4-89b939b3e8f9@2ndquadrant.com>
<20161213004903.iito2pefdrrjgny5@alap3.anarazel.de>
<6886c56a-f6c8-3c44-53a1-dae6c24afb76@2ndquadrant.com>
<20161213010059.dn64rrb3u7zxnnwk@alap3.anarazel.de>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-committers>
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.
--
Petr Jelinek http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-committers mailing list (pgsql-committers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-committersAttachments:0001-Various-temporary-slots-test-improvements.patch (text/x-diff, 7.2K) download
From 93f6af70ec9bd2b37b5d4b67afda187f96cad502 Mon Sep 17 00:00:00 2001
From: Petr Jelinek <pjmodos@pjmodos.net>
Date: Tue, 13 Dec 2016 02:02:31 +0100
Subject: [PATCH] Various temporary slots test improvements
Fix the tests on slow machines (per buildfarm).
Add test for dropping on error. And also try to consume real changes
from temporary slots.
---
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.outindex c9171ff..7b1f3ad 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 * FROM pg_logical_slot_get_changes('regression_slot1', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1');+ location | xid | data +-----------+-----+---------------------------------------------------------------------------------------------------------+ 0/2846FE8 | 993 | BEGIN+ 0/2846FE8 | 993 | table public.replication_example: INSERT: id[integer]:1 somedata[integer]:1 text[character varying]:'1'+ 0/28470C0 | 993 | table public.replication_example: INSERT: id[integer]:2 somedata[integer]:1 text[character varying]:'2'+ 0/2847178 | 993 | COMMIT+ 0/28471B0 | 994 | BEGIN+ 0/28471B0 | 994 | table public.replication_example: INSERT: id[integer]:3 somedata[integer]:1 text[character varying]:'3'+ 0/2847268 | 994 | COMMIT+(7 rows)-SELECT * FROM pg_logical_slot_get_changes('regression_slot2', NULL, NULL);- location | xid | data -----------+-----+-------(0 rows)+SELECT * FROM pg_logical_slot_get_changes('regression_slot2', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1');+ location | xid | data +-----------+-----+---------------------------------------------------------------------------------------------------------+ 0/28471B0 | 994 | BEGIN+ 0/28471B0 | 994 | table public.replication_example: INSERT: id[integer]:3 somedata[integer]:1 text[character varying]:'3'+ 0/2847268 | 994 | 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 existdiff --git a/contrib/test_decoding/sql/slot.sql b/contrib/test_decoding/sql/slot.sqlindex 5d6d97a..b1b853c 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 * FROM pg_logical_slot_get_changes('regression_slot1', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1');+SELECT * 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-committers@postgresql.org
Cc: petr.jelinek@2ndquadrant.com, andres@anarazel.de, tgl@sss.pgh.pa.us, peter_e@gmx.net
Subject: Re: pgsql: Add support for temporary replication slots
In-Reply-To: <a6b63bc4-ed38-2908-ef8d-1d96f2012d57@2ndquadrant.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox