For every query, you may specify special hints in different ways:

  • 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;

​
__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__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;