Horton

mel0721_100_0_2_1.dat_PUB

application/octet-stream

Download
Filename: mel0721_100_0_2_1.dat_PUB
Type: application/octet-stream
Part: 0
Message: Re: [PATCH] Reuse Workers and Replication Slots during Logical Replication 
########################
# Check configurations #
########################
100 tables will be used while testing
0 tuples will be inserted to each tables
#########################
# IPC at publisher-side #
#########################
CREATE PUBLICATION ipc_at_publisher FOR TABLE ipc;
CREATE PUBLICATION
CREATE SUBSCRIPTION ipc_from_subscriber CONNECTION 'host=localhost user=postgres port=5432' PUBLICATION ipc_at_subscriber WITH(origin=NONE);
WARNING:  subscription "ipc_from_subscriber" requested copy_data with origin = NONE but might copy data that had a different origin
DETAIL:  Subscribed publication "ipc_at_subscriber" is subscribing to other publications.
HINT:  Verify that initial data copied from the publisher tables did not come from other origins.
NOTICE:  created replication slot "ipc_from_subscriber" on publisher
CREATE SUBSCRIPTION
INSERT INTO ipc VALUES('pub ipc ready');
INSERT 0 1
CALL ipc_wait_for('sub ipc ready');
NOTICE:  wait for state sub ipc ready
NOTICE:  waiting until state sub ipc ready
NOTICE:  ok, reached state sub ipc ready
CALL
SELECT * FROM ipc ORDER BY ts;
      msg      |             ts             
---------------+----------------------------
 pub ipc ready | 2023-07-28 21:40:04.641257
 sub ipc ready | 2023-07-28 21:40:04.64141
(2 rows)

#############################################
# Create tables, and populate the test data #
#############################################
CREATE TABLE busy_tbl(a text);
CREATE TABLE
CREATE SCHEMA test_tables;
CREATE SCHEMA
SELECT 'CREATE TABLE test_tables.manytables_'||i||'(i int);' FROM generate_series(1, 100) g(i) \gexec
CREATE TABLE test_tables.manytables_1(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_2(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_3(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_4(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_5(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_6(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_7(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_8(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_9(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_10(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_11(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_12(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_13(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_14(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_15(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_16(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_17(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_18(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_19(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_20(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_21(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_22(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_23(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_24(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_25(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_26(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_27(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_28(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_29(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_30(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_31(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_32(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_33(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_34(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_35(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_36(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_37(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_38(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_39(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_40(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_41(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_42(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_43(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_44(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_45(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_46(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_47(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_48(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_49(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_50(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_51(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_52(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_53(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_54(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_55(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_56(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_57(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_58(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_59(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_60(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_61(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_62(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_63(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_64(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_65(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_66(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_67(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_68(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_69(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_70(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_71(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_72(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_73(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_74(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_75(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_76(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_77(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_78(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_79(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_80(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_81(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_82(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_83(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_84(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_85(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_86(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_87(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_88(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_89(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_90(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_91(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_92(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_93(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_94(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_95(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_96(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_97(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_98(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_99(i int);
CREATE TABLE
CREATE TABLE test_tables.manytables_100(i int);
CREATE TABLE
SELECT 'INSERT INTO test_tables.manytables_'||i||' VALUES (generate_series(1, 0))' FROM generate_series(1, 100) g(i) \gexec
INSERT INTO test_tables.manytables_1 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_2 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_3 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_4 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_5 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_6 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_7 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_8 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_9 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_10 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_11 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_12 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_13 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_14 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_15 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_16 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_17 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_18 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_19 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_20 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_21 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_22 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_23 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_24 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_25 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_26 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_27 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_28 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_29 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_30 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_31 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_32 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_33 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_34 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_35 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_36 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_37 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_38 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_39 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_40 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_41 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_42 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_43 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_44 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_45 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_46 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_47 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_48 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_49 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_50 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_51 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_52 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_53 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_54 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_55 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_56 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_57 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_58 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_59 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_60 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_61 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_62 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_63 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_64 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_65 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_66 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_67 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_68 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_69 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_70 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_71 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_72 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_73 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_74 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_75 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_76 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_77 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_78 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_79 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_80 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_81 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_82 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_83 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_84 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_85 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_86 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_87 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_88 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_89 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_90 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_91 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_92 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_93 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_94 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_95 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_96 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_97 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_98 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_99 VALUES (generate_series(1, 0))
INSERT 0 0
INSERT INTO test_tables.manytables_100 VALUES (generate_series(1, 0))
INSERT 0 0
##############
# Busy table #
##############
CREATE PUBLICATION mypub FOR TABLE busy_tbl;
CREATE PUBLICATION
CALL ipc_wait_for('mysub is created');
NOTICE:  wait for state mysub is created
NOTICE:  ok, reached state mysub is created
CALL
SELECT * FROM ipc ORDER BY ts;
       msg        |             ts             
------------------+----------------------------
 pub ipc ready    | 2023-07-28 21:40:04.641257
 sub ipc ready    | 2023-07-28 21:40:04.64141
 mysub is created | 2023-07-28 21:40:05.128628
(3 rows)

#####################
# Alter publication #
#####################
ALTER PUBLICATION mypub ADD TABLES IN SCHEMA test_tables;
ALTER PUBLICATION
################
# Stay busy... #
################
CREATE OR REPLACE PROCEDURE stay_busy() AS $$
    DECLARE
      counter INTEGER := 0;
      commit_counter INTEGER := 0;
    BEGIN
      EXECUTE 'INSERT INTO ipc VALUES(''pub busy started'');';
      RAISE NOTICE 'START: stay_busy';
      WHILE NOT EXISTS (SELECT 1 FROM ipc WHERE msg = 'test finished') LOOP
        EXECUTE 'INSERT INTO busy_tbl VALUES(''some data'');';
        counter := counter + 1;
        commit_counter := commit_counter + 1;
        IF commit_counter = 1000 THEN
          COMMIT;
          commit_counter := 0;
        END IF;
      END LOOP;
      EXECUTE 'INSERT INTO ipc VALUES(''pub busy finished'');';
      RAISE NOTICE 'END: stay_busy inserted % records', counter;
    END;
    $$ LANGUAGE plpgsql;
CREATE PROCEDURE
CALL stay_busy();
NOTICE:  START: stay_busy
NOTICE:  END: stay_busy inserted 600430 records
CALL
SELECT * FROM ipc ORDER BY ts;
        msg        |             ts             
-------------------+----------------------------
 pub ipc ready     | 2023-07-28 21:40:04.641257
 sub ipc ready     | 2023-07-28 21:40:04.64141
 mysub is created  | 2023-07-28 21:40:05.128628
 pub busy started  | 2023-07-28 21:40:09.858026
 test started      | 2023-07-28 21:40:10.107935
 test finished     | 2023-07-28 21:40:31.73341
 pub busy finished | 2023-07-28 21:40:31.925437
(7 rows)

DROP PUBLICATION mypub;
DROP PUBLICATION
DROP SUBSCRIPTION ipc_from_subscriber;
NOTICE:  dropped replication slot "ipc_from_subscriber" on publisher
DROP SUBSCRIPTION
DROP PUBLICATION ipc_at_publisher;
DROP PUBLICATION