ckb fails to start with rich-indexer using PostgreSQL

[15168316096]

Bug Report

Current 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

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 doc: Refactor README #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;

[15168316096]

It’s OK to package and verify this branch.

2024-10-08 12:34:17.610 +00:00 main INFO ckb_rich_indexer::store  PostgreSQL is connected.
2024-10-08 12:34:17.744 +00:00 main INFO ckb_rich_indexer::store  Running migrations...
2024-10-08 12:34:17.744 +00:00 main INFO ckb_rich_indexer::store  Found migration file: "20240603_add_is_spent_to_output.sql"
2024-10-08 12:34:17.760 +00:00 main INFO ckb_rich_indexer::store  Migrations are done.
2024-10-08 12:34:17.761 +00:00 main INFO ckb_rpc::server  Listen HTTP RPCServer on address: 127.0.0.1:8114
2024-10-08 12:34:17.835 +00:00 main INFO ckb_bin::subcommand::run  CKB service started ...

time curl  --location --request POST 'http://127.0.0.1:8114/' --header 'Content-Type: application/json' --data '{              
            
    "id": 2,
    "jsonrpc": "2.0",
    "method": "get_cells",
    "params": [                             
        {   
            "script_search_mode": "exact",                         
            "script": {                                                                           
                "code_hash": "0x9bd7e06f3ecf4be0f2fcd2188b23f1b9fcc88e5d4b65a8637b17723bbda3cce8",
                "hash_type": "type",
                "args": "0x"
            },                   
            "script_type": "lock",
            "filter": {
                "block_range": [
                    "0x1",
                    "0xffffffff"
                ]
            }
        },     
        "desc",
        "0x1",
        null
    ]
}'
{"jsonrpc":"2.0","result":{"last_cursor":"0x","objects":[]},"id":2}
real    0m0.008s
user    0m0.006s
sys    0m0.000s