Troubleshooting in Managed Service for Sharded PostgreSQL

• Transaction does not apply to all shards

• Requests are not routed to a new shard

• failed to get connection to any shard host within error when connecting to cluster hosts

• error processing query ... : syntax error error when executing the query

• permission denied for schema error

• failed to find primary within host error

• failed to match any datashard error or blocked request

Transaction does not apply to all shardsTransaction does not apply to all shards

Cause: Two-phase fixation not enabled for cross-shard operations.
Solution: Enable two-phase fixation:

BEGIN;
SET __spqr__commit_strategy TO '2pc';
INSERT INTO orders ...; /* affects several shards */
COMMIT;

Queries are not routed to new shardQueries are not routed to new shard

When adding a new shard, you may hit a situation where queries are not routed to it. To track query routing, you can:

• Enable show_notice_message.

• Use the /* __spqr__reply_notice: true */ virtual parameter.

  You can set virtual parameters with comments in SQL or via SET.

In either case, the router will message to your app the shard the query was routed to.

Solution:

• Check whether the new shard is displayed in Sharded PostgreSQL (SHOW shards).
• If you are using a sharded table, make sure the data needs to be on this particular shard (SHOW key_ranges).
• If you are using a reference table, make sure the table was created on this particular shard (SHOW reference_relations).

failed to get connection to any shard host within error when connecting to cluster hostsfailed to get connection to any shard host within error when connecting to cluster hosts

Error example:

failed to get connection to any shard host within: host {rc1d-cofs7cre********.mdb.yandexcloud.net:6432 rc1d}: dial tcp 10.151.25.35:6432: i/o timeout, host {rc1b-49796b52********.mdb.yandexcloud.net:6432 rc1b}: dial tcp 10.149.25.23:6432: i/o timeout, host {rc1a-kdm7v4qm********.mdb.yandexcloud.net:6432 rc1a}: dial tcp 10.148.25.15:6432: i/o timeout

This error occurs if the router cannot connect to the shard hosts.

Solution:

1. Make sure that the Managed Service for Sharded PostgreSQL cluster and shards are in the same network and security group.

2. Add to the security group the inbound and outbound traffic rules allowing TCP connection to port 6432:

   • Port range: 6432.
   • Protocol: TCP.
   • Destination name: CIDR.
   • CIDR blocks: Specify the cluster CIDR, e.g., 10.96.0.0/16.

error processing query ... : syntax error when running a queryerror processing query ... : syntax error when running a query

This error occurs due to internal Sharded PostgreSQL problem, not because of syntax errors in your SQL query. Sharded PostgreSQL uses a proprietary SQL parser which may not support some of the nuances:

• PostgreSQL-specific operators.
• Rare syntax variants.
• Non-standard functions.

Solution: Report the problem to the Sharded PostgreSQL vendor by creating an issue in the project’s Github repository and attaching the full text of your query.

permission denied for schema errorpermission denied for schema error

This error occurs if the user does not have enough permissions to use a schema.

Solution: Use the GRANT ALL ON SCHEMA <schema_name> TO <username>; command to grant the user permissions for the schema on the relevant shard or all shards.

failed to find primary within host errorfailed to find primary within host error

This error means that your router fails to connect to the shard master within the specified time.

Possible causes:

• Network issues between the router and the shard.
• Shard overload (e.g., high CPU wait).
• Incorrect target-session-attrs settings (e.g., read-only in case of a write query).

Solution:

• Make sure the networking between the router and the shard is undisturbed.
• Increase computing resources in the PostgreSQL cluster which is the overloaded shard.
• Make sure the target-session-attrs settings match your query.

failed to match any datashard error or blocked queryfailed to match any datashard error or blocked query

This error occurs when your router cannot match the query with a specific sharding key range. For example, if default_route_behaviour in the router configuration is set to BLOCK, queries without a sharding key are blocked.

Solution:

• Change the router's request matching behavior:

  • Permanently: Set default_route_behaviour to ALLOW in the router configuration.
  • Temporarily: Use the /* __spqr__default_route_behaviour: allow */ virtual parameter.

• Check that:

  • Sharding key is correct in the query: its name in the query must match the key name in Sharded PostgreSQL metadata.
  • Sharding rules are in place (SHOW distributions).
  • Tables are in place (SHOW relations).
  • Ranges are in place (SHOW key_ranges).

• For multi-shard queries, activate engine_v2 via the /* __spqr__engine_v2: true */ virtual parameter.

You can set virtual parameters with comments in SQL or via SET.