Repository: nervosnetwork/ckb (language: rust)

Issue:
ckb fails to start with rich-indexer using PostgreSQL
## Bug Report

<!-- Thanks for your bug report! We appreciate your time to fill following sections to help us locate the root cause. -->

### Current Behavior
<!-- A clear and concise description of the behavior. -->
ckb fails to start with rich-indexer using PostgreSQL.
In the binary packaged by ckb 119 pre version, using postgresql version >= 16.4, executing ./ckb run --rich-indexer fails to start the ckb node.

### Expected Behavior
<!-- A clear and concise description of what you expected to happen. -->
ckb success to start with rich-indexer using PostgreSQL.
### Environment

- **CKB version**:  ckb 0.119.0-pre (762fd06 2024-09-26)
- **Chain**: dev
- **Operating system**: Ubuntu 20.04
- **Arch**: Linux test-new-02 6.2.0-1012-aws #12~22.04.1-Ubuntu SMP Thu Sep  7 14:01:24 UTC 2023 x86_64 x86_64 x86_64 GNU/Linux
- **Installation**:  Built from source
https://github.com/nervosnetwork/ckb/tree/develop

### Additional context/Screenshots

ckb node log info
```
<!-- Add any other context about the problem here. If applicable, add ckb.toml and screenshots to help explain. -->
./ckb run --rich-indexer
2024-10-08 09:13:32.179 +00:00 main INFO sentry  sentry is disabled
2024-10-08 09:13:32.272 +00:00 main INFO ckb_bin::setup  assume_valid_target set to 0xd07c7f0fcea6e2a20a51dabbf0caae7ebff49723bcd8bac81a8b8c021a546a32
2024-10-08 09:13:32.272 +00:00 main INFO ckb_bin::subcommand::run  ckb version: 0.119.0-pre (762fd06 2024-09-26)
2024-10-08 09:13:32.272 +00:00 main INFO ckb_bin::subcommand::run  run rpc server with 8 threads
2024-10-08 09:13:32.272 +00:00 main INFO ckb_launcher  Miner is disabled; edit ckb.toml to enable it
2024-10-08 09:13:32.397 +00:00 main INFO ckb_memory_tracker::process  track current process: disable
2024-10-08 09:13:32.402 +00:00 main INFO ckb_launcher  chain genesis hash: 0x92b197aa1fba0f63633922c61c92375c9c074a93e85963554f5499fe1450d0e5
2024-10-08 09:13:32.403 +00:00 main INFO ckb_sync::types  header_map.memory_limit 256MB
2024-10-08 09:13:32.402 +00:00 init_load_unverified_blocks INFO ckb_chain::init_load_unverified  finding unverified blocks, current tip: 0-Byte32(0x92b197aa1fba0f63633922c61c92375c9c074a93e85963554f5499fe1450d0e5)
2024-10-08 09:13:32.403 +00:00 init_load_unverified_blocks INFO ckb_chain::init_load_unverified  no unverified blocks found after tip, current tip: 0-Byte32(0x92b197aa1fba0f63633922c61c92375c9c074a93e85963554f5499fe1450d0e5)
2024-10-08 09:13:32.403 +00:00 init_load_unverified_blocks INFO ckb_chain::init_load_unverified  find unverified blocks finished
2024-10-08 09:13:32.403 +00:00 main INFO ckb_network::network  Loading the peer store. This process may take a few seconds to complete.
2024-10-08 09:13:32.403 +00:00 GlobalRt-7 INFO ckb_network::network  Listen on address: /ip4/0.0.0.0/tcp/8115
2024-10-08 09:13:32.403 +00:00 GlobalRt-7 INFO ckb_network::network  p2p service event: ListenStarted { address: "/ip4/0.0.0.0/tcp/8115" }
2024-10-08 09:13:32.418 +00:00 main INFO ckb_rich_indexer::store  PostgreSQL is connected.
2024-10-08 09:13:32.437 +00:00 main ERROR panic  thread 'main' panicked at 'Failed to connect to rich-indexer database: error returned from database: syntax error at or near "NOT"

Caused by:
    syntax error at or near "NOT"

Stack backtrace:
   0: anyhow::error::<impl core::convert::From<E> for anyhow::Error>::from
   1: ckb_rich_indexer::store::SQLXPool::connect::{{closure}}
   2: ckb_rpc::service_builder::ServiceBuilder::enable_indexer
   3: ckb_launcher::Launcher::start_network_and_rpc
   4: ckb_bin::subcommand::run::run
   5: ckb_bin::run_app_inner
   6: ckb_bin::run_app
   7: ckb::main
   8: std::sys_common::backtrace::__rust_begin_short_backtrace
   9: std::rt::lang_start
  10: <unknown>
  11: __libc_start_main
  12: _start': util/rich-indexer/src/service.rs:38   0: ckb_logger_service::setup_panic_logger::{{closure}}
   1: <alloc::boxed::Box<F,A> as core::ops::function::Fn<Args>>::call
             at /rustc/82e1608dfa6e0b5569232559e3d385fea5a93112/library/alloc/src/boxed.rs:2021:9
      std::panicking::rust_panic_with_hook
             at /rustc/82e1608dfa6e0b5569232559e3d385fea5a93112/library/std/src/panicking.rs:783:13
   2: std::panicking::begin_panic_handler::{{closure}}
             at /rustc/82e1608dfa6e0b5569232559e3d385fea5a93112/library/std/src/panicking.rs:657:13
   3: std::sys_common::backtrace::__rust_end_short_backtrace
             at /rustc/82e1608dfa6e0b5569232559e3d385fea5a93112/library/std/src/sys_common/backtrace.rs:170:18
   4: rust_begin_unwind
             at /rustc/82e1608dfa6e0b5569232559e3d385fea5a93112/library/std/src/panicking.rs:645:5
   5: core::panicking::panic_fmt
             at /rustc/82e1608dfa6e0b5569232559e3d385fea5a93112/library/core/src/panicking.rs:72:14
   6: core::result::unwrap_failed
             at /rustc/82e1608dfa6e0b5569232559e3d385fea5a93112/library/core/src/result.rs:1653:5
   7: ckb_rpc::service_builder::ServiceBuilder::enable_indexer
   8: ckb_launcher::Launcher::start_network_and_rpc
   9: ckb_bin::subcommand::run::run
  10: ckb_bin::run_app_inner
  11: ckb_bin::run_app
  12: ckb::main
  13: std::sys_common::backtrace::__rust_begin_short_backtrace
  14: std::rt::lang_start
  15: <unknown>
  16: __libc_start_main
  17: _start

terminate called without an active exception
Aborted (core dumped)
```

postgresql log info
```
$ sudo tail -f /var/log/postgresql/postgresql-16-main.log
2024-10-08 09:03:15.791 UTC [2947664] LOG:  checkpoint starting: time
2024-10-08 09:03:15.914 UTC [2947664] LOG:  checkpoint complete: wrote 4 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.109 s, sync=0.005 s, total=0.123 s; sync files=3, longest=0.003 s, average=0.002 s; distance=0 kB, estimate=0 kB; lsn=0/1952088, redo lsn=0/1952050
2024-10-08 09:12:55.851 UTC [3074765] postgres@ckb-rich-indexer ERROR:  syntax error at or near "NOT" at character 20
2024-10-08 09:12:55.851 UTC [3074765] postgres@ckb-rich-indexer STATEMENT:  CREATE DATABASE IF NOT EXISTS "postgres"
2024-10-08 09:12:55.868 UTC [3074765] postgres@ckb-rich-indexer LOG:  could not receive data from client: Connection reset by peer
2024-10-08 09:13:32.418 UTC [3080449] postgres@ckb-rich-indexer ERROR:  syntax error at or near "NOT" at character 20
2024-10-08 09:13:32.418 UTC [3080449] postgres@ckb-rich-indexer STATEMENT:  CREATE DATABASE IF NOT EXISTS "postgres"
2024-10-08 09:13:32.438 UTC [3080449] postgres@ckb-rich-indexer LOG:  could not receive data from client: Connection reset by peer
2024-10-08 09:18:42.412 UTC [3123752] postgres@ckb-rich-indexer ERROR:  relation "table_name" does not exist at character 15
2024-10-08 09:18:42.412 UTC [3123752] postgres@ckb-rich-indexer STATEMENT:  SELECT * FROM table_name WHERE NOT condition;
```


Discussion / hints:

Fix patch:
diff --git a/util/rich-indexer/src/store.rs b/util/rich-indexer/src/store.rs
index abd95914a0..1855997983 100644
--- a/util/rich-indexer/src/store.rs
+++ b/util/rich-indexer/src/store.rs
@@ -70,6 +70,7 @@ impl SQLXPool {
                 pool
             }
             DBDriver::Postgres => {
+                self.postgres_init(db_config).await?;
                 let uri = build_url_for_postgres(db_config);
                 let connection_options =
                     AnyConnectOptions::from_str(&uri)?.log_statements(LevelFilter::Trace);
@@ -79,9 +80,6 @@ impl SQLXPool {
                     .set(pool.clone())
                     .map_err(|_| anyhow!("set pool failed"))?;
 
-                SQLXPool::new_query(r#"CREATE DATABASE IF NOT EXISTS "postgres""#)
-                    .execute(&pool)
-                    .await?;
                 self.create_tables_for_postgres().await?;
 
                 self.db_driver = DBDriver::Postgres;
@@ -207,6 +205,30 @@ impl SQLXPool {
         }
         Ok(())
     }
+
+    pub async fn postgres_init(&mut self, db_config: &RichIndexerConfig) -> Result<()> {
+        // Connect to the "postgres" database first
+        let mut temp_config = db_config.clone();
+        temp_config.db_name = "postgres".to_string();
+        let uri = build_url_for_postgres(&temp_config);
+        let connection_options =
+            AnyConnectOptions::from_str(&uri)?.log_statements(LevelFilter::Trace);
+        let tmp_pool_options = AnyPoolOptions::new();
+        let pool = tmp_pool_options.connect_with(connection_options).await?;
+        // Check if database exists
+        let query =
+            SQLXPool::new_query(r#"SELECT EXISTS (SELECT FROM pg_database WHERE datname = $1)"#)
+                .bind(db_config.db_name.as_str());
+        let row = query.fetch_one(&pool).await?;
+        // If database does not exist, create it
+        if !row.get::<bool, _>(0) {
+            let query = format!(r#"CREATE DATABASE "{}""#, db_config.db_name);
+            SQLXPool::new_query(&query).execute(&pool).await?;
+            Ok(())
+        } else {
+            Ok(())
+        }
+    }
 }
 
 fn build_url_for_sqlite(db_config: &RichIndexerConfig) -> String {


Test patch: