MaterializedPostgreSQL: failed CREATE TABLE (publication name is not escaped)

[statsbro-user]

When I try to

CREATE TABLE  (....) 
ENGINE = MaterializedPostgresSQL('myserver.postgres.database.azure.com:5432', 'sparklint-prod', 'table', 'ro_user', '*********')
PRIMRY KEY id;

SQL Error [1002] [07000]: std::exception. Code: 1001, type: pqxx::syntax_error, e.what() = ERROR:  syntax error at or near "-"
LINE 1: CREATE PUBLICATION sparklint-prod_table_ch_publication FOR TABLE...

How to reproduce

• Which ClickHouse server version to use 23.9.1.1854

The problem is that db name contains dash, and Clickhouse is not handling it property as the PUBLICATION name should not contain any dashes, quick solution for that is to put quotations marks around the PUBLCATION name, but then it will probably cause some problems in Clickhouse when it resolves names later in data processing.

[aN00B1s]

I have the same problem.
I would not like to rename the database.

[kssenii]

This issue was accidentally fixed in #55145.
Added an integration test for this issue in #57588.

[andrejs82git]

Hi!
I get that issue too.

I have that error :

2024.09.07 16:47:36.381205 [ 102 ] {} <Error> DatabaseMaterializedPostgreSQL (erp_replica_mps_2): Unable to load replicated tables list
2024.09.07 16:47:36.381239 [ 102 ] {} <Error> DatabaseMaterializedPostgreSQL (erp_replica_mps_2): Failed to start replication from PostgreSQL, will retry. Error: std::exception. Code: 1001, type: pqxx::syntax_error, e.what() = ERROR:  syntax error at or near "-"
LINE 1: DROP PUBLICATION IF EXISTS db-a-prokat_ch_publication
                                     ^
, Stack trace (when copying this message, always include the lines below):

0. pqxx::sql_error::sql_error(String const&, String const&, char const*) @ 0x0000000017b1cee3
1. pqxx::syntax_error::syntax_error(String const&, String const&, char const*, int) @ 0x0000000017b1e58f
2. pqxx::result::throw_sql_error(String const&, String const&) const @ 0x0000000017b1dfc2
3. pqxx::result::check_status(std::basic_string_view<char, std::char_traits<char>>) const @ 0x0000000017b1e799
4. pqxx::connection::make_result(pg_result*, std::shared_ptr<String> const&, std::basic_string_view<char, std::char_traits<char>>) @ 0x0000000017b18206
5. pqxx::connection::exec(std::shared_ptr<String>, std::basic_string_view<char, std::char_traits<char>>) @ 0x0000000017b19df8
6. pqxx::transaction_base::exec(std::basic_string_view<char, std::char_traits<char>>, std::basic_string_view<char, std::char_traits<char>>) @ 0x0000000017b23764
7. DB::PostgreSQLReplicationHandler::dropPublication(pqxx::nontransaction&) @ 0x00000000113095f9
8. postgres::Connection::execWithRetry(std::function<void (pqxx::nontransaction&)> const&) @ 0x00000000109f2cec
9. DB::PostgreSQLReplicationHandler::fetchRequiredTables() @ 0x000000001130d199
10. void std::__function::__policy_invoker<void ()>::__call_impl<std::__function::__default_alloc_func<DB::DatabaseMaterializedPostgreSQL::DatabaseMaterializedPostgreSQL(std::shared_ptr<DB::Context const>, String const&, StrongTypedef<wide::integer<128ul, unsigned int>, DB::UUIDTag>, bool, String const&, String const&, postgres::ConnectionInfo const&, std::unique_ptr<DB::MaterializedPostgreSQLSettings, std::default_delete<DB::MaterializedPostgreSQLSettings>>)::$_0, void ()>>(std::__function::__policy_storage const*) @ 0x00000000112f8c95
11. DB::BackgroundSchedulePool::threadFunction() @ 0x0000000010de0320
12. void std::__function::__policy_invoker<void ()>::__call_impl<std::__function::__default_alloc_func<ThreadFromGlobalPoolImpl<false, true>::ThreadFromGlobalPoolImpl<DB::BackgroundSchedulePool::BackgroundSchedulePool(unsigned long, StrongTypedef<unsigned long, CurrentMetrics::MetricTag>, StrongTypedef<unsigned long, CurrentMetrics::MetricTag>, char const*)::$_0>(DB::BackgroundSchedulePool::BackgroundSchedulePool(unsigned long, StrongTypedef<unsigned long, CurrentMetrics::MetricTag>, StrongTypedef<unsigned long, CurrentMetrics::MetricTag>, char const*)::$_0&&)::'lambda'(), void ()>>(std::__function::__policy_storage const*) @ 0x0000000010de13c7
13. void* std::__thread_proxy[abi:v15007]<std::tuple<std::unique_ptr<std::__thread_struct, std::default_delete<std::__thread_struct>>, void ThreadPoolImpl<std::thread>::scheduleImpl<void>(std::function<void ()>, Priority, std::optional<unsigned long>, bool)::'lambda0'()>>(void*) @ 0x000000000dbd7509
14. ? @ 0x00007f5e3b244609
15. ? @ 0x00007f5e3b169353
 (version 24.8.4.13 (official build))