More Replication Index Tests

column_row_filter.sql

CREATE TABLE test(a int, b int, c int);
ALTER TABLE test REPLICA IDENTITY FULL;
INSERT INTO test SELECT i,i,i FROM generate_series(0,1000000)i;

CREATE PUBLICATION pub_test_1 FOR TABLE test(a,b) WHERE (a%2=0);


-- on the target, same table with an index
CREATE TABLE test(a int, b int, c int);
CREATE INDEX i1 ON test(a);
CREATE SUBSCRIPTION sub_test_1
         CONNECTION 'host=localhost port=5432 user=onderkalaci dbname=postgres'
        PUBLICATION pub_test_1;
        

-- on the source we get errors because REPLICA IDENTITY FULL does not work with column filter
UPDATE test SET a = a + 1 WHERE a = 16;
ERROR:  cannot update table "test"
DETAIL:  Column list used by the publication does not cover the replica identity.
Time: 1.755 ms

nulls_not_distint.sql

CREATE TABLE test(a int, b int, c int);
ALTER TABLE test REPLICA IDENTITY FULL;
INSERT INTO test SELECT i,i,i FROM generate_series(0,1000000)i;

CREATE PUBLICATION pub_test_1 FOR TABLE test;


-- on the target, same table with an index
CREATE TABLE test(a int, b int, c int);

CREATE UNIQUE INDEX idx2_null_distinct_test ON test(a,b,c) NULLS NOT DISTINCT ;

CREATE SUBSCRIPTION sub_test_1
         CONNECTION 'host=localhost port=5432 user=onderkalaci dbname=postgres'
        PUBLICATION pub_test_1;
        


-- test with queries like
insert into test VALUES (NULL, NULL, NULL);

insert into test VALUES (NULL,NULL,NULL);
delete from test where a IS NULL;

publish_via_partition_root.sql

CREATE TABLE users_table_part(user_id bigint, value_1 int, value_2 int) PARTITION BY RANGE (value_1);
CREATE TABLE users_table_part_0 PARTITION OF users_table_part FOR VALUES FROM (0) TO (10);
CREATE TABLE users_table_part_1 PARTITION OF users_table_part FOR VALUES FROM (10) TO (20);
INSERT INTO users_table_part SELECT i, i %20, i %5000 FROM generate_series(0, 10000) i;


ALTER TABLE users_table_part REPLICA IDENTITY FULL;
ALTER TABLE users_table_part_0 REPLICA IDENTITY FULL;
ALTER TABLE users_table_part_1 REPLICA IDENTITY FULL;

CREATE PUBLICATION pub_test_1 FOR TABLE users_table_part WITH(publish_via_partition_root=true);


CREATE TABLE users_table_part(user_id bigint, value_1 int, value_2 int) PARTITION BY RANGE (value_1);
CREATE TABLE users_table_part_0 PARTITION OF users_table_part FOR VALUES FROM (0) TO (5);
CREATE TABLE users_table_part_1 PARTITION OF users_table_part FOR VALUES FROM (5) TO (10);
CREATE TABLE users_table_part_2 PARTITION OF users_table_part FOR VALUES FROM (10) TO (15);
CREATE TABLE users_table_part_3 PARTITION OF users_table_part FOR VALUES FROM (15) TO (20);


ALTER TABLE users_table_part REPLICA IDENTITY FULL;
ALTER TABLE users_table_part_0 REPLICA IDENTITY FULL;
ALTER TABLE users_table_part_1 REPLICA IDENTITY FULL;
ALTER TABLE users_table_part_2 REPLICA IDENTITY FULL;
ALTER TABLE users_table_part_3 REPLICA IDENTITY FULL;

CREATE INDEX i1 ON users_table_part(value_2);
CREATE SUBSCRIPTION sub_test_1
         CONNECTION 'host=localhost port=5432 user=onderkalaci dbname=postgres'
        PUBLICATION pub_test_1;