def test_single_transaction(started_cluster): conn = get_postgres_conn( ip=started_cluster.postgres_ip, port=started_cluster.postgres_port, database=True, auto_commit=False, ) cursor = conn.cursor() table_name = "postgresql_replica_0" create_postgres_table(cursor, table_name) conn.commit() pg_manager.create_materialized_db(ip=started_cluster.postgres_ip, port=started_cluster.postgres_port) assert_nested_table_is_created(instance, table_name) for query in queries: print("query {}".format(query)) cursor.execute(query.format(0)) time.sleep(5) result = instance.query(f"select count() from test_database.{table_name}") # no commit yet assert int(result) == 0 conn.commit() check_tables_are_synchronized(instance, table_name)
def test_table_override(started_cluster): cursor = pg_manager.get_db_cursor() table_name = "table_override" materialized_database = "test_database" create_postgres_table(cursor, table_name, template=postgres_table_template_5) instance.query( f"create table {table_name}(key Int32, value UUID) engine = PostgreSQL (postgres1, table={table_name})" ) instance.query( f"insert into {table_name} select number, generateUUIDv4() from numbers(10)" ) table_overrides = f" TABLE OVERRIDE {table_name} (COLUMNS (key Int32, value UUID) PARTITION BY key)" pg_manager.create_materialized_db( ip=started_cluster.postgres_ip, port=started_cluster.postgres_port, settings=[f"materialized_postgresql_tables_list = '{table_name}'"], table_overrides=table_overrides, ) assert_nested_table_is_created(instance, table_name, materialized_database) result = instance.query( f"show create table {materialized_database}.{table_name}") print(result) expected = "CREATE TABLE test_database.table_override\\n(\\n `key` Int32,\\n `value` UUID,\\n `_sign` Int8() MATERIALIZED 1,\\n `_version` UInt64() MATERIALIZED 1\\n)\\nENGINE = ReplacingMergeTree(_version)\\nPARTITION BY key\\nORDER BY tuple(key)" assert result.strip() == expected time.sleep(5) query = f"select * from {materialized_database}.{table_name} order by key" expected = instance.query(f"select * from {table_name} order by key") instance.query(f"drop table {table_name} no delay") assert_eq_with_retry(instance, query, expected)
def test_virtual_columns(started_cluster): conn = get_postgres_conn( ip=started_cluster.postgres_ip, port=started_cluster.postgres_port, database=True, ) cursor = conn.cursor() table_name = "postgresql_replica_0" create_postgres_table(cursor, table_name) pg_manager.create_materialized_db( ip=started_cluster.postgres_ip, port=started_cluster.postgres_port, settings=["materialized_postgresql_allow_automatic_update = 1"], ) assert_nested_table_is_created(instance, table_name) instance.query( f"INSERT INTO postgres_database.{table_name} SELECT number, number from numbers(10)" ) check_tables_are_synchronized(instance, table_name) # just check that it works, no check with `expected` because _version is taken as LSN, which will be different each time. result = instance.query( f"SELECT key, value, _sign, _version FROM test_database.{table_name};") print(result) cursor.execute(f"ALTER TABLE {table_name} ADD COLUMN value2 integer") instance.query( f"INSERT INTO postgres_database.{table_name} SELECT number, number, number from numbers(10, 10)" ) assert_number_of_columns(instance, 3, table_name) check_tables_are_synchronized(instance, table_name) result = instance.query( "SELECT key, value, value2, _sign, _version FROM test_database.postgresql_replica_0;" ) print(result) instance.query( f"INSERT INTO postgres_database.{table_name} SELECT number, number, number from numbers(20, 10)" ) check_tables_are_synchronized(instance, table_name) result = instance.query( f"SELECT key, value, value2, _sign, _version FROM test_database.{table_name};" ) print(result)
def test_load_and_sync_subset_of_database_tables(started_cluster): NUM_TABLES = 10 pg_manager.create_and_fill_postgres_tables(NUM_TABLES) publication_tables = "" for i in range(NUM_TABLES): if i < int(NUM_TABLES / 2): if publication_tables != "": publication_tables += ", " publication_tables += f"postgresql_replica_{i}" pg_manager.create_materialized_db( ip=started_cluster.postgres_ip, port=started_cluster.postgres_port, settings=[ "materialized_postgresql_tables_list = '{}'".format( publication_tables) ], ) time.sleep(1) for i in range(int(NUM_TABLES / 2)): table_name = f"postgresql_replica_{i}" assert_nested_table_is_created(instance, table_name) result = instance.query( """SELECT count() FROM system.tables WHERE database = 'test_database';""" ) assert int(result) == int(NUM_TABLES / 2) database_tables = instance.query("SHOW TABLES FROM test_database") for i in range(NUM_TABLES): table_name = "postgresql_replica_{}".format(i) if i < int(NUM_TABLES / 2): assert table_name in database_tables else: assert table_name not in database_tables instance.query( "INSERT INTO postgres_database.{} SELECT 50 + number, {} from numbers(100)" .format(table_name, i)) for i in range(NUM_TABLES): table_name = f"postgresql_replica_{i}" if i < int(NUM_TABLES / 2): check_tables_are_synchronized(instance, table_name)
def test_table_schema_changes(started_cluster): conn = get_postgres_conn( ip=started_cluster.postgres_ip, port=started_cluster.postgres_port, database=True, ) cursor = conn.cursor() NUM_TABLES = 5 for i in range(NUM_TABLES): create_postgres_table( cursor, "postgresql_replica_{}".format(i), template=postgres_table_template_2, ) instance.query( "INSERT INTO postgres_database.postgresql_replica_{} SELECT number, {}, {}, {} from numbers(25)" .format(i, i, i, i)) pg_manager.create_materialized_db( ip=started_cluster.postgres_ip, port=started_cluster.postgres_port, settings=["materialized_postgresql_allow_automatic_update = 1"], ) for i in range(NUM_TABLES): instance.query( "INSERT INTO postgres_database.postgresql_replica_{} SELECT 25 + number, {}, {}, {} from numbers(25)" .format(i, i, i, i)) check_several_tables_are_synchronized(instance, NUM_TABLES) expected = instance.query( "SELECT key, value1, value3 FROM test_database.postgresql_replica_3 ORDER BY key" ) altered_idx = random.randint(0, 4) altered_table = f"postgresql_replica_{altered_idx}" cursor.execute(f"ALTER TABLE {altered_table} DROP COLUMN value2") for i in range(NUM_TABLES): cursor.execute( f"INSERT INTO postgresql_replica_{i} VALUES (50, {i}, {i})") cursor.execute(f"UPDATE {altered_table} SET value3 = 12 WHERE key%2=0") time.sleep(2) assert_nested_table_is_created(instance, altered_table) assert_number_of_columns(instance, 3, altered_table) check_tables_are_synchronized(instance, altered_table) print("check1 OK") check_several_tables_are_synchronized(instance, NUM_TABLES) for i in range(NUM_TABLES): if i != altered_idx: instance.query( "INSERT INTO postgres_database.postgresql_replica_{} SELECT 51 + number, {}, {}, {} from numbers(49)" .format(i, i, i, i)) else: instance.query( "INSERT INTO postgres_database.postgresql_replica_{} SELECT 51 + number, {}, {} from numbers(49)" .format(i, i, i)) check_tables_are_synchronized(instance, altered_table) print("check2 OK") check_several_tables_are_synchronized(instance, NUM_TABLES)