Subject: Re: pgsql: Add support for temporary replication slots
To: Andres Freund <andres@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>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Peter Eisentraut <peter_e@gmx.net>,
 pgsql-committers@postgresql.org
From: Petr Jelinek <petr.jelinek@2ndquadrant.com>
Message-ID: <a6b63bc4-ed38-2908-ef8d-1d96f2012d57@2ndquadrant.com>
Date: Tue, 13 Dec 2016 02:28:26 +0100
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:45.0) Gecko/20100101
 Thunderbird/45.5.1
MIME-Version: 1.0
In-Reply-To: <20161213010059.dn64rrb3u7zxnnwk@alap3.anarazel.de>
Content-Type: multipart/mixed;
 boundary="------------76233B95026E3AC160B306F9"
Precedence: bulk
Sender: pgsql-committers-owner@postgresql.org

This is a multi-part message in MIME format.
--------------76233B95026E3AC160B306F9
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: 8bit

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

--------------76233B95026E3AC160B306F9
Content-Type: text/x-diff;
 name="0001-Various-temporary-slots-test-improvements.patch"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: attachment;
 filename*0="0001-Various-temporary-slots-test-improvements.patch"

=46rom 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_decodin=
g/expected/slot.out
index 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)
=20
+SELECT 'init' FROM pg_create_logical_replication_slot('regression_slot_t2'=
, 'test_decoding', true);
+ ?column?=20
+----------
+ 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 =3D 0;
 begin
-  while (select count(*) from pg_stat_activity where pid =3D '
+  while (select count(*) from pg_replication_slots where active_pid =3D '
     :'oldpid'
-  ') > 0 loop c :=3D c + 1; perform pg_stat_clear_snapshot(); end loop;
+  ') > 0 loop c :=3D 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=20
@@ -49,19 +57,44 @@ SELECT 'init' FROM pg_create_logical_replication_slot('=
regression_slot1', 'test_
  init
 (1 row)
=20
+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?=20
 ----------
  init
 (1 row)
=20
-SELECT * FROM pg_logical_slot_get_changes('regression_slot1', NULL, NULL);
- location | xid | data=20
-----------+-----+------
-(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=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20
+-----------+-----+--------------------------------------------------------=
-------------------------------------------------
+ 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)
=20
-SELECT * FROM pg_logical_slot_get_changes('regression_slot2', NULL, NULL);
- location | xid | data=20
-----------+-----+------
-(0 rows)
+SELECT * FROM pg_logical_slot_get_changes('regression_slot2', NULL, NULL, =
'include-xids', '0', 'skip-empty-xacts', '1');
+ location  | xid |                                                  data=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20
+-----------+-----+--------------------------------------------------------=
-------------------------------------------------
+ 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)
=20
+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..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('re=
gression_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);
=20
+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 =3D 0;
 begin
-  while (select count(*) from pg_stat_activity where pid =3D '
+  while (select count(*) from pg_replication_slots where active_pid =3D '
     :'oldpid'
-  ') > 0 loop c :=3D c + 1; perform pg_stat_clear_snapshot(); end loop;
+  ') > 0 loop c :=3D c + 1; perform pg_sleep(0.01); end loop;
   raise log ''slot test looped % times'', c;
 end';
=20
--- 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');
=20
 -- permanent slot has survived
 SELECT pg_drop_replication_slot('regression_slot_p');
=20
 -- 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');
--=20
2.7.4


--------------76233B95026E3AC160B306F9
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0


-- 
Sent via pgsql-committers mailing list (pgsql-committers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-committers

--------------76233B95026E3AC160B306F9--