pg-sharding / spqr Goto Github PK

Stateless Postgres Query Router.

License: Other

Makefile 0.68% Yacc 1.47% Go 86.29% Dockerfile 0.52% Shell 0.53% PLpgSQL 0.09% Gherkin 10.11% Ragel 0.28% JavaScript 0.02%

go postgresql sharding

spqr's Introduction

Stateless Postgres Query Router

PostgreSQL is awesome, but it's hard to manage a single database with some terabytes of data and 10⁵+ queries per second. Current sharding solutions focus on analytical and hybrid workloads (OLAP, HTAP). Moreover, most of those solutions do not provide a smooth path for monolith to sharded transitions, which is why Yandex Cloud's Data Platform team developed SPQR.

SPQR is a production-ready system for horizontal scaling of PostgreSQL via sharding. We appreciate any kind of feedback and contribution to the project.

For more about SPQR, please see docs/.

Main features

SPQR works well when most of your queries can be executed on one shard.

Sharding. If possible, the router tries to determine on the first transaction statement to which shard this transaction should be sent. But you can explicitly specify a shard or a sharding key in a comment request.
Transaction and session pooling. Just as in your favorite connection pooler (Odyssey or PgBouncer).
Multiple routers for fault tolerance. The router stores the sharding rules only for cache purposes. Information about the entire installation is stored inside the QDB service, so the number of routers running simultaneously is unlimited.
Liquid data migrations. Data migration between shards aims to balance the workload across shards proportionally. The main idea is to minimize any locking impact during these migrations, which is accomplished by reducing the size of the data ranges being transferred.
Limited cross-shard queries. SPQR router supports limited subset of cross-shard queries. This is made from best-effort logic in a non-disruptive and non-consistent way and is used mainly for testing purposes. Please do not use this feature in your production, cross-shard snapshot will be inconsistent.
Multiple servers and failover. In the router configuration, it is possible to specify multiple servers for one shard. Then the router will distribute read-only queries among the replicas. However, in addition to the automatic routing, you also have the option to explicitly define the destination for a specific query by using the target-session-attr parameter within the query.
Works over PostgreSQL protocol. It means you can connect to the router and the coordinator via psql to perform administrative tasks.
Dedicated read-only mode. Once enabled, the router will respond to a SHOW transaction_read_only command with "true" and handle only read-only queries, similar to a standard PostgreSQL replica.
Minor overhead for query execution. See benchmarks here and here.
Various authentication types. From basic OK and plain text to MD5 and SCRAM, see Authentication.md.
Live configuration reloading. You can send a SIGHUP signal to the router's process. This will trigger the router to reload its configuration file and apply any changes without interrupting its operation.
Statistics. You can get access to statistics in router's administrative console via SHOW command.
Fallback execution of unrouted queries to the "world shard". SPQR is optimized for single-shard OLTP queries. But we have long-term plans to support routing queries for 2 or more shards.

Development

You can use make run for a quick example using Docker. For local development, you need the latest Go version.

How to build:

make
make build

How to run:

spqr-router run --config path-to-router-config.yaml

Tests

SPQR has all types of tests: unit, regress, and end-to-end. These tests require Docker Compose, and can be run using make. For more information on testing, please see unittest, regress, and feature sections in Makefile.

License

The SPQR source code is distributed under the PostgreSQL Global Development Group License.

Chat

We have a Discord server and a Telegram chat to discuss SPQR usage and development.

spqr's People

Contributors

Stargazers

Watchers

spqr's Issues

panic: runtime error: slice bounds out of range [1390:1386]

Describe the bug

panic: runtime error: slice bounds out of range [1390:1386]

goroutine 87 [running]:
github.com/jackc/chunkreader/v2.(*ChunkReader).Next(0xc00041c940?, 0x5?)
        /Users/denchick/go/pkg/mod/github.com/jackc/chunkreader/[email protected]/chunkreader.go:61 +0x365
github.com/jackc/pgproto3/v2.(*Frontend).Receive(0xc000512400)
        /Users/denchick/go/pkg/mod/github.com/jackc/pgproto3/[email protected]/frontend.go:84 +0x128
github.com/pg-sharding/spqr/pkg/conn.(*PostgreSQLInstance).Receive(0xc000131cc0?)
        /Users/denchick/Code/spqr/pkg/conn/instance.go:73 +0x1d
github.com/pg-sharding/spqr/router/pkg/datashard.(*Conn).Receive(0x0?)
        /Users/denchick/Code/spqr/router/pkg/datashard/datashard.go:103 +0x28
github.com/pg-sharding/spqr/router/pkg/server.(*MultiShardServer).Receive.func1.1(0x0)
        /Users/denchick/Code/spqr/router/pkg/server/multishard.go:121 +0x72
created by github.com/pg-sharding/spqr/router/pkg/server.(*MultiShardServer).Receive.func1
        /Users/denchick/Code/spqr/router/pkg/server/multishard.go:119 +0xe5

To Reproduce

log_level: 'DEBUG5'
host: 'localhost'
router_port: '6432'
admin_console_port: '7432'
grpc_api_port: '7000'
router_mode: PROXY
show_notice_messages: true
init_sql: "init.sql"
frontend_tls:
  sslmode: disable
frontend_rules:
  - usr: sbtest
    db: sbtest
    pool_mode: TRANSACTION
    auth_rule:
      auth_method: ok
backend_rules:
  - usr: sbtest
    db: sbtest
    pool_discard: true
    pool_rollback: true
    auth_rule:
      auth_method: md5
      password: password
shards:
  shard01:
    db: sbtest
    usr: sbtest
    pwd: password
    type: DATA
    tls:
      sslmode: "require"
      root_cert_file: "/Users/denchick/.postgresql/root.crt"
    hosts:
      - 'sas-xhxplfw3lnnqbf5c.db.yandex.net:6432'
  shard02:
    db: sbtest
    usr: sbtest
    pwd: password
    type: DATA
    tls:
      sslmode: "require"
      root_cert_file: "/Users/denchick/.postgresql/root.crt"
    hosts:
      - 'sas-v0df2vny5r5x5u77.db.yandex.net:6432'

sysbench --threads=1 --table_size=1000 --tables=10 --pgsql-host=localhost --pgsql-port=6432 --db-driver=pgsql --pgsql-user=sbtest --pgsql-password=password --pgsql-db=sbtest --pgsql-sslmode=disable --verbosity=5 --db-debug=on --db-ps-mode=disable /usr/local/share/sysbench/oltp_read_write.lua cleanup
sysbench 1.1.0-df89d34 (using bundled LuaJIT 2.1.0-beta3)

Nothing happens when `spqr-coordinator run --unknown-parameter`

How to reproduce

Literally,

root@denchick /home/denchick # /usr/bin/spqr-coordinator run --unknown-parameter
root@denchick /home/denchick #

Expected behavior
Print an error parameter X is unknown and exit

Run coordinator as routers part

In the begging of SPQR project, the decision to have coordinator as separate unit was made.

This was not a right, but neither a wrong decision. On one hand, this helps to manage installation topology and keep routers metadata in sync, avoid data loss and routing schema corruption.

On other hand, why should we have this unit as separate binary being run by admin, robot, etc?

Maybe run this as part of router process will be better and will simplify SPQR a lot for users.

TLDR:
simply run this function from here in separate goroutine

Add uuid type support for key ranges

So, it would be useful to add UUID type to shard tables such as

create table table1
(
    place_id       integer not null references smth,
    product_nmn_id uuid    not null
)

For example, we could redirect

range from 00000000-0000-0000-0000-000000000000 to 80000000-0000-0000-0000-000000000000 to the first shard
range from 80000000-0000-0000-0000-000000000000 to ffffffff-ffff-ffff-ffff-ffffffffffff to the second shard

panic: implement me

panic: implement me

goroutine 78 [running]:
github.com/pg-sharding/spqr/router/pkg/server.(*MultiShardServer).HasPrepareStatement(0xc0005ea000?, 0xc000ac8c80?)
        /spqr/router/pkg/server/multishard.go:49 +0x27
github.com/pg-sharding/spqr/router/pkg/rulerouter.(*RelayStateImpl).PrepareStatement(0xc00112a240, 0x2?, {{0xc0012b6270?, 0x1?}, {0xc0008aa1d0?, 0xc0011280d8?}})
        /spqr/router/pkg/rulerouter/relay.go:91 +0x68
github.com/pg-sharding/spqr/router/pkg.Frontend.func1({0xdf0210, 0xc000720090}, 0xc000567ea8, {0xdee628?, 0xc00112a240?}, {0xdebcb8, 0xc0011280d8})
        /spqr/router/pkg/frontend.go:276 +0x428
github.com/pg-sharding/spqr/router/pkg.Frontend({0xdeed80?, 0xc0001cb800}, {0xdf0210, 0xc000720090}, {0xdebcb8?, 0xc0011280d8}, 0xc0003f8000)
        /spqr/router/pkg/frontend.go:291 +0x56c
github.com/pg-sharding/spqr/router/pkg.(*InstanceImpl).serv(0xc000181360, {0xdec3f8, 0xc000b0c100})
        /spqr/router/pkg/instance.go:130 +0x114
github.com/pg-sharding/spqr/router/pkg.(*InstanceImpl).Run.func3()
        /spqr/router/pkg/instance.go:163 +0x29
created by github.com/pg-sharding/spqr/router/pkg.(*InstanceImpl).Run
        /spqr/router/pkg/instance.go:162 +0x32a

Migrate tests to Docker Compose v2

It's as simpe as to replace docker-compose with docker compose

Relations List via SQL

Add view to see list of relation in dataspace to administrator console. Something like: SHOW relations [WHERE distirbution = d_id]

No config-example file

Describe the bug
In docs/router.yaml, it's said

All SPQR configurations can be written in json, yaml or toml format. See examples in config-example

However, there's no such file in the repository

To Reproduce
Steps to reproduce the behavior:

Go to https://github.com/pg-sharding/spqr/blob/master/docs/Router.md
Click on 'config-example'
Get 404 Error

Expected behavior
Get config example file

SPQR Kubernetes Operator

Currently, it is nice to have SPQR k8s operator to simplify the deployment and management of SPQR instances within Kubernetes clusters. This operator will automate the setup, scaling, and monitoring of SPQR instances in Kubernetes environments.
Feel free to add any additional information here.

More informative ERROR level logs

INFO: 2022/11/21 08:41:57.545152 match route {denchick denchick}
INFO: 2022/11/21 08:41:57.545522 match route {denchick denchick}
INFO: 2022/11/21 08:41:57.545630 match route {denchick denchick}
INFO: 2022/11/21 08:41:57.545674 match route {denchick denchick}
INFO: 2022/11/21 08:41:57.545701 match route {denchick denchick}
INFO: 2022/11/21 08:41:57.545732 match route {denchick denchick}
INFO: 2022/11/21 08:41:57.545766 match route {denchick denchick}
INFO: 2022/11/21 08:41:57.545806 match route {denchick denchick}
INFO: 2022/11/21 08:41:57.545864 match route {denchick denchick}
INFO: 2022/11/21 08:41:57.545887 match route {denchick denchick}
INFO: 2022/11/21 08:41:57.545910 match route {denchick denchick}
INFO: 2022/11/21 08:41:57.545952 match route {denchick denchick}
INFO: 2022/11/21 08:41:57.545999 match route {denchick denchick}
INFO: 2022/11/21 08:41:57.546024 match route {denchick denchick}
INFO: 2022/11/21 08:41:57.546060 match route {denchick denchick}
INFO: 2022/11/21 08:41:57.546082 match route {denchick denchick}
INFO: 2022/11/21 08:41:57.546110 match route {denchick denchick}
INFO: 2022/11/21 08:41:57.546139 match route {denchick denchick}
INFO: 2022/11/21 08:41:57.546184 match route {denchick denchick}
INFO: 2022/11/21 08:41:57.546240 match route {denchick denchick}
INFO: 2022/11/21 08:41:57.546278 match route {denchick denchick}
INFO: 2022/11/21 08:41:57.546301 match route {denchick denchick}
INFO: 2022/11/21 08:41:57.546324 match route {denchick denchick}
INFO: 2022/11/21 08:41:57.546397 match route {denchick denchick}
INFO: 2022/11/21 08:41:57.546461 match route {denchick denchick}
INFO: 2022/11/21 08:41:57.546482 match route {denchick denchick}
INFO: 2022/11/21 08:41:57.546501 match route {denchick denchick}
INFO: 2022/11/21 08:41:57.546795 match route {denchick denchick}
INFO: 2022/11/21 08:41:57.546911 match route {denchick denchick}
INFO: 2022/11/21 08:41:57.546989 match route {denchick denchick}
INFO: 2022/11/21 08:41:57.547014 match route {denchick denchick}
INFO: 2022/11/21 08:41:57.547283 match route {denchick denchick}
INFO: 2022/11/21 08:41:57.547600 match route {denchick denchick}
INFO: 2022/11/21 08:41:57.547623 match route {denchick denchick}
INFO: 2022/11/21 08:41:57.548003 match route {denchick denchick}
INFO: 2022/11/21 08:41:57.548397 match route {denchick denchick}
INFO: 2022/11/21 08:41:57.549474 match route {denchick denchick}
INFO: 2022/11/21 08:41:57.549915 match route {denchick denchick}
INFO: 2022/11/21 08:41:57.550407 match route {denchick denchick}
INFO: 2022/11/21 08:41:57.550886 match route {denchick denchick}
ERROR: 2022/11/21 08:42:07.598418 <nil>
ERROR: 2022/11/21 08:42:07.600009 <nil>
ERROR: 2022/11/21 08:42:07.600029 <nil>
ERROR: 2022/11/21 08:42:07.600098 <nil>
ERROR: 2022/11/21 08:42:07.600135 <nil>
ERROR: 2022/11/21 08:42:07.600217 <nil>
ERROR: 2022/11/21 08:42:07.600240 <nil>
ERROR: 2022/11/21 08:42:07.600104 <nil>
ERROR: 2022/11/21 08:42:07.600281 <nil>
ERROR: 2022/11/21 08:42:07.600034 <nil>
ERROR: 2022/11/21 08:42:07.600296 <nil>
ERROR: 2022/11/21 08:42:07.600298 <nil>
ERROR: 2022/11/21 08:42:07.600631 <nil>
ERROR: 2022/11/21 08:42:07.600640 <nil>
ERROR: 2022/11/21 08:42:07.600744 <nil>
ERROR: 2022/11/21 08:42:07.600754 <nil>
ERROR: 2022/11/21 08:42:07.600779 <nil>
ERROR: 2022/11/21 08:42:07.600784 <nil>
ERROR: 2022/11/21 08:42:07.600990 <nil>
ERROR: 2022/11/21 08:42:07.601042 <nil>
ERROR: 2022/11/21 08:42:07.600999 <nil>
ERROR: 2022/11/21 08:42:07.601298 <nil>
ERROR: 2022/11/21 08:42:07.601399 <nil>
ERROR: 2022/11/21 08:42:07.601595 <nil>
ERROR: 2022/11/21 08:42:07.601704 <nil>
ERROR: 2022/11/21 08:42:07.601732 <nil>
ERROR: 2022/11/21 08:42:07.601788 <nil>
ERROR: 2022/11/21 08:42:07.601866 <nil>
ERROR: 2022/11/21 08:42:07.602564 <nil>
ERROR: 2022/11/21 08:42:07.602736 <nil>
ERROR: 2022/11/21 08:42:07.603088 <nil>
ERROR: 2022/11/21 08:42:07.603506 <nil>
ERROR: 2022/11/21 08:42:07.604151 <nil>
ERROR: 2022/11/21 08:42:07.604207 <nil>
ERROR: 2022/11/21 08:42:07.604720 <nil>
ERROR: 2022/11/21 08:42:07.604978 <nil>
ERROR: 2022/11/21 08:42:07.605137 <nil>
ERROR: 2022/11/21 08:42:07.605317 <nil>
ERROR: 2022/11/21 08:42:07.605332 <nil>
ERROR: 2022/11/21 08:42:07.605414 <nil>

Small console messages

Have a look

ADD SHARDING RULE r2 COLUMNS w_id FOR DATASPACE ds2;
                                    add sharding rule                                    
-----------------------------------------------------------------------------------------
 created sharding rule r2 for table (*) with columns [w_id, hash: x->x] in ds2 dataspace
(1 row)

Why are these response messages so long? Let's make it a bit smaller, something like

ADD SHARDING RULE r2 COLUMNS w_id FOR DATASPACE ds2;
                   add sharding rule r2                                   
------------------------------------------------------------
 table (*) with columns [w_id, hash: x->x] in ds2 dataspace
(1 row)

But to be honest, it's still hard to read. What do you think?

ADD SHARDING RULE r2 COLUMNS w_id FOR DATASPACE 
  NEW SHARDING RULE
---------------------------------
 rule id   -> r2
 table     -> (*)
 columns   -> [w_id, hash: x->x]
 dataspace -> ds2
(1 row)

Any ideas?

Make FOR DISTRIBUTION optional when there is only one distribution

I've been thinking for a long time what I did wrong.

spqr-console=> CREATE KEY RANGE krid1 FROM 0 ROUTE TO stockdb-test-shard1;
ERROR:  syntax error

But I have only one distribution. Could you please decide for yourself where to create a key range, when there are no other distributions exist? It is a super popular scenario.

P.S. this works

spqr-console=> CREATE KEY RANGE krid2 FROM 1073741823 ROUTE TO stockdb-test-shard2  FOR DISTRIBUTION stockdb;
              add key range
---------------------------------------
 created key range with bound 1073741823
(1 row)

`SHOW sharding_rules;` doesn't show tables

db1=> show sharding_rules;
            listing sharding rules            
----------------------------------------------
 sharding rule r1 with column set: [id]
 sharding rule r2 with column set: [order_id]
(2 rows)

Optional Key Range ID

In the current version, you need to provide a key range ID when creating a new key range.

CREATE KEY RANGE krid1 FROM 1 ROUTE TO shard1 FOR DISTRIBUTION ds1;

There is no need to create a unique identifier for the key range, as spqr can generate one for you.

Attach several relation in one SQL-command

In the current version, you need to execute several SQL commands to attach multiple relations to the distribution. The commands are as follows:

ALTER DISTRIBUTION ds1 ATTACH RELATION a DISTRIBUTION KEY a_id;
ALTER DISTRIBUTION ds1 ATTACH RELATION b DISTRIBUTION KEY b_id HASH FUNCTION MURMUR;
ALTER DISTRIBUTION ds2 ATTACH RELATION c DISTRIBUTION KEY c_id_1 HASH FUNCTION IDENT,
                                                          c_id_2 HASH FUNCTION CITY;

It would be cool to set up this mapping in just one command. Something like this:

ALTER DISTRIBUTION ds1  ATTACH [
    (a a_id)
    (b b_id hash murmur)
    (c_id_1 hash ident c_id_2 hash city)
];

It seems strange, I agree. I'm sure it should be done in a more SQL-friendly way, maybe someone else has a better idea on how it could look.

Some small log fixes

LOG: 2022/11/18 07:27:59.380872 unrouting from datashard shard01
DEBUG1: 2022/11/18 07:27:59.380908 put connection to sas-vewvjvext6fqguug.db.yandex.net:6432 back to pool
DEBUG1: 2022/11/18 07:27:59.380925 active shards are 0x9ebee0

Cannot query using Go pgx library

Describe the bug
Cannot run query "SELECT 1" using github.com/jackc/pgx/v5 Go library

To Reproduce

Set up feature tests cluster (test/featuretests/docker-compose.yaml)
Create Go package with main.go as follows

package main

import (
	"context"
	"fmt"
	"os"

	"github.com/jackc/pgx/v5"
)

func main() {
	conn, err := pgx.Connect(context.Background(), os.Getenv("DATABASE_URL"))
	if err != nil {
		fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err)
		os.Exit(1)
	}
	defer conn.Close(context.Background())

	fmt.Println("Connection opened")

	var one int
	err = conn.QueryRow(context.Background(), "SELECT 1").Scan(&one)

	if err != nil {
		fmt.Fprintf(os.Stderr, "QueryRow failed: %v\n", err)
		os.Exit(1)
	}
	

	fmt.Printf("%v\n", one)
}

Run the following command

DATABASE_URL="user=regress host=localhost port=6432 dbname=regress" go run main.go

Program outputs Connection opened, then idles infinitely

Expected behavior
When running command on shard, it works as expected

$ DATABASE_URL="user=regress host=localhost port=7432 dbname=regress sslmode=disable connect_timeout=5" go run main.go
Connection opened
1

When query is ran via psql, it also works

$ psql -c 'SELECT 1;' -h localhost -p 6432 -U regress -d regress
NOTICE: send query to shard(s) : sh1
 ?column? 
----------
        1
(1 row)

Router logs during query

regress_router  | {"level":"info","ssl":false,"time":"2023-07-20T09:53:22Z","message":"init client connection"}
regress_router  | {"level":"debug","client":824639078400,"proto-version":196608,"time":"2023-07-20T09:53:22Z","message":"received protocol version"}
regress_router  | {"level":"debug","name":"user","value":"regress","time":"2023-07-20T09:53:22Z","message":"client param"}
regress_router  | {"level":"debug","name":"database","value":"regress","time":"2023-07-20T09:53:22Z","message":"client param"}
regress_router  | {"level":"debug","user":"regress","db":"regress","time":"2023-07-20T09:53:22Z","message":"allocate route"}
regress_router  | {"level":"info","user":"regress","db":"regress","time":"2023-07-20T09:53:22Z","message":"processing frontend auth"}
regress_router  | {"level":"debug","client":824639078400,"msg-type":"*pgproto3.AuthenticationOk","time":"2023-07-20T09:53:22Z"}
regress_router  | {"level":"info","client":"0xc00051c000","user":"regress","db":"regress","time":"2023-07-20T09:53:22Z","message":"client connection for rule accepted"}
regress_router  | {"level":"error","client":"internal","shard":"sh1","tsa":"","time":"2023-07-20T09:53:22Z","message":"acquiring new instance connection for client to shard with target session attrs"}
regress_router  | {"level":"debug","pool":824639111360,"tokens":50,"time":"2023-07-20T09:53:22Z","message":"initialized pool queue with tokens"}
regress_router  | {"level":"info","host":"spqr_shard_1:6432","ssl":false,"time":"2023-07-20T09:53:22Z","message":"instance acquire new connection"}
regress_router  | {"level":"debug","shard":"sh1","msg":{"Type":"StartupMessage","ProtocolVersion":196608,"Parameters":{"application_name":"app","client_encoding":"UTF8","database":"regress","user":"regress"}},"time":"2023-07-20T09:53:22Z","message":"shard connection startup message"}
regress_router  | {"level":"debug","shard":"sh1","msg":{"Type":"AuthenticationOK"},"sync-out":0,"time":"2023-07-20T09:53:22Z","message":"shard connection received message"}
regress_router  | {"level":"debug","shard ":824633942576,"authtype":"*pgproto3.AuthenticationOk","time":"2023-07-20T09:53:22Z","message":"auth backend"}
regress_router  | {"level":"debug","shard":"sh1","msg":{"Type":"ParameterStatus","Name":"application_name","Value":"app"},"sync-out":0,"time":"2023-07-20T09:53:22Z","message":"shard connection received message"}
regress_router  | {"level":"debug","name":"application_name","value":"app","time":"2023-07-20T09:53:22Z","message":"parameter status"}
regress_router  | {"level":"debug","shard":"sh1","msg":{"Type":"ParameterStatus","Name":"client_encoding","Value":"UTF8"},"sync-out":0,"time":"2023-07-20T09:53:22Z","message":"shard connection received message"}
regress_router  | {"level":"debug","name":"client_encoding","value":"UTF8","time":"2023-07-20T09:53:22Z","message":"parameter status"}
regress_router  | {"level":"debug","shard":"sh1","msg":{"Type":"ParameterStatus","Name":"DateStyle","Value":"ISO, MDY"},"sync-out":0,"time":"2023-07-20T09:53:22Z","message":"shard connection received message"}
regress_router  | {"level":"debug","name":"DateStyle","value":"ISO, MDY","time":"2023-07-20T09:53:22Z","message":"parameter status"}
regress_router  | {"level":"debug","shard":"sh1","msg":{"Type":"ParameterStatus","Name":"integer_datetimes","Value":"on"},"sync-out":0,"time":"2023-07-20T09:53:22Z","message":"shard connection received message"}
regress_router  | {"level":"debug","name":"integer_datetimes","value":"on","time":"2023-07-20T09:53:22Z","message":"parameter status"}
regress_router  | {"level":"debug","shard":"sh1","msg":{"Type":"ParameterStatus","Name":"IntervalStyle","Value":"postgres"},"sync-out":0,"time":"2023-07-20T09:53:22Z","message":"shard connection received message"}
regress_router  | {"level":"debug","name":"IntervalStyle","value":"postgres","time":"2023-07-20T09:53:22Z","message":"parameter status"}
regress_router  | {"level":"debug","shard":"sh1","msg":{"Type":"ParameterStatus","Name":"is_superuser","Value":"on"},"sync-out":0,"time":"2023-07-20T09:53:22Z","message":"shard connection received message"}
regress_router  | {"level":"debug","name":"is_superuser","value":"on","time":"2023-07-20T09:53:22Z","message":"parameter status"}
regress_router  | {"level":"debug","shard":"sh1","msg":{"Type":"ParameterStatus","Name":"server_encoding","Value":"SQL_ASCII"},"sync-out":0,"time":"2023-07-20T09:53:22Z","message":"shard connection received message"}
regress_router  | {"level":"debug","name":"server_encoding","value":"SQL_ASCII","time":"2023-07-20T09:53:22Z","message":"parameter status"}
regress_router  | {"level":"debug","shard":"sh1","msg":{"Type":"ParameterStatus","Name":"server_version","Value":"13.11 (Ubuntu 13.11-1.pgdg20.04+1)"},"sync-out":0,"time":"2023-07-20T09:53:22Z","message":"shard connection received message"}
regress_router  | {"level":"debug","name":"server_version","value":"13.11 (Ubuntu 13.11-1.pgdg20.04+1)","time":"2023-07-20T09:53:22Z","message":"parameter status"}
regress_router  | {"level":"debug","shard":"sh1","msg":{"Type":"ParameterStatus","Name":"session_authorization","Value":"regress"},"sync-out":0,"time":"2023-07-20T09:53:22Z","message":"shard connection received message"}
regress_router  | {"level":"debug","name":"session_authorization","value":"regress","time":"2023-07-20T09:53:22Z","message":"parameter status"}
regress_router  | {"level":"debug","shard":"sh1","msg":{"Type":"ParameterStatus","Name":"standard_conforming_strings","Value":"on"},"sync-out":0,"time":"2023-07-20T09:53:22Z","message":"shard connection received message"}
regress_router  | {"level":"debug","name":"standard_conforming_strings","value":"on","time":"2023-07-20T09:53:22Z","message":"parameter status"}
regress_router  | {"level":"debug","shard":"sh1","msg":{"Type":"ParameterStatus","Name":"TimeZone","Value":"EUROPE0"},"sync-out":0,"time":"2023-07-20T09:53:22Z","message":"shard connection received message"}
regress_router  | {"level":"debug","name":"TimeZone","value":"EUROPE0","time":"2023-07-20T09:53:22Z","message":"parameter status"}
regress_router  | {"level":"debug","shard":"sh1","msg":{"Type":"BackendKeyData","ProcessID":75,"SecretKey":841826651},"sync-out":0,"time":"2023-07-20T09:53:22Z","message":"shard connection received message"}
regress_router  | {"level":"debug","process-id":75,"secret-key":841826651,"time":"2023-07-20T09:53:22Z","message":"backend key data"}
regress_router  | {"level":"debug","shard":"sh1","msg":{"Type":"ReadyForQuery","TxStatus":"I"},"sync-out":1,"time":"2023-07-20T09:53:22Z","message":"shard connection received message"}
regress_router  | {"level":"debug","shard":"sh1","host":"spqr_shard_1:6432","time":"2023-07-20T09:53:22Z","message":"put connection back to pool"}
regress_router  | {"level":"debug","client":824639078400,"msg-type":"*pgproto3.ParameterStatus","time":"2023-07-20T09:53:22Z"}
regress_router  | {"level":"debug","client":824639078400,"msg-type":"*pgproto3.ParameterStatus","time":"2023-07-20T09:53:22Z"}
regress_router  | {"level":"debug","client":824639078400,"msg-type":"*pgproto3.ParameterStatus","time":"2023-07-20T09:53:22Z"}
regress_router  | {"level":"debug","client":824639078400,"msg-type":"*pgproto3.ParameterStatus","time":"2023-07-20T09:53:22Z"}
regress_router  | {"level":"debug","client":824639078400,"msg-type":"*pgproto3.ParameterStatus","time":"2023-07-20T09:53:22Z"}
regress_router  | {"level":"debug","client":824639078400,"msg-type":"*pgproto3.ParameterStatus","time":"2023-07-20T09:53:22Z"}
regress_router  | {"level":"debug","client":824639078400,"msg-type":"*pgproto3.ParameterStatus","time":"2023-07-20T09:53:22Z"}
regress_router  | {"level":"debug","client":824639078400,"msg-type":"*pgproto3.ParameterStatus","time":"2023-07-20T09:53:22Z"}
regress_router  | {"level":"debug","client":824639078400,"msg-type":"*pgproto3.ParameterStatus","time":"2023-07-20T09:53:22Z"}
regress_router  | {"level":"debug","client":824639078400,"msg-type":"*pgproto3.ParameterStatus","time":"2023-07-20T09:53:22Z"}
regress_router  | {"level":"debug","client":824639078400,"msg-type":"*pgproto3.ParameterStatus","time":"2023-07-20T09:53:22Z"}
regress_router  | {"level":"debug","client":824639078400,"msg-type":"*pgproto3.BackendKeyData","time":"2023-07-20T09:53:22Z"}
regress_router  | {"level":"debug","client":824639078400,"msg-type":"*pgproto3.ReadyForQuery","time":"2023-07-20T09:53:22Z"}
regress_router  | {"level":"debug","client":824639078400,"time":"2023-07-20T09:53:22Z","message":"client auth succeeded"}
regress_router  | {"level":"debug","client":824639078400,"time":"2023-07-20T09:53:22Z","message":"prerouting phase succeeded"}
regress_router  | {"level":"info","user":"regress","db":"regress","client":824639078400,"time":"2023-07-20T09:53:22Z","message":"process frontend for route"}
regress_router  | {"level":"debug","client":824639078400,"message":{"Type":"Parse","Name":"stmtcache_1","Query":"SELECT 1","ParameterOIDs":null},"time":"2023-07-20T09:53:22Z","message":"client received message"}
regress_router  | {"level":"debug","stmt-type":"*lyx.Select","time":"2023-07-20T09:53:22Z","message":"parsed query statements"}
regress_router  | {"level":"debug","client":824639078400,"message-type":"*pgproto3.Parse","time":"2023-07-20T09:53:22Z","message":"client relay: adding message to message buffer"}
regress_router  | {"level":"debug","client":824639078400,"user":"regress","db":"regress","time":"2023-07-20T09:53:22Z","message":"reparing relay step for client"}
regress_router  | {"level":"debug","client":824639078400,"statement":{"FromClause":null,"Where":{},"TargetList":[{"Value":"1"}]},"time":"2023-07-20T09:53:22Z","message":"rerouting client"}
regress_router  | {"level":"debug","time":"2023-07-20T09:53:22Z","message":"memqdb: list all key ranges"}
regress_router  | {"level":"debug","time":"2023-07-20T09:53:22Z","message":"memqdb: list sharding rules"}
regress_router  | {"level":"debug","time":"2023-07-20T09:53:22Z","message":"memqdb: list shards"}
regress_router  | {"level":"debug","user":"regress","db":"regress","client":824639078400,"time":"2023-07-20T09:53:22Z","message":"route client to datashard"}
regress_router  | {"level":"debug","client":824639078400,"msg-type":"*pgproto3.NoticeResponse","time":"2023-07-20T09:53:22Z"}
regress_router  | {"level":"debug","client tsa":"read-write","time":"2023-07-20T09:53:22Z","message":"adding shard with tsa"}
regress_router  | {"level":"error","client":"0xc00051c000","shard":"sh1","tsa":"read-write","time":"2023-07-20T09:53:22Z","message":"acquiring new instance connection for client to shard with target session attrs"}
regress_router  | {"level":"debug","client":"0xc00051c000","shard":"sh1","host":"spqr_shard_1:6432","time":"2023-07-20T09:53:22Z","message":"connection pool for client: reuse cached shard connection to instance"}
regress_router  | {"level":"debug","shard":"sh1","query":{"Type":"Query","String":"SHOW transaction_read_only"},"sync-in":2,"time":"2023-07-20T09:53:22Z","message":"shard connection send message"}
regress_router  | {"level":"debug","shard":"sh1","msg":{"Type":"RowDescription","Fields":[{"Name":"transaction_read_only","TableOID":0,"TableAttributeNumber":0,"DataTypeOID":25,"DataTypeSize":-1,"TypeModifier":-1,"Format":0}]},"sync-out":1,"time":"2023-07-20T09:53:22Z","message":"shard connection received message"}
regress_router  | {"level":"debug","shard":"sh1","message":{"Type":"RowDescription","Fields":[{"Name":"transaction_read_only","TableOID":0,"TableAttributeNumber":0,"DataTypeOID":25,"DataTypeSize":-1,"TypeModifier":-1,"Format":0}]},"time":"2023-07-20T09:53:22Z","message":"shard recieved error during check rw"}
regress_router  | {"level":"debug","shard":"sh1","msg":{"Type":"DataRow","Values":[{"text":"off"}]},"sync-out":1,"time":"2023-07-20T09:53:22Z","message":"shard connection received message"}
regress_router  | {"level":"debug","shard":"sh1","message":{"Type":"DataRow","Values":[{"text":"off"}]},"time":"2023-07-20T09:53:22Z","message":"shard recieved error during check rw"}
regress_router  | {"level":"debug","shard":"sh1","datarow":{"Type":"DataRow","Values":[{"text":"off"}]},"time":"2023-07-20T09:53:22Z","message":"shard checking read-write"}
regress_router  | {"level":"debug","shard":"sh1","msg":{"Type":"CommandComplete","CommandTag":"SHOW"},"sync-out":1,"time":"2023-07-20T09:53:22Z","message":"shard connection received message"}
regress_router  | {"level":"debug","shard":"sh1","message":{"Type":"CommandComplete","CommandTag":"SHOW"},"time":"2023-07-20T09:53:22Z","message":"shard recieved error during check rw"}
regress_router  | {"level":"debug","shard":"sh1","msg":{"Type":"ReadyForQuery","TxStatus":"I"},"sync-out":2,"time":"2023-07-20T09:53:22Z","message":"shard connection received message"}
regress_router  | {"level":"debug","shard":"sh1","message":{"Type":"ReadyForQuery","TxStatus":"I"},"time":"2023-07-20T09:53:22Z","message":"shard recieved error during check rw"}
regress_router  | {"level":"debug","shard":"sh1","result":true,"time":"2023-07-20T09:53:22Z","message":"shard calculated RW result"}
regress_router  | {"level":"debug","client":824639078400,"time":"2023-07-20T09:53:22Z","message":"flushing message buffer"}
regress_router  | {"level":"debug","waitForResp":true,"replyCl":true,"time":"2023-07-20T09:53:22Z","message":"flushing"}
regress_router  | {"level":"debug","server":"sh1","query-type":"*pgproto3.Parse","time":"2023-07-20T09:53:22Z","message":"client process query"}
regress_router  | {"level":"debug","single-shard":824634934528,"query-type":"*pgproto3.Parse","time":"2023-07-20T09:53:22Z","message":"single-shard sending msg to server"}
regress_router  | {"level":"debug","shard":"sh1","query":{"Type":"Parse","Name":"stmtcache_1","Query":"SELECT 1","ParameterOIDs":null},"sync-in":3,"time":"2023-07-20T09:53:22Z","message":"shard connection send message"}

Change error message when happend routing error

When router can not route request, a parsing error message is sent to the interface
ERROR: error processing query 'SELECT * FROM xx WHERE w_id = 5;': too complex query to parse

Fix spqrdump

It currently doesn't work as intended as it doesn't dump distributions

Unknown bug with prepared statements

Some logs

DEBUG3: 2022/11/18 07:33:43.316073 recv msg from server *pgproto3.RowDescription
DEBUG2: 2022/11/18 07:33:43.316081 got msg type from server: *pgproto3.RowDescription
DEBUG3: 2022/11/18 07:33:43.316085 sending *pgproto3.RowDescription to client
DEBUG3: 2022/11/18 07:33:43.316106 recv msg from server *pgproto3.DataRow
DEBUG2: 2022/11/18 07:33:43.316111 got msg type from server: *pgproto3.DataRow
DEBUG3: 2022/11/18 07:33:43.316115 sending *pgproto3.DataRow to client
DEBUG3: 2022/11/18 07:33:43.316129 recv msg from server *pgproto3.CommandComplete
DEBUG2: 2022/11/18 07:33:43.316134 got msg type from server: *pgproto3.CommandComplete
DEBUG3: 2022/11/18 07:33:43.316138 sending *pgproto3.CommandComplete to client
DEBUG3: 2022/11/18 07:33:43.316150 recv msg from server *pgproto3.ReadyForQuery
DEBUG1: 2022/11/18 07:33:43.316155 complete relay iter with TX active
DEBUG3: 2022/11/18 07:33:43.316160 sending *pgproto3.ReadyForQuery to client
DEBUG1: 2022/11/18 07:33:43.316172 active shards are 0x9ebee0
DEBUG3: 2022/11/18 07:33:43.316187 Received *pgproto3.Query from client
DEBUG1: 2022/11/18 07:33:43.316193 received *pgproto3.Query msg, 0xc0004bca38
DEBUG1: 2022/11/18 07:33:43.316200 received query SELECT c FROM sbtest1 WHERE id=5138
DEBUG2: 2022/11/18 07:33:43.316251 parsed query stmt is *pg_query.ParseResult
DEBUG2: 2022/11/18 07:33:43.316314 [stmt:{select_stmt:{target_list:{res_target:{val:{column_ref:{fields:{string:{str:"c"}}  location:7}}  location:7}}  from_clause:{range_var:{relname:"sbtest1"  inh:true  relpersistence:"p"  location:14}}  where_clause:{a_expr:{kind:AEXPR_OP  name:{string:{str:"="}}  lexpr:{column_ref:{fields:{string:{str:"id"}}  location:28}}  rexpr:{a_const:{val:{integer:{ival:5138}}  location:31}}  location:30}}  limit_option:LIMIT_OPTION_DEFAULT  op:SETOP_NONE}}]
DEBUG1: 2022/11/18 07:33:43.316324 adding pgproto3.Query
DEBUG1: 2022/11/18 07:33:43.316329 preparing relay step for denchick denchick
DEBUG1: 2022/11/18 07:33:43.316333 flush message buff
DEBUG1: 2022/11/18 07:33:43.316339 flushing {String:SELECT c FROM sbtest1 WHERE id=5138} waitForResp: true replyCl: true
DEBUG2: 2022/11/18 07:33:43.316345 process query &{SELECT c FROM sbtest1 WHERE id=5138}
DEBUG3: 2022/11/18 07:33:43.316618 recv msg from server *pgproto3.RowDescription
DEBUG2: 2022/11/18 07:33:43.316632 got msg type from server: *pgproto3.RowDescription
DEBUG3: 2022/11/18 07:33:43.316639 sending *pgproto3.RowDescription to client
DEBUG3: 2022/11/18 07:33:43.316666 recv msg from server *pgproto3.DataRow
DEBUG2: 2022/11/18 07:33:43.316672 got msg type from server: *pgproto3.DataRow
DEBUG3: 2022/11/18 07:33:43.316678 sending *pgproto3.DataRow to client
DEBUG3: 2022/11/18 07:33:43.316694 recv msg from server *pgproto3.CommandComplete
DEBUG2: 2022/11/18 07:33:43.316699 got msg type from server: *pgproto3.CommandComplete
DEBUG3: 2022/11/18 07:33:43.316704 sending *pgproto3.CommandComplete to client
DEBUG3: 2022/11/18 07:33:43.316708 send msg to server &{String:SELECT c FROM sbtest1 WHERE id=5138}
DEBUG3: 2022/11/18 07:33:43.316721 recv msg from server *pgproto3.ReadyForQuery
DEBUG1: 2022/11/18 07:33:43.316769 complete relay iter with TX active
DEBUG3: 2022/11/18 07:33:43.316780 sending *pgproto3.ReadyForQuery to client
DEBUG1: 2022/11/18 07:33:43.316819 active shards are 0x9ebee0
DEBUG3: 2022/11/18 07:33:43.316890 Received *pgproto3.Query from client
DEBUG1: 2022/11/18 07:33:43.316900 received *pgproto3.Query msg, 0xc0004bc878
DEBUG1: 2022/11/18 07:33:43.316909 received query COMMIT
DEBUG2: 2022/11/18 07:33:43.316943 parsed query stmt is *pg_query.ParseResult
DEBUG3: 2022/11/18 07:33:43.317033 recv msg from server *pgproto3.RowDescription
DEBUG2: 2022/11/18 07:33:43.317046 got msg type from server: *pgproto3.RowDescription
DEBUG3: 2022/11/18 07:33:43.317055 sending *pgproto3.RowDescription to client
DEBUG3: 2022/11/18 07:33:43.317091 recv msg from server *pgproto3.DataRow
DEBUG2: 2022/11/18 07:33:43.317098 got msg type from server: *pgproto3.DataRow
DEBUG3: 2022/11/18 07:33:43.317103 sending *pgproto3.DataRow to client
DEBUG3: 2022/11/18 07:33:43.317131 recv msg from server *pgproto3.CommandComplete
DEBUG2: 2022/11/18 07:33:43.317138 got msg type from server: *pgproto3.CommandComplete
DEBUG3: 2022/11/18 07:33:43.317146 sending *pgproto3.CommandComplete to client
DEBUG2: 2022/11/18 07:33:43.317140 [stmt:{transaction_stmt:{kind:TRANS_STMT_COMMIT}}]
DEBUG1: 2022/11/18 07:33:43.317210 adding pgproto3.Query
DEBUG1: 2022/11/18 07:33:43.317220 preparing relay step for denchick denchick
DEBUG1: 2022/11/18 07:33:43.317226 flush message buff
DEBUG1: 2022/11/18 07:33:43.317238 flushing {String:COMMIT} waitForResp: true replyCl: true
DEBUG2: 2022/11/18 07:33:43.317245 process query &{COMMIT}
DEBUG3: 2022/11/18 07:33:43.317194 recv msg from server *pgproto3.ReadyForQuery
DEBUG1: 2022/11/18 07:33:43.317261 complete relay iter with TX active
DEBUG3: 2022/11/18 07:33:43.317266 sending *pgproto3.ReadyForQuery to client
DEBUG3: 2022/11/18 07:33:43.317254 send msg to server &{String:COMMIT}
DEBUG1: 2022/11/18 07:33:43.317291 active shards are 0x9ebee0
DEBUG3: 2022/11/18 07:33:43.317521 Received *pgproto3.Query from client
DEBUG1: 2022/11/18 07:33:43.317528 received *pgproto3.Query msg, 0xc0004bca38
DEBUG1: 2022/11/18 07:33:43.317534 received query SELECT c FROM sbtest1 WHERE id=1958
DEBUG2: 2022/11/18 07:33:43.317652 parsed query stmt is *pg_query.ParseResult
DEBUG2: 2022/11/18 07:33:43.317737 [stmt:{select_stmt:{target_list:{res_target:{val:{column_ref:{fields:{string:{str:"c"}}  location:7}}  location:7}}  from_clause:{range_var:{relname:"sbtest1"  inh:true  relpersistence:"p"  location:14}}  where_clause:{a_expr:{kind:AEXPR_OP  name:{string:{str:"="}}  lexpr:{column_ref:{fields:{string:{str:"id"}}  location:28}}  rexpr:{a_const:{val:{integer:{ival:1958}}  location:31}}  location:30}}  limit_option:LIMIT_OPTION_DEFAULT  op:SETOP_NONE}}]
DEBUG1: 2022/11/18 07:33:43.317748 adding pgproto3.Query
DEBUG1: 2022/11/18 07:33:43.317753 preparing relay step for denchick denchick
DEBUG1: 2022/11/18 07:33:43.317758 flush message buff
DEBUG1: 2022/11/18 07:33:43.317765 flushing {String:SELECT c FROM sbtest1 WHERE id=1958} waitForResp: true replyCl: true
DEBUG3: 2022/11/18 07:33:43.317767 recv msg from server *pgproto3.CommandComplete
DEBUG2: 2022/11/18 07:33:43.317772 process query &{SELECT c FROM sbtest1 WHERE id=1958}
DEBUG3: 2022/11/18 07:33:43.317798 send msg to server &{String:SELECT c FROM sbtest1 WHERE id=1958}
DEBUG2: 2022/11/18 07:33:43.317784 got msg type from server: *pgproto3.CommandComplete
DEBUG3: 2022/11/18 07:33:43.317848 sending *pgproto3.CommandComplete to client
DEBUG3: 2022/11/18 07:33:43.317868 recv msg from server *pgproto3.ReadyForQuery
DEBUG1: 2022/11/18 07:33:43.317875 complete relay iter with TX idle
DEBUG3: 2022/11/18 07:33:43.317880 sending *pgproto3.ReadyForQuery to client
LOG: 2022/11/18 07:33:43.317898 end of tx unrouting from [{shard01 false}]
LOG: 2022/11/18 07:33:43.317903 unrouting from datashard shard01
DEBUG1: 2022/11/18 07:33:43.317912 put connection to sas-vewvjvext6fqguug.db.yandex.net:6432 back to pool
DEBUG1: 2022/11/18 07:33:43.317920 active shards are 0x9ebee0
DEBUG3: 2022/11/18 07:33:43.317959 Received *pgproto3.Query from client
DEBUG1: 2022/11/18 07:33:43.317968 received *pgproto3.Query msg, 0xc0004bc878
DEBUG1: 2022/11/18 07:33:43.317977 received query BEGIN
DEBUG2: 2022/11/18 07:33:43.318010 parsed query stmt is *pg_query.ParseResult
DEBUG2: 2022/11/18 07:33:43.318040 [stmt:{transaction_stmt:{kind:TRANS_STMT_BEGIN}}]
DEBUG1: 2022/11/18 07:33:43.318049 adding silent pgproto3.Query
DEBUG4: 2022/11/18 07:33:43.318056 start new params set
DEBUG3: 2022/11/18 07:33:43.318067 sending *pgproto3.CommandComplete to client
DEBUG3: 2022/11/18 07:33:43.318090 sending *pgproto3.ReadyForQuery to client
DEBUG3: 2022/11/18 07:33:43.318125 recv msg from server *pgproto3.RowDescription
DEBUG2: 2022/11/18 07:33:43.318134 got msg type from server: *pgproto3.RowDescription
DEBUG3: 2022/11/18 07:33:43.318140 sending *pgproto3.RowDescription to client
DEBUG3: 2022/11/18 07:33:43.318163 recv msg from server *pgproto3.DataRow
DEBUG2: 2022/11/18 07:33:43.318168 got msg type from server: *pgproto3.DataRow
DEBUG3: 2022/11/18 07:33:43.318174 sending *pgproto3.DataRow to client
DEBUG3: 2022/11/18 07:33:43.318194 recv msg from server *pgproto3.CommandComplete
DEBUG2: 2022/11/18 07:33:43.318200 got msg type from server: *pgproto3.CommandComplete
DEBUG3: 2022/11/18 07:33:43.318205 sending *pgproto3.CommandComplete to client
DEBUG3: 2022/11/18 07:33:43.318223 recv msg from server *pgproto3.ReadyForQuery
DEBUG1: 2022/11/18 07:33:43.318229 complete relay iter with TX active
DEBUG3: 2022/11/18 07:33:43.318233 sending *pgproto3.ReadyForQuery to client
DEBUG1: 2022/11/18 07:33:43.318247 active shards are 0x9ebee0
DEBUG3: 2022/11/18 07:33:43.318251 Received *pgproto3.Query from client
DEBUG1: 2022/11/18 07:33:43.318264 received *pgproto3.Query msg, 0xc0004bc878
DEBUG1: 2022/11/18 07:33:43.318271 received query SELECT c FROM sbtest1 WHERE id=5968
DEBUG3: 2022/11/18 07:33:43.318304 Received *pgproto3.Query from client
DEBUG1: 2022/11/18 07:33:43.318311 received *pgproto3.Query msg, 0xc0004bca38
DEBUG1: 2022/11/18 07:33:43.318316 received query COMMIT
DEBUG2: 2022/11/18 07:33:43.318340 parsed query stmt is *pg_query.ParseResult
DEBUG2: 2022/11/18 07:33:43.318367 [stmt:{transaction_stmt:{kind:TRANS_STMT_COMMIT}}]
DEBUG1: 2022/11/18 07:33:43.318374 adding pgproto3.Query
DEBUG1: 2022/11/18 07:33:43.318379 preparing relay step for denchick denchick
DEBUG1: 2022/11/18 07:33:43.318383 flush message buff
DEBUG1: 2022/11/18 07:33:43.318390 flushing {String:COMMIT} waitForResp: true replyCl: true
DEBUG2: 2022/11/18 07:33:43.318396 process query &{COMMIT}
DEBUG3: 2022/11/18 07:33:43.318402 send msg to server &{String:COMMIT}
DEBUG2: 2022/11/18 07:33:43.318340 parsed query stmt is *pg_query.ParseResult
DEBUG2: 2022/11/18 07:33:43.318500 [stmt:{select_stmt:{target_list:{res_target:{val:{column_ref:{fields:{string:{str:"c"}}  location:7}}  location:7}}  from_clause:{range_var:{relname:"sbtest1"  inh:true  relpersistence:"p"  location:14}}  where_clause:{a_expr:{kind:AEXPR_OP  name:{string:{str:"="}}  lexpr:{column_ref:{fields:{string:{str:"id"}}  location:28}}  rexpr:{a_const:{val:{integer:{ival:5968}}  location:31}}  location:30}}  limit_option:LIMIT_OPTION_DEFAULT  op:SETOP_NONE}}]
DEBUG1: 2022/11/18 07:33:43.318510 adding pgproto3.Query
DEBUG1: 2022/11/18 07:33:43.318515 preparing relay step for denchick denchick
DEBUG5: 2022/11/18 07:33:43.318552 mathcing qstmt *pg_query.Node_TransactionStmt
DEBUG3: 2022/11/18 07:33:43.318569 sending *pgproto3.ErrorResponse to client
DEBUG3: 2022/11/18 07:33:43.318591 sending *pgproto3.ReadyForQuery to client
DEBUG5: 2022/11/18 07:33:43.318606 client iter done with too complex query to parse
DEBUG3: 2022/11/18 07:33:43.318592 recv msg from server *pgproto3.CommandComplete
DEBUG2: 2022/11/18 07:33:43.318620 got msg type from server: *pgproto3.CommandComplete
DEBUG3: 2022/11/18 07:33:43.318624 sending *pgproto3.CommandComplete to client
DEBUG3: 2022/11/18 07:33:43.318640 recv msg from server *pgproto3.ReadyForQuery
DEBUG1: 2022/11/18 07:33:43.318645 complete relay iter with TX idle
DEBUG3: 2022/11/18 07:33:43.318650 sending *pgproto3.ReadyForQuery to client
LOG: 2022/11/18 07:33:43.318664 end of tx unrouting from [{shard01 false}]
LOG: 2022/11/18 07:33:43.318669 unrouting from datashard shard01
DEBUG1: 2022/11/18 07:33:43.318675 put connection to sas-vewvjvext6fqguug.db.yandex.net:6432 back to pool
DEBUG1: 2022/11/18 07:33:43.318681 active shards are 0x9ebee0
DEBUG3: 2022/11/18 07:33:43.325298 Received <nil> from client
DEBUG3: 2022/11/18 07:33:43.325371 Received <nil> from client
DEBUG3: 2022/11/18 07:33:43.325388 sending *pgproto3.ErrorResponse to client
DEBUG1: 2022/11/18 07:33:43.325417 received <nil> msg, %!p(<nil>)
DEBUG3: 2022/11/18 07:33:43.325425 Received <nil> from client

There is no console user validation

You can connect to the console as anybody and not understand that you are doing something wrong.

➜  ~ psql "host=localhost sslmode=disable user=username dbname=dbname port=7432"

		SQPR router admin console
	Here you can configure your routing rules
------------------------------------------------
	You can find documentation here
https://github.com/pg-sharding/spqr/tree/master/docs

psql (14.5 (Homebrew), server console)
Type "help" for help.

dbname=> SHOW SHARDS;
ERROR:  unknown coordinator cmd

What I should do after getting `multishard state is out of sync`?

DEBUG4: 2022/11/18 06:22:36.689840 sending &{String:DROP TABLE IF EXISTS sbtest1} to sh shard02
DEBUG2: 2022/11/18 06:22:36.690048 multishard server init: got *pgproto3.NoticeResponse msg from shard01 shard
DEBUG1: 2022/11/18 06:22:36.690075 complete relay iter with TX idle
DEBUG1: 2022/11/18 06:22:36.690080 unroute multishard route
DEBUG3: 2022/11/18 06:22:36.690090 sending *pgproto3.ReadyForQuery to client
LOG: 2022/11/18 06:22:36.690123 end of tx unrouting from [{shard01 true} {shard02 true}]
LOG: 2022/11/18 06:22:36.690129 unrouting from datashard shard01
LOG: 2022/11/18 06:22:36.690134 unrouting from datashard shard02
DEBUG5: 2022/11/18 06:22:36.690140 client iter done with multishard state is out of sync
LOG: 2022/11/18 06:22:36.690442 multishard server: got *pgproto3.CommandComplete message from shard01 shard while rollback after error
DEBUG3: 2022/11/18 06:22:36.696279 Received <nil> from client

Crash on lock cancel

Describe the bug
Router crash when user try to Ctrl+C LOCK KEY RANGE

To Reproduce
Steps to reproduce the behavior:

Start router

./spqr-router -c examples/2shardproxy.yaml run

Run spqr-console:

nikifkon@ttt:~/spqr$ psql "host=localhost port=7432"

                SQPR router admin console
        Here you can configure your routing rules
------------------------------------------------
        You can find documentation here 
https://github.com/pg-sharding/spqr/tree/master/docs

psql (14.8 (Ubuntu 14.8-0ubuntu0.22.04.1), server console)
Type "help" for help.

nikifkon=> show key_ranges;
 Key range ID | Shard ID | Lower bound | Upper bound 
--------------+----------+-------------+-------------
 krid1        | sh1      | 1           | 10
 krid2        | sh1      | 11          | 20
 krid3        | sh2      | 21          | 30
(3 rows)

nikifkon=?> lock key range krid1;
        lock key range        
------------------------------
 lock key range with id krid1
(1 row)

nikifkon=> lock key range krid1;
^CCancel request sent
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!?>

See error:

"level":"info","ssl":false,"time":"2023-08-01T12:22:11Z","message":"init client connection"}
{"level":"info","time":"2023-08-01T12:22:11Z","message":"console.ProcClient start"}
{"level":"info","ssl":false,"time":"2023-08-01T12:22:39Z","message":"init client connection"}                 
panic: runtime error: invalid memory address or nil pointer dereference                                       
[signal SIGSEGV: segmentation violation code=0x1 addr=0x28 pc=0x79b0a7]                                       
                                                                                                              
goroutine 52 [running]:                                
github.com/jackc/pgx/v5/pgproto3.(*Backend).Send(0x0, {0xf53f80, 0x151ce48})
        /home/nikifkon/spqr/vendor/github.com/jackc/pgx/v5/pgproto3/backend.go:60 +0x27
github.com/pg-sharding/spqr/router/client.(*PsqlClient).Send(0xc0000a4680, {0xf53f80?, 0x151ce48})
        /home/nikifkon/spqr/router/client/client.go:759 +0xba
github.com/pg-sharding/spqr/router/console.(*Local).Serve(0xc0000980a0?, {0xf58278, 0xc000130c30}, {0xf63d28, 
0xc0000a4680})                                         
        /home/nikifkon/spqr/router/console/console.go:99 +0x1e6
github.com/pg-sharding/spqr/router.(*InstanceImpl).servAdm(0xc0000bc000, {0xf58278, 0xc000130c30}, {0xf5b870?,
 0xc00012a040?})                                       
        /home/nikifkon/spqr/router/instance.go:217 +0x98
github.com/pg-sharding/spqr/router.(*InstanceImpl).RunAdm.func2()
        /home/nikifkon/spqr/router/instance.go:245 +0x2d                                                      
created by github.com/pg-sharding/spqr/router.(*InstanceImpl).RunAdm
        /home/nikifkon/spqr/router/instance.go:244 +0xe5

Expected behavior
router don't crush

Desktop (please complete the following information):

OS: Linux version 5.15.0-76-generic (buildd@lcy02-amd64-028) (gcc (Ubuntu 11.3.0-1ubuntu1~22.04.1) 11.3.0, GNU ld (GNU Binutils for Ubuntu) 2.38) #83-Ubuntu SMP Thu Jun 15 19:16:32 UTC 2023
Version master (d63fcd5)

support queries from sysbench default tests

When I tried to benchmark SPQR router with sysbench I found some queries are not supported yet.
I think it would be cool if SPQR could pass default benchmark tests.

panic: conn.AuthBackend

LOG: 2022/11/17 10:58:28.788666 acquire new connection to sas-m9tf7zhglbs4y4vu.db.yandex.net:6432
panic: runtime error: invalid memory address or nil pointer dereference
[signal SIGSEGV: segmentation violation code=0x1 addr=0x10 pc=0x9c0aee]

goroutine 62 [running]:
github.com/pg-sharding/spqr/pkg/conn.AuthBackend({0xdec1e8, 0xc0004281c0}, 0xc0002fd200, {0xde7988?, 0xc000400420?})
        /spqr/pkg/conn/auth.go:22 +0x18e
github.com/pg-sharding/spqr/router/pkg/datashard.(*Conn).Auth(0xc00043c460, 0xc000412960?)
        /spqr/router/pkg/datashard/datashard.go:161 +0x4c7
github.com/pg-sharding/spqr/router/pkg/datashard.NewShard({{0xc0003c2aa5?, 0xc0002fd500?}, 0xa5?}, {0xdec1e8?, 0xc0004281c0}, 0xc0003d4700, 0xc0002fd200)
        /spqr/router/pkg/datashard/datashard.go:137 +0x313
github.com/pg-sharding/spqr/router/pkg/datashard.NewConnPool.func1({{0xc0003c2aa5?, 0xc0000d68f0?}, 0x60?}, {0xc00003c660, 0x27}, 0x569740?)
        /spqr/router/pkg/datashard/conn_pool.go:281 +0x18b
github.com/pg-sharding/spqr/router/pkg/datashard.(*cPool).Connection(0xc000426030, {{0xc0003c2aa5?, 0xc0003c2aa5?}, 0x7?}, {0xc00003c660, 0x27}, 0x503324?)
        /spqr/router/pkg/datashard/conn_pool.go:84 +0x214
github.com/pg-sharding/spqr/router/pkg/datashard.(*InstancePoolImpl).Connection(0xc0003d4900, {{0xc0003c2aa5?, 0xc0003c2070?}, 0x9?}, 0x10?)
        /spqr/router/pkg/datashard/conn_pool.go:211 +0x209
github.com/pg-sharding/spqr/router/pkg/route.(*Route).Params(0xc00012a000)
        /spqr/router/pkg/route/route.go:81 +0x153
github.com/pg-sharding/spqr/router/pkg/client.(*PsqlClient).Auth(0xc0001a6090, 0xc0003c2018?)
        /spqr/router/pkg/client/client.go:513 +0x2ea
github.com/pg-sharding/spqr/router/pkg/rulerouter.(*RuleRouterImpl).PreRoute(0xc000428000, {0xdec3f8?, 0xc000010008})
        /spqr/router/pkg/rulerouter/rulerouter.go:175 +0x42c
github.com/pg-sharding/spqr/router/pkg.(*InstanceImpl).serv(0xc000410320, {0xdec3f8, 0xc000010008})
        /spqr/router/pkg/instance.go:117 +0x3d
github.com/pg-sharding/spqr/router/pkg.(*InstanceImpl).Run.func3()
        /spqr/router/pkg/instance.go:163 +0x29
created by github.com/pg-sharding/spqr/router/pkg.(*InstanceImpl).Run
        /spqr/router/pkg/instance.go:162 +0x32a

panic: runtime error: invalid memory address or nil pointer dereference

Describe the bug

panic: runtime error: invalid memory address or nil pointer dereference
[signal SIGSEGV: segmentation violation code=0x1 addr=0x0 pc=0x45a927d]

goroutine 40 [running]:
github.com/pg-sharding/spqr/router/pkg/parser.(*QParser).Stmt(0xc000126058)
        /Users/denchick/Code/spqr/router/pkg/parser/qparser.go:23 +0x1d
github.com/pg-sharding/spqr/router/pkg/qrouter.(*ProxyQrouter).Route(0xc000126000, {0x4a10790, 0xc00003c0c8})
        /Users/denchick/Code/spqr/router/pkg/qrouter/proxy_routing.go:311 +0x4c
github.com/pg-sharding/spqr/router/pkg/rulerouter.(*RelayStateImpl).Reroute(0xc0004e4240)
        /Users/denchick/Code/spqr/router/pkg/rulerouter/relay.go:214 +0x1aa
github.com/pg-sharding/spqr/router/pkg/rulerouter.(*RelayStateImpl).PrepareRelayStep(0xc0004e4240, {0x4a1aac0, 0xc0004e0090}, {0x4a12f60, 0xc00011e4ec})
        /Users/denchick/Code/spqr/router/pkg/rulerouter/relay.go:508 +0x165
github.com/pg-sharding/spqr/router/pkg/rulerouter.(*RelayStateImpl).ProcessMessage(0xc0004e4240, {0x4a0e660, 0xc0004b84c0}, 0x0?, 0x0?, {0x4a12f60?, 0xc00011e4ec?})
        /Users/denchick/Code/spqr/router/pkg/rulerouter/relay.go:549 +0x69
github.com/pg-sharding/spqr/router/pkg.Frontend.func1({0x4a1aac0, 0xc0004e0090}, 0xc0000fbea8, {0x4a18938?, 0xc0004e4240?}, {0x4a12f60, 0xc00011e4ec})
        /Users/denchick/Code/spqr/router/pkg/frontend.go:211 +0x267
github.com/pg-sharding/spqr/router/pkg.Frontend({0x4a191a0?, 0xc000126000}, {0x4a1aac0, 0xc0004e0090}, {0x4a12f60?, 0xc00011e4ec}, 0xc0002fc000)
        /Users/denchick/Code/spqr/router/pkg/frontend.go:291 +0x56c
github.com/pg-sharding/spqr/router/pkg.(*InstanceImpl).serv(0xc0001223c0, {0x4a13a00, 0xc0004a6040})
        /Users/denchick/Code/spqr/router/pkg/instance.go:130 +0x114
github.com/pg-sharding/spqr/router/pkg.(*InstanceImpl).Run.func3()
        /Users/denchick/Code/spqr/router/pkg/instance.go:163 +0x29
created by github.com/pg-sharding/spqr/router/pkg.(*InstanceImpl).Run
        /Users/denchick/Code/spqr/router/pkg/instance.go:162 +0x34a

To Reproduce

log_level: 'DEBUG5'
host: 'localhost'
router_port: '6432'
admin_console_port: '7432'
grpc_api_port: '7000'
router_mode: PROXY
show_notice_messages: true
init_sql: "init.sql"
frontend_tls:
  sslmode: disable
frontend_rules:
  - usr: sbtest
    db: sbtest
    pool_mode: TRANSACTION
    auth_rule:
      auth_method: ok
backend_rules:
  - usr: sbtest
    db: sbtest
    pool_discard: true
    pool_rollback: true
    auth_rule:
      auth_method: md5
      password: password
shards:
  shard01:
    db: sbtest
    usr: sbtest
    pwd: password
    type: DATA
    tls:
      sslmode: "require"
      root_cert_file: "/Users/denchick/.postgresql/root.crt"
    hosts:
      - 'sas-xhxplfw3lnnqbf5c.db.yandex.net:6432'
  shard02:
    db: sbtest
    usr: sbtest
    pwd: password
    type: DATA
    tls:
      sslmode: "require"
      root_cert_file: "/Users/denchick/.postgresql/root.crt"
    hosts:
      - 'sas-v0df2vny5r5x5u77.db.yandex.net:6432'

'DROP DISTRIBUTION ALL CASADE' does not detach relations

To Reproduce
Steps to reproduce the behavior:
In coordinator console
CREATE DISTRIBUTION ds1 COLUMN TYPES integer; ALTER DISTRIBUTION ds1 ATTACH RELATION test DISTRIBUTION KEY id; DROP DISTRIBUTION ds1 CASCADE; CREATE DISTRIBUTION ds1 COLUMN TYPES integer; ALTER DISTRIBUTION ds1 ATTACH RELATION test DISTRIBUTION KEY id;
Resulting error
ERROR: relation "test" is already attached. (SQLSTATE )

Expected behavior
No error

`SHOW sharding_rules` response as a table

For some reason, show sharding_rules output is a text:

SHOW sharding_rules;
                           listing sharding rules                           
----------------------------------------------------------------------------
 sharding rule cat for table (orders) with columns [iid, hash: x->x]
 sharding rule dog for table (delivery) with columns [order_id, hash: x->x]
 sharding rule rule1 for table (*) with columns [id, hash: x->x]
(3 rows)

But key_range's output is a table, why not do the same for sharding_rule?

SHOW key_ranges;
 Key range ID | Shard ID | Lower bound | Upper bound 
--------------+----------+-------------+-------------
 krid1        | sh1      | 1           | 10
 krid2        | sh1      | 11          | 20
(2 rows)

Why if here?

          Why if here?

Originally posted by @EinKrebs in #416 (comment)

pg-sharding / spqr Goto Github PK

spqr's Introduction

Stateless Postgres Query Router

Main features

Development

Tests

License

Chat

spqr's People

Contributors

Stargazers

Watchers

Forkers

spqr's Issues

Recommend Projects

Recommend Topics

Recommend Org