Routing hints

Routing hints aka virtual parameters

The SPQR router supports runtime hints and behavior configuration parameters. They behave differently depending on how users apply them in their work. There are two main ways to work with virtual parameters:

• Comment in the SQL statement:

  SELECT count(*) FROM articles /* __spqr__execute_on: shard2 */;
  

• Via SET-RESET interface:

  SET __spqr__default_route_behaviour TO 'ALLOW';
  SHOW __spqr__default_route_behaviour;
   default route behaviour 
  -------------------------
   ALLOW
  (1 row)
  RESET __spqr__default_route_behaviour;
  

When you define a virtual parameter using an SQL comment inside a query, its effect is limited to that specific query only.

Using the SET statement to define a virtual parameter will take effect when the transaction block is bound. The value of the virtual parameter will be session-wide if no explicit transaction is initiated.

To put it simply:

• SQL comment = affects only one query
• SET statement = affects the whole session (unless a transaction is active)

Example

db1=# SHOW __spqr__commit_strategy;
 __spqr__commit_strategy
-------------------------
 best-effort
(1 row)

db1=# SET __spqr__commit_strategy TO '2pc';
SET
db1=# SHOW __spqr__commit_strategy;
 __spqr__commit_strategy
-------------------------
 2pc
(1 row)

db1=# BEGIN;
BEGIN
db1=*# SET __spqr__commit_strategy TO '1pc';
SET
db1=*# show __spqr__commit_strategy;
 __spqr__commit_strategy
-------------------------
 1pc
(1 row)

db1=*# ROLLBACK;
ROLLBACK
db1=# SHOW __spqr__commit_strategy;
 __spqr__commit_strategy
-------------------------
 2pc
(1 row)

List of hints

__spqr__target_session_attrs

The target-session-attrs parameter in PostgreSQL is used to specify the desired session state when connecting to a database. It is particularly useful in scenarios with multiple servers and failover configurations, where you want to ensure your session connects to the server with the desired role or state. target_session_attrs and __spqr__target_session_attrs are both aliases for target-session-attrs.

__spqr__target_session_attrs, target_session_attrs and __spqr__target_session_attrs are aliases for target-session-attrs.

Possible values for target-session-attrs:

• read-write: Connects to a server in read-write mode, and fails if the primary is unavailable.
• smart-read-write: Connects to a server in read-write mode, and fails if the primary is unavailable. Automatically routes read-only queries to standbys (with prefer-standby semantics), if query_routing: auto_route_ro_on_standby is set to true in configure file.
• read-only: Connects only to read-only (in recovery) servers. Fails if none available.
• prefer-standby or prefer-replica: Prefers connecting to standby servers. If none are available, it connects to primary.
• any: Connect to any available server, regardless of state.

SELECT pg_is_in_recovery() /* target-session-attrs: read-write */;
 pg_is_in_recovery 
-------------------
 f                 
(1 row)
SELECT pg_is_in_recovery() /* target-session-attrs: read-only */;
 pg_is_in_recovery 
-------------------
 t                 
(1 row)

__spqr__default_route_behaviour

Whether to explicitly block multishard queries. Overload for query_routing.default_route_behaviour.

DROP DATABASE prod;
ERROR:  client processing error: 'DROP DATABASE prod': failed to match any datashard., tx status IDLE

DROP DATABASE prod /* __spqr__default_route_behaviour: allow */;
NOTICE: send query to shard(s) : shard1,shard2,shard3,shard4

__spqr__execute_on

The __spqr__execute_on parameter is used to specify the shard on which a query should be executed.

SELECT col1 FROM xx ORDER BY col1 /* __spqr__execute_on: shard2 */;

__spqr__reply_notice

Whether to show notice messages. Overload for query_routing.default_route_behaviour.

SET __spqr__reply_notice TO true;
INSERT INTO xxtest_sw (id) VALUES(1), (2), (3);
NOTICE: send query to shard(s) : sh1

__spqr__sharding_key

The __spqr__sharding_key explicitly pass the sharding key to the SPQR Router.

INSERT INTO test(id, age) VALUES (10, 16) /*__spqr__sharding_key: 30*/;

__spqr__distribution

The __spqr__distribution explicitly specify which distribution the SPQR router should use for query routing.

DELETE FROM users WHERE name = 'denchick'
/* __spqr__distribution: ds1,__spqr__sharding_key: 123 */;

__spqr__distributed_relation

The __spqr__distributed_relation is name of distributed relation in context of which query meant to be executed. Is takes effect when distribution contains both CITY and MURMUR hash based sharding

DELETE FROM users WHERE name = 'denchick'
/* __spqr__distribution: ds1,__spqr__distributed_relation: users,__spqr__sharding_key: 123 */;

__spqr__auto_distribution

The __spqr__auto_distribution parameter is used to automatically adding new table in SPQR metadata without going into the spqr-console.

CREATE TABLE tax_rates (
    country_code VARCHAR(2),
    state_code VARCHAR(2)
);
ERROR:  client processing error: error processing query 'CREATE TABLE zz(i int, j int, k int);': distribution for relation "zz" not found, tx status IDLE

CREATE TABLE tax_rates (
    country_code VARCHAR(2),
    state_code VARCHAR(2) /* __spqr__auto_distribution: REPLICATED */;
);
NOTICE: send query to shard(s) : sh1,sh2,sh3,sh4

__spqr__distribution_key

The __spqr__distribution_key parameter is used to automatically add a new table to the SPQR metadata without having to go into the spqr console. This parameter can only be used with the __spqr__auto_distribution hint.

CREATE TABLE orders (id int, item int) /* __spqr__auto_distribution: ds1, __spqr__distribution_key: id */;

__spqr__scatter_query

The __spqr__scatter_query parameter is used to indicate that the query should be sent to all shards in the cluster.

SELECT * FROM users /* __spqr__scatter_query: true */;

__spqr__commit_strategy

The __spqr__commit_strategy parameter is used to specify the commit strategy for a distributed transaction. Possible values are 1pc and 2pc. best-effort is an alias for 1pc.

SELECT * FROM users /* __spqr__commit_strategy: 1pc */ WHERE id = 123;