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)
