lijunwangs/solana-postgres-rpc-server

A RPC server serving major RPC requests from PostgreSQL database streamed by the AccountsDb plugin

solana-postgres-rpc-server

A RPC server serving major RPC requests from PostgreSQL database streamed by the solana-geyser-plugin-postgres plugin.

Build the RPC Server

Do the following to build

cargo build [--release]

Run the RPC Server

Prepare the Database

The RPC server requires a PostgreSQL database has already been setup. Please consult with solana-geyser-plugin-postgres on setting up the plugin and database. In addition, run the following script to create some of the stored functions and procedures in the database which are used for serving RPC queries.

psql -U solana -p 5433 -h 10.138.0.9 -w -d solana -f sql/query_account.sql

In order for this server to produce correct results following the RPC API spec you need to enable store_account_historical_data in the plugin configuration.

Start the RPC Server

Execute the command similar to the following to run the RPC server listening on port 8888.

solana-postgres-rpc-server --db-config ~/postgres-db-config.json --rpc-port 8888 --rpc-threads 100 -o -

Use the following to see the detailed arguments of the command line.

solana-postgres-rpc-server --help

The Database Configuration File Format

The postgres-db-config.json file specifies the connection information to the PostgreSQL database in JSON format.

For example,

{
	"host": "postgres-server",
	"user": "solana",
	"port": 5433,
}

The host, user, and port control the PostgreSQL configuration information. For more advanced connection options such as passwords, please use the connection_str field. Please see Rust Postgres Configuration.

Running RPC Queries Against the Server

The Server currently supports the following RPC calls:

• getAccountInfo
• getMultipleAccounts

The Server has partial/preliminary support for:

• getProgramAccounts

There is plan to add support for other RPC calls related to blocks, accounts and transactions.

Please see JSON RPC API for details on these APIs.

For example to get the account info for an account, do the following

curl http://localhost:8888 -X POST -H "Content-Type: application/json" -d '
  {
    "jsonrpc": "2.0",
    "id": 1,
    "method": "getAccountInfo",
    "params": [
      "vines1vzrYbzLMRdu58ou5XTby4qAqVRLmqo36NKPTg",
      {
        "encoding": "base58"
      }
    ]
  }
'

Response

{
    "id": 1,
    "jsonrpc": "2.0",
    "result": {
        "context": {
            "slot": 126599223
        },
        "value": {
            "data": [
                "",
                "base58"
            ],
            "executable": false,
            "lamports": 1974219920,
            "owner": "11111111111111111111111111111111",
            "rentEpoch": 293
        }
    }
}

solana-postgres-rpc-server's Issues

`getProgramAccounts` returning empty resonse.

I'm trying to get all token accounts with -

curl http://localhost:8888 -X POST -H "Content-Type: application/json" -d '
  {
    "jsonrpc": "2.0",
    "id": 1,
    "method": "getProgramAccounts",
    "params": [
      "TokenkegQfeZyiNwAJbNbGKPFXCWuBvf9Ss623VQ5DA",
      {
        "filters": [
          {
            "dataSize": 165
          },
          {
            "memcmp": {
              "offset": 32,
              "bytes": "HohZaV9RWGQfgBaqo1TDPp4P4a7kKW5nCdahr3W9DNXr"
            }
          }
        ]
      }
    ]
  }
'

But it returns curl: (52) Empty reply from server. I get some errors also in the rpc server logs -

[2023-07-18T07:54:42.124183817Z INFO  solana_postgres_rpc_server::rpc::rpc_accounts] get_program_accounts rpc request received: "TokenkegQfeZyiNwAJbNbGKPFXCWuBvf9Ss623VQ5DA"
[2023-07-18T07:54:42.124228517Z INFO  solana_postgres_rpc_server::request_processor] get_program_accounts is called... TokenkegQfeZyiNwAJbNbGKPFXCWuBvf9Ss623VQ5DA
[2023-07-18T07:54:42.128715485Z INFO  solana_postgres_rpc_server::postgres_client] Creating SimplePostgresClient...
[2023-07-18T07:54:42.128728975Z INFO  solana_postgres_rpc_server::postgres_client] Preparing statements ...
[2023-07-18T07:54:42.128735615Z INFO  solana_postgres_rpc_server::postgres_client::postgres_client_account] Preparing statement SELECT pubkey, slot, owner, lamports, executable, rent_epoch, data, write_version, updated_on FROM account AS acct WHERE pubkey = $1
[2023-07-18T07:54:42.128741455Z INFO  solana_postgres_rpc_server::postgres_client] Preparing statement SELECT pubkey, slot, owner, lamports, executable, rent_epoch, data, write_version, updated_on FROM account AS acct WHERE pubkey = $1
[2023-07-18T07:54:42.129697014Z INFO  solana_postgres_rpc_server::postgres_client] Prepared statement, ok? true
[2023-07-18T07:54:42.129708014Z INFO  solana_postgres_rpc_server::postgres_client::postgres_client_account] Preparing statement SELECT get_account_with_commitment_level($1, $2)
[2023-07-18T07:54:42.129713605Z INFO  solana_postgres_rpc_server::postgres_client] Preparing statement SELECT get_account_with_commitment_level($1, $2)
[2023-07-18T07:54:42.130221254Z INFO  solana_postgres_rpc_server::postgres_client] Prepared statement, ok? true
[2023-07-18T07:54:42.130229774Z INFO  solana_postgres_rpc_server::postgres_client] Preparing statement SELECT pubkey, slot, owner, lamports, executable, rent_epoch, data, write_version, updated_on FROM account AS acct WHERE owner = $1 AND slot <= $2
[2023-07-18T07:54:42.130430624Z INFO  solana_postgres_rpc_server::postgres_client] Prepared statement, ok? true
[2023-07-18T07:54:42.130436684Z INFO  solana_postgres_rpc_server::postgres_client] Preparing statement SELECT acct.pubkey, acct.slot, acct.owner, acct.lamports, acct.executable, acct.rent_epoch, acct.data, acct.write_version, acct.updated_on FROM account AS acct JOIN  spl_token_owner_index AS owner_idx ON acct.pubkey = owner_idx.owner_key WHERE owner_idx.owner_key = $1
                AND owner_idx.slot <= $2
[2023-07-18T07:54:42.130612174Z INFO  solana_postgres_rpc_server::postgres_client] Prepared statement, ok? true
[2023-07-18T07:54:42.130620954Z INFO  solana_postgres_rpc_server::postgres_client] Preparing statement SELECT acct.pubkey, acct.slot, acct.owner, acct.lamports, acct.executable, acct.rent_epoch, acct.data, acct.write_version, acct.updated_on FROM account AS acct JOIN  spl_token_mint_index AS owner_idx ON acct.pubkey = owner_idx.mint_key WHERE owner_idx.mint_key = $1
                AND owner_idx.slot <= $2
[2023-07-18T07:54:42.130834114Z INFO  solana_postgres_rpc_server::postgres_client] Prepared statement, ok? true
[2023-07-18T07:54:42.130841604Z INFO  solana_postgres_rpc_server::postgres_client] Preparing statement SELECT s.* FROM slot s WHERE s.slot IN (SELECT max(s2.slot) FROM slot AS s2)
[2023-07-18T07:54:42.131576804Z INFO  solana_postgres_rpc_server::postgres_client] Prepared statement, ok? true
[2023-07-18T07:54:42.131585714Z INFO  solana_postgres_rpc_server::postgres_client] Preparing statement SELECT s.* FROM slot s WHERE s.slot IN (SELECT max(s2.slot) FROM slot AS s2 WHERE s2.status in ('confirmed', 'rooted'))
[2023-07-18T07:54:42.132650073Z INFO  solana_postgres_rpc_server::postgres_client] Prepared statement, ok? true
[2023-07-18T07:54:42.132659313Z INFO  solana_postgres_rpc_server::postgres_client] Preparing statement SELECT s.* FROM slot s WHERE s.slot IN (SELECT max(s2.slot) FROM slot AS s2 WHERE s2.status = 'rooted')
[2023-07-18T07:54:42.132790003Z INFO  solana_postgres_rpc_server::postgres_client] Prepared statement, ok? true
[2023-07-18T07:54:42.132798403Z INFO  solana_postgres_rpc_server::postgres_client::postgres_client_account] Preparing statement SELECT get_account_with_commitment_level_and_slot($1, $2, $3)
[2023-07-18T07:54:42.132805913Z INFO  solana_postgres_rpc_server::postgres_client] Preparing statement SELECT get_account_with_commitment_level_and_slot($1, $2, $3)
[2023-07-18T07:54:42.134834232Z INFO  solana_postgres_rpc_server::postgres_client] Prepared statement, ok? true
[2023-07-18T07:54:42.134851983Z INFO  solana_postgres_rpc_server::postgres_client] Prepared statements.
thread 'sol-rpc-el' panicked at 'error retrieving column 1: error deserializing column 1: a Postgres value was `NULL`', /home/flux/.cargo/registry/src/github.com-1ecc6299db9ec823/tokio-postgres-0.7.8/src/row.rs:151:25
stack backtrace:
   0: rust_begin_unwind
             at /rustc/84c898d65adf2f39a5a98507f1fe0ce10a2b8dbc/library/std/src/panicking.rs:579:5
   1: core::panicking::panic_fmt
             at /rustc/84c898d65adf2f39a5a98507f1fe0ce10a2b8dbc/library/core/src/panicking.rs:64:14
   2: tokio_postgres::row::Row::get
   3: <core::iter::adapters::map::Map<I,F> as core::iter::traits::iterator::Iterator>::fold
   4: alloc::vec::in_place_collect::<impl alloc::vec::spec_from_iter::SpecFromIter<T,I> for alloc::vec::Vec<T>>::from_iter
   5: solana_postgres_rpc_server::postgres_client::postgres_client_slot::load_single_slot
   6: solana_postgres_rpc_server::postgres_client::postgres_client_slot::<impl solana_postgres_rpc_server::postgres_client::AsyncPooledPostgresClient>::get_last_processed_slot::{{closure}}
   7: solana_postgres_rpc_server::request_processor::JsonRpcRequestProcessor::get_slot_with_commitment::{{closure}}
   8: <solana_postgres_rpc_server::rpc::rpc_accounts::AccountsDataImpl as solana_postgres_rpc_server::rpc::rpc_accounts::rpc_impl_AccountsData::gen_server::AccountsData>::get_program_accounts::{{closure}}
   9: <futures_util::future::future::map::Map<Fut,F> as core::future::future::Future>::poll
  10: <futures_util::future::future::map::Map<Fut,F> as core::future::future::Future>::poll
  11: <futures_util::future::either::Either<A,B> as core::future::future::Future>::poll
  12: <futures_util::future::future::map::Map<Fut,F> as core::future::future::Future>::poll
  13: <futures_util::future::future::flatten::Flatten<Fut,<Fut as core::future::future::Future>::Output> as core::future::future::Future>::poll
  14: <futures_util::future::future::Then<Fut1,Fut2,F> as core::future::future::Future>::poll
  15: <futures_util::future::future::map::Map<Fut,F> as core::future::future::Future>::poll
  16: <futures_util::future::future::map::Map<Fut,F> as core::future::future::Future>::poll
  17: <futures_util::future::future::Map<Fut,F> as core::future::future::Future>::poll
  18: <jsonrpc_http_server::handler::RpcHandler<M,S> as core::future::future::Future>::poll
  19: hyper::proto::h1::dispatch::Dispatcher<D,Bs,I,T>::poll_catch
  20: <hyper::server::conn::upgrades::UpgradeableConnection<I,S,E> as core::future::future::Future>::poll
  21: <hyper::server::server::new_svc::NewSvcTask<I,N,S,E,W> as core::future::future::Future>::poll
  22: tokio::loom::std::unsafe_cell::UnsafeCell<T>::with_mut
  23: tokio::runtime::task::core::Core<T,S>::poll
  24: tokio::runtime::task::harness::Harness<T,S>::poll
  25: tokio::runtime::scheduler::multi_thread::worker::Context::run_task
  26: tokio::runtime::scheduler::multi_thread::worker::Context::run
  27: tokio::runtime::context::scoped::Scoped<T>::set
  28: tokio::runtime::context::runtime::enter_runtime
  29: tokio::runtime::scheduler::multi_thread::worker::run
  30: tokio::runtime::task::core::Core<T,S>::poll
  31: tokio::runtime::task::harness::Harness<T,S>::poll
  32: tokio::runtime::blocking::pool::Inner::run
note: Some details are omitted, run with `RUST_BACKTRACE=full` for a verbose backtrace.

GetAccountInfo can return incorrect reads?

I believe that getAccountInfo can return incorrect/invalid reads when accounts are written to on a fork that later doesn't get confirmed.

Suppose account XXX is written to on slot 123. This will create an entry in accounts table, with slot=123 and slot=123 will have status processed. However, if slot 123 is skipped / on a fork, then when slot 124 is rooted with parent 121 the state for account XXX in the accounts table will remain until it is written to next. This means that getAccountInfo(commitment=processed) will now return a state from a fork that has been discarded.

Proposed solution:
The postgres-rpc-server should ignore any processed commitments that are on orphaned slots.

GetProgramAccounts can yield incosistent results

Since GPA is currently not fully supported by the plugin (not using account_audit table) this means it can yield incosistent result - either including accounts that shouldn't be or excluding accounts that should be present in the response.

Two examples:

1. if an account YYYY owned by XXX had a write on processed level commitment, then it would now no longer be visible in getProgramAccounts(XXX, commitment=confirmed) until that write got confirmed - so getProgramAccounts from the proxy for Postgres would not include all the program accounts necessarily

2. if there is a fork that creates an account AAAA owned by XXX on slot 123 (so there is an entry in accounts table with commitment processed) but slot 123 is skipped now it seems to me that getProgramAccounts(XXX, commitment=processed) would include the account AAAA even after slot 123 is orphaned (i.e. after 124 is confirmed with child = 122 for example).