Add 'Fail' mode when no shard selected

[lc-guy]

Equivalent of MR postgresml/pgcat#859 for pgcat.

I need my application to explicitly select shard for every single connection. Not doing so should be an error, because it can lead to data compromise.

I would like to use shards as tenants, and have pgdog act as a router for the correct instance based on comments, so that it can be transparent for my applications without needing to reestablish a connection with every request.

[levkk]

Hi. How are you planning on selecting the shards? Currently, there are 3 mechanisms:

• Using comments, e.g. /* pgdog_shard: 1 */
• Automatically using a sharding key in the query, e.g. SELECT * FROM users WHERE tenant_id = $1
• Using SET, e.g. SET pgdog.shard TO 1

[lc-guy]

Using comments. (I'm converting an existing service to handle multi-tenancy without touching the db schema or heavily modifying application code.)

[lc-guy]

To elaborate:

The multi-tenancy feature described in this blog post is pretty neat, but it only works insofar as the tenant_id becomes a sharding key.

However, I'd like to be able to precisely say which database a tenant will end up on. As such, I cannot leave it up to the sharding function, which is why I'm using comments to directly specify the shard.

[levkk]

Got it. If you're using prepared statements, consider using SET instead of comments. The comments will increase the size of the statement cache, both inside PgDog and inside your client. Unique comments make the statement unique.

You can use SET as follows:

BEGIN;
SET pgdog.shard TO 1;
-- The rest of your queries.
COMMIT;

The only issue is this only works inside a transaction.

Another feature we're building is sharding by list/range. You would be able to specify which tenant_id goes to which shard using the config, e.g.:

[[tenant]]
id = 1
shard = 1

[[tenant]]
id = 2
shard = 2

# etc

[levkk]

Added in #236:

[general]
cross_shard_disabled = true