Virtual functions

Virtual functions let you check router status and query SPQR metadata from an application database connection. They require router_mode: PROXY. If the router has only one shard, set query_routing.always_check_rules to true; otherwise, these queries are forwarded directly to PostgreSQL.

query_routing:
  always_check_rules: true

spqris_ready()

Returns the router’s readiness status as a boolean.

SELECT __spqr__is_ready();

Readiness is based on cached host health checks. The router is considered ready if at least one host is alive or the cache is empty. This does not guarantee that every shard is reachable or has a writable host.

spqrshards()

Lists the shards in SPQR metadata, with one row per shard and a single text column named shard name.

SELECT __spqr__shards();

 shard name
------------
 sh1
 sh2
(2 rows)

For shard configuration details, use SELECT * FROM __spqr__show('shards_extended');.

spqrshow(target)

Runs a router console SHOW command. Pass the target as a lowercase string, for example 'key_ranges'. Both forms return one row per key range:

SELECT __spqr__show('key_ranges');
SELECT * FROM __spqr__show('key_ranges');

 key_range_id | shard_id | distribution_id | lower_bound | locked
--------------+----------+-----------------+-------------+--------
 k1           | sh1      | ds1             | 1           | false
 k2           | sh2      | ds1             | 101         | false
(2 rows)

All columns have type text:

Column	Description
`key_range_id`	Key range identifier.
`shard_id`	Shard assigned to the key range.
`distribution_id`	Distribution containing the key range.
`lower_bound`	Lower bound; multiple components are comma-separated.
`locked`	Lock status: `'true'` or `'false'`.

Use the function in FROM to select columns and filter rows:

SELECT key_range_id, shard_id FROM __spqr__show('key_ranges')
WHERE shard_id = 'sh1';

SELECT * FROM __spqr__show('key_ranges') WHERE locked = 'false';

ORDER BY works only when postprocessing is enabled in one of these ways:

Set query_routing.allow_postprocessing to true in the router configuration.
Run SET __spqr__allow_postprocessing TO true for the current session.
Add the /* __spqr__allow_postprocessing: true */ query hint.

spqrhost_status(host)

Returns a host’s cached read-write status. Pass the host address, including its port, as a string literal:

SELECT __spqr__host_status('spqr_shard_1:6432');

The result has two text columns: host is the address, and rw is 'true' for a writable host or 'false' otherwise. The call fails if the host has no cached health check result.

​__spqr__is_ready()

​__spqr__shards()

​__spqr__show(target)

​__spqr__host_status(host)

spqris_ready()

spqrshards()

spqrshow(target)

spqrhost_status(host)