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_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_schema_changes_2(started_cluster):
if instance.is_built_with_sanitizer() or instance.is_debug_build():
pytest.skip("Temporary disabled (FIXME)")
cursor = pg_manager.get_db_cursor()
table_name = "test_table"
create_postgres_table(cursor,
table_name,
template=postgres_table_template_2)
instance.query(
f"INSERT INTO postgres_database.{table_name} SELECT number, number, number, number from numbers(25)"
)
pg_manager.create_materialized_db(
ip=started_cluster.postgres_ip,
port=started_cluster.postgres_port,
settings=[
"materialized_postgresql_allow_automatic_update = 1, materialized_postgresql_tables_list='test_table'"
],
)
instance.query(
f"INSERT INTO postgres_database.{table_name} SELECT number, number, number, number from numbers(25, 25)"
)
check_tables_are_synchronized(instance, table_name)
cursor.execute(f"ALTER TABLE {table_name} DROP COLUMN value1")
cursor.execute(f"ALTER TABLE {table_name} DROP COLUMN value2")
cursor.execute(f"ALTER TABLE {table_name} ADD COLUMN value1 Text")
cursor.execute(f"ALTER TABLE {table_name} ADD COLUMN value2 Text")
cursor.execute(f"ALTER TABLE {table_name} DROP COLUMN value3")
cursor.execute(f"ALTER TABLE {table_name} ADD COLUMN value3 Text")
cursor.execute(f"ALTER TABLE {table_name} ADD COLUMN value4 Text")
cursor.execute(f"UPDATE {table_name} SET value3 = 'kek' WHERE key%2=0")
check_tables_are_synchronized(instance, table_name)
instance.query(
f"INSERT INTO postgres_database.{table_name} SELECT number, toString(number), toString(number), toString(number), toString(number) from numbers(50, 25)"
)
cursor.execute(f"ALTER TABLE {table_name} ADD COLUMN value5 Integer")
cursor.execute(f"ALTER TABLE {table_name} DROP COLUMN value2")
instance.query(
f"INSERT INTO postgres_database.{table_name} SELECT number, toString(number), toString(number), toString(number), number from numbers(75, 25)"
)
check_tables_are_synchronized(instance, table_name)
instance.restart_clickhouse()
check_tables_are_synchronized(instance, table_name)
cursor.execute(f"ALTER TABLE {table_name} DROP COLUMN value5")
cursor.execute(f"ALTER TABLE {table_name} ADD COLUMN value5 Text")
instance.query(
f"INSERT INTO postgres_database.{table_name} SELECT number, toString(number), toString(number), toString(number), toString(number) from numbers(100, 25)"
)
check_tables_are_synchronized(instance, table_name)
cursor.execute(f"ALTER TABLE {table_name} ADD COLUMN value6 Text")
cursor.execute(f"ALTER TABLE {table_name} ADD COLUMN value7 Integer")
cursor.execute(f"ALTER TABLE {table_name} ADD COLUMN value8 Integer")
cursor.execute(f"ALTER TABLE {table_name} DROP COLUMN value5")
instance.query(
f"INSERT INTO postgres_database.{table_name} SELECT number, toString(number), toString(number), toString(number), toString(number), number, number from numbers(125, 25)"
)
check_tables_are_synchronized(instance, table_name)
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_replicating_dml(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))
instance.query(
"INSERT INTO postgres_database.postgresql_replica_{} SELECT number, {} from numbers(50)"
.format(i, i))
pg_manager.create_materialized_db(ip=started_cluster.postgres_ip,
port=started_cluster.postgres_port)
for i in range(NUM_TABLES):
instance.query(
"INSERT INTO postgres_database.postgresql_replica_{} SELECT 50 + number, {} from numbers(1000)"
.format(i, i))
check_several_tables_are_synchronized(instance, NUM_TABLES)
for i in range(NUM_TABLES):
cursor.execute(
"UPDATE postgresql_replica_{} SET value = {} * {} WHERE key < 50;".
format(i, i, i))
cursor.execute(
"UPDATE postgresql_replica_{} SET value = {} * {} * {} WHERE key >= 50;"
.format(i, i, i, i))
check_several_tables_are_synchronized(instance, NUM_TABLES)
for i in range(NUM_TABLES):
cursor.execute(
"DELETE FROM postgresql_replica_{} WHERE (value*value + {}) % 2 = 0;"
.format(i, i))
cursor.execute(
"UPDATE postgresql_replica_{} SET value = value - (value % 7) WHERE key > 128 AND key < 512;"
.format(i))
cursor.execute(
"DELETE FROM postgresql_replica_{} WHERE key % 7 = 1;".format(
i, i))
check_several_tables_are_synchronized(instance, NUM_TABLES)
def test_replica_identity_index(started_cluster):
conn = get_postgres_conn(
ip=started_cluster.postgres_ip,
port=started_cluster.postgres_port,
database=True,
)
cursor = conn.cursor()
create_postgres_table(cursor,
"postgresql_replica",
template=postgres_table_template_3)
cursor.execute(
"CREATE unique INDEX idx on postgresql_replica(key1, key2);")
cursor.execute(
"ALTER TABLE postgresql_replica REPLICA IDENTITY USING INDEX idx")
instance.query(
"INSERT INTO postgres_database.postgresql_replica SELECT number, number, number, number from numbers(50, 10)"
)
pg_manager.create_materialized_db(ip=started_cluster.postgres_ip,
port=started_cluster.postgres_port)
instance.query(
"INSERT INTO postgres_database.postgresql_replica SELECT number, number, number, number from numbers(100, 10)"
)
check_tables_are_synchronized(instance,
"postgresql_replica",
order_by="key1")
cursor.execute(
"UPDATE postgresql_replica SET key1=key1-25 WHERE key1<100 ")
cursor.execute(
"UPDATE postgresql_replica SET key2=key2-25 WHERE key2>100 ")
cursor.execute(
"UPDATE postgresql_replica SET value1=value1+100 WHERE key1<100 ")
cursor.execute(
"UPDATE postgresql_replica SET value2=value2+200 WHERE key2>100 ")
check_tables_are_synchronized(instance,
"postgresql_replica",
order_by="key1")
cursor.execute("DELETE FROM postgresql_replica WHERE key2<75;")
check_tables_are_synchronized(instance,
"postgresql_replica",
order_by="key1")
def test_changing_replica_identity_value(started_cluster):
conn = get_postgres_conn(ip=started_cluster.postgres_ip,
port=started_cluster.postgres_port,
database=True)
cursor = conn.cursor()
create_postgres_table(cursor, 'postgresql_replica')
instance.query(
"INSERT INTO postgres_database.postgresql_replica SELECT 50 + number, number from numbers(50)"
)
pg_manager.create_materialized_db(ip=started_cluster.postgres_ip,
port=started_cluster.postgres_port)
instance.query(
"INSERT INTO postgres_database.postgresql_replica SELECT 100 + number, number from numbers(50)"
)
check_tables_are_synchronized(instance, 'postgresql_replica')
cursor.execute("UPDATE postgresql_replica SET key=key-25 WHERE key<100 ")
check_tables_are_synchronized(instance, 'postgresql_replica')
def test_multiple_databases(started_cluster):
NUM_TABLES = 5
conn = get_postgres_conn(
ip=started_cluster.postgres_ip,
port=started_cluster.postgres_port,
database=False,
)
cursor = conn.cursor()
pg_manager.create_postgres_db(cursor, "postgres_database_1")
pg_manager.create_postgres_db(cursor, "postgres_database_2")
conn1 = get_postgres_conn(
ip=started_cluster.postgres_ip,
port=started_cluster.postgres_port,
database=True,
database_name="postgres_database_1",
)
conn2 = get_postgres_conn(
ip=started_cluster.postgres_ip,
port=started_cluster.postgres_port,
database=True,
database_name="postgres_database_2",
)
cursor1 = conn1.cursor()
cursor2 = conn2.cursor()
pg_manager.create_clickhouse_postgres_db(
cluster.postgres_ip,
cluster.postgres_port,
"postgres_database_1",
"postgres_database_1",
)
pg_manager.create_clickhouse_postgres_db(
cluster.postgres_ip,
cluster.postgres_port,
"postgres_database_2",
"postgres_database_2",
)
cursors = [cursor1, cursor2]
for cursor_id in range(len(cursors)):
for i in range(NUM_TABLES):
table_name = "postgresql_replica_{}".format(i)
create_postgres_table(cursors[cursor_id], table_name)
instance.query(
"INSERT INTO postgres_database_{}.{} SELECT number, number from numbers(50)"
.format(cursor_id + 1, table_name))
print(
"database 1 tables: ",
instance.query(
"""SELECT name FROM system.tables WHERE database = 'postgres_database_1';"""
),
)
print(
"database 2 tables: ",
instance.query(
"""SELECT name FROM system.tables WHERE database = 'postgres_database_2';"""
),
)
pg_manager.create_materialized_db(
started_cluster.postgres_ip,
started_cluster.postgres_port,
"test_database_1",
"postgres_database_1",
)
pg_manager.create_materialized_db(
started_cluster.postgres_ip,
started_cluster.postgres_port,
"test_database_2",
"postgres_database_2",
)
cursors = [cursor1, cursor2]
for cursor_id in range(len(cursors)):
for i in range(NUM_TABLES):
table_name = "postgresql_replica_{}".format(i)
instance.query(
"INSERT INTO postgres_database_{}.{} SELECT 50 + number, number from numbers(50)"
.format(cursor_id + 1, table_name))
for cursor_id in range(len(cursors)):
for i in range(NUM_TABLES):
table_name = "postgresql_replica_{}".format(i)
check_tables_are_synchronized(
instance,
table_name,
"key",
"postgres_database_{}".format(cursor_id + 1),
"test_database_{}".format(cursor_id + 1),
)
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)
def test_add_new_table_to_replication(started_cluster):
cursor = pg_manager.get_db_cursor()
cursor.execute("DROP TABLE IF EXISTS test_table")
NUM_TABLES = 5
pg_manager.create_and_fill_postgres_tables_from_cursor(
cursor, NUM_TABLES, 10000)
pg_manager.create_materialized_db(ip=started_cluster.postgres_ip,
port=started_cluster.postgres_port)
check_several_tables_are_synchronized(instance, NUM_TABLES)
result = instance.query("SHOW TABLES FROM test_database")
assert (
result ==
"postgresql_replica_0\npostgresql_replica_1\npostgresql_replica_2\npostgresql_replica_3\npostgresql_replica_4\n"
)
table_name = "postgresql_replica_5"
pg_manager.create_and_fill_postgres_table_from_cursor(cursor, table_name)
result = instance.query("SHOW CREATE DATABASE test_database")
assert (result[:63] ==
"CREATE DATABASE test_database\\nENGINE = MaterializedPostgreSQL("
) # Check without ip
assert (
result[-59:] ==
"\\'postgres_database\\', \\'postgres\\', \\'mysecretpassword\\')\n")
result = instance.query_and_get_error(
"ALTER DATABASE test_database MODIFY SETTING materialized_postgresql_tables_list='tabl1'"
)
assert (
"Changing setting `materialized_postgresql_tables_list` is not allowed"
in result)
result = instance.query_and_get_error(
"ALTER DATABASE test_database MODIFY SETTING materialized_postgresql_tables='tabl1'"
)
assert "Database engine MaterializedPostgreSQL does not support setting" in result
instance.query(f"ATTACH TABLE test_database.{table_name}")
result = instance.query("SHOW TABLES FROM test_database")
assert (
result ==
"postgresql_replica_0\npostgresql_replica_1\npostgresql_replica_2\npostgresql_replica_3\npostgresql_replica_4\npostgresql_replica_5\n"
)
check_tables_are_synchronized(instance, table_name)
instance.query(
f"INSERT INTO postgres_database.{table_name} SELECT number, number from numbers(10000, 10000)"
)
check_tables_are_synchronized(instance, table_name)
result = instance.query_and_get_error(
f"ATTACH TABLE test_database.{table_name}")
assert "Table test_database.postgresql_replica_5 already exists" in result
result = instance.query_and_get_error(
"ATTACH TABLE test_database.unknown_table")
assert "PostgreSQL table unknown_table does not exist" in result
result = instance.query("SHOW CREATE DATABASE test_database")
assert (result[:63] ==
"CREATE DATABASE test_database\\nENGINE = MaterializedPostgreSQL(")
assert (
result[-180:] ==
")\\nSETTINGS materialized_postgresql_tables_list = \\'postgresql_replica_0,postgresql_replica_1,postgresql_replica_2,postgresql_replica_3,postgresql_replica_4,postgresql_replica_5\\'\n"
)
table_name = "postgresql_replica_6"
create_postgres_table(cursor, table_name)
instance.query(
"INSERT INTO postgres_database.{} SELECT number, number from numbers(10000)"
.format(table_name))
instance.query(f"ATTACH TABLE test_database.{table_name}")
instance.restart_clickhouse()
table_name = "postgresql_replica_7"
create_postgres_table(cursor, table_name)
instance.query(
"INSERT INTO postgres_database.{} SELECT number, number from numbers(10000)"
.format(table_name))
instance.query(f"ATTACH TABLE test_database.{table_name}")
result = instance.query("SHOW CREATE DATABASE test_database")
assert (result[:63] ==
"CREATE DATABASE test_database\\nENGINE = MaterializedPostgreSQL(")
assert (
result[-222:] ==
")\\nSETTINGS materialized_postgresql_tables_list = \\'postgresql_replica_0,postgresql_replica_1,postgresql_replica_2,postgresql_replica_3,postgresql_replica_4,postgresql_replica_5,postgresql_replica_6,postgresql_replica_7\\'\n"
)
instance.query(
f"INSERT INTO postgres_database.{table_name} SELECT number, number from numbers(10000, 10000)"
)
result = instance.query("SHOW TABLES FROM test_database")
assert (
result ==
"postgresql_replica_0\npostgresql_replica_1\npostgresql_replica_2\npostgresql_replica_3\npostgresql_replica_4\npostgresql_replica_5\npostgresql_replica_6\npostgresql_replica_7\n"
)
check_several_tables_are_synchronized(instance, NUM_TABLES + 3)
