Horton

mel0721_100_0_2_1.dat_SUB

application/octet-stream

Download
Filename: mel0721_100_0_2_1.dat_SUB
Type: application/octet-stream
Part: 1
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 subscriber-side #
##########################
CREATE SUBSCRIPTION ipc_from_publisher CONNECTION 'host=localhost user=postgres port=5431' PUBLICATION ipc_at_publisher WITH(origin=NONE);
NOTICE:  created replication slot "ipc_from_publisher" on publisher
CREATE SUBSCRIPTION
CREATE PUBLICATION ipc_at_subscriber FOR TABLE ipc;
CREATE PUBLICATION
INSERT INTO ipc VALUES('sub ipc ready');
INSERT 0 1
CALL ipc_wait_for('pub ipc ready');
NOTICE:  wait for state pub ipc ready
NOTICE:  waiting until state pub ipc ready
NOTICE:  waiting until state pub ipc ready
NOTICE:  waiting until state pub ipc ready
NOTICE:  waiting until state pub ipc ready
NOTICE:  ok, reached state pub ipc ready
CALL
#################
# Create tables #
#################
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
######################################
# Create subscription for busy table #
######################################
CREATE SUBSCRIPTION mysub CONNECTION 'host=localhost user=postgres port=5431' PUBLICATION mypub;
NOTICE:  created replication slot "mysub" on publisher
CREATE SUBSCRIPTION
INSERT INTO ipc VALUES ('mysub is created');
INSERT 0 1
########
# Test #
########
CREATE OR REPLACE PROCEDURE log_rep_test(max INTEGER) AS $$
    DECLARE
        total_duration INTERVAL := '0';
        avg_duration FLOAT := 0.0;
        start_time TIMESTAMP;
        end_time TIMESTAMP;
    BEGIN
        start_time := clock_timestamp();

        -- time how long it takes for all the tablesyncs to become ready
        WHILE EXISTS (SELECT 1 FROM pg_subscription_rel WHERE srsubstate != 'r') LOOP
            COMMIT;
        END LOOP;

        end_time := clock_timestamp();

        total_duration := total_duration + (end_time - start_time);
    
        IF max > 0 THEN
            avg_duration := EXTRACT(EPOCH FROM total_duration) / max * 1000;
        END IF;
    
        RAISE NOTICE 'RESULT: %', avg_duration;
    END;
    $$ LANGUAGE plpgsql;
CREATE PROCEDURE
CALL ipc_wait_for('pub busy started');
NOTICE:  wait for state pub busy started
NOTICE:  waiting until state pub busy started
NOTICE:  waiting until state pub busy started
NOTICE:  waiting until state pub busy started
NOTICE:  waiting until state pub busy started
NOTICE:  waiting until state pub busy started
NOTICE:  waiting until state pub busy started
NOTICE:  waiting until state pub busy started
NOTICE:  waiting until state pub busy started
NOTICE:  waiting until state pub busy started
NOTICE:  waiting until state pub busy started
NOTICE:  waiting until state pub busy started
NOTICE:  waiting until state pub busy started
NOTICE:  waiting until state pub busy started
NOTICE:  waiting until state pub busy started
NOTICE:  waiting until state pub busy started
NOTICE:  waiting until state pub busy started
NOTICE:  waiting until state pub busy started
NOTICE:  waiting until state pub busy started
NOTICE:  waiting until state pub busy started
NOTICE:  waiting until state pub busy started
NOTICE:  waiting until state pub busy started
NOTICE:  waiting until state pub busy started
NOTICE:  waiting until state pub busy started
NOTICE:  waiting until state pub busy started
NOTICE:  waiting until state pub busy started
NOTICE:  waiting until state pub busy started
NOTICE:  waiting until state pub busy started
NOTICE:  waiting until state pub busy started
NOTICE:  waiting until state pub busy started
NOTICE:  waiting until state pub busy started
NOTICE:  waiting until state pub busy started
NOTICE:  waiting until state pub busy started
NOTICE:  waiting until state pub busy started
NOTICE:  waiting until state pub busy started
NOTICE:  waiting until state pub busy started
NOTICE:  waiting until state pub busy started
NOTICE:  waiting until state pub busy started
NOTICE:  waiting until state pub busy started
NOTICE:  waiting until state pub busy started
NOTICE:  waiting until state pub busy started
NOTICE:  waiting until state pub busy started
NOTICE:  waiting until state pub busy started
NOTICE:  waiting until state pub busy started
NOTICE:  waiting until state pub busy started
NOTICE:  waiting until state pub busy started
NOTICE:  waiting until state pub busy started
NOTICE:  waiting until state pub busy started
NOTICE:  waiting until state pub busy started
NOTICE:  waiting until state pub busy started
NOTICE:  ok, reached state pub busy started
CALL
INSERT INTO ipc VALUES ('test started');
INSERT 0 1
ALTER SUBSCRIPTION mysub REFRESH PUBLICATION;
ALTER SUBSCRIPTION
CALL log_rep_test(1);
NOTICE:  RESULT: 21592.356
CALL
INSERT INTO ipc VALUES ('test finished');
INSERT 0 1
CALL ipc_wait_for('pub busy finished');
NOTICE:  wait for state pub busy finished
NOTICE:  waiting until state pub busy finished
NOTICE:  waiting until state pub busy finished
NOTICE:  waiting until state pub busy finished
NOTICE:  waiting until state pub busy finished
NOTICE:  waiting until state pub busy finished
NOTICE:  waiting until state pub busy finished
NOTICE:  ok, reached state pub busy finished
CALL
SELECT count(*) FROM busy_tbl;
 count  
--------
 600430
(1 row)

DROP SUBSCRIPTION mysub;
NOTICE:  dropped replication slot "mysub" on publisher
DROP SUBSCRIPTION
DROP SUBSCRIPTION ipc_from_publisher;
NOTICE:  dropped replication slot "ipc_from_publisher" on publisher
DROP SUBSCRIPTION
DROP PUBLICATION ipc_at_subscriber;
DROP PUBLICATION