Improve JDBC Postgres driver support

[lc-guy]

The following prepared statement:

BEGIN; SET pgdog.sharding_key TO '10000000-0000-0000-0000-000000000000'; CREATE SCHEMA IF NOT EXISTS system;

returns an error (and seems to route it to shard All):

2025-08-29T11:10:29.042275Z TRACE routing ClientRequest {
    messages: [
        Parse(
            Parse {
                name: "__pgdog_1",
                query: "BEGIN",
                modified: true,
            },
        ),
        Bind(
            Bind {
                portal: b"\0",
                statement: b"__pgdog_1\0",
                codes: [],
                params: [],
                results: [],
                original: None,
            },
        ),
        Describe(
            Describe {
                payload: b"D\0\0\0\x06P\0",
                original: Some(
                    b"D\0\0\0\x06P\0",
                ),
            },
        ),
        Execute(
            Execute {
                portal: "",
            },
        ),
        Parse(
            Parse {
                name: "__pgdog_2",
                query: " SET pgdog.sharding_key TO '10000000-0000-0000-0000-000000000000'",
                modified: true,
            },
        ),
        Bind(
            Bind {
                portal: b"\0",
                statement: b"__pgdog_2\0",
                codes: [],
                params: [],
                results: [],
                original: None,
            },
        ),
        Describe(
            Describe {
                payload: b"D\0\0\0\x06P\0",
                original: Some(
                    b"D\0\0\0\x06P\0",
                ),
            },
        ),
        Execute(
            Execute {
                portal: "",
            },
        ),
        Parse(
            Parse {
                name: "__pgdog_3",
                query: " CREATE SCHEMA IF NOT EXISTS system",
                modified: true,
            },
        ),
        Bind(
            Bind {
                portal: b"\0",
                statement: b"__pgdog_3\0",
                codes: [],
                params: [],
                results: [],
                original: None,
            },
        ),
        Describe(
            Describe {
                payload: b"D\0\0\0\x06P\0",
                original: Some(
                    b"D\0\0\0\x06P\0",
                ),
            },
        ),
        Execute(
            Execute {
                portal: "",
            },
        ),
        Sync(
            Sync,
        ),
    ],
    route: Route {
        shard: All,
        read: false,
        order_by: [],
        aggregate: Aggregate {
            targets: [],
            group_by: [],
        },
        limit: Limit {
            limit: None,
            offset: None,
        },
        lock_session: false,
        distinct: None,
    },
} to Query(
    Route {
        shard: All,
        read: false,
        order_by: [],
        aggregate: Aggregate {
            targets: [],
            group_by: [],
        },
        limit: Limit {
            limit: None,
            offset: None,
        },
        lock_session: false,
        distinct: None,
    },
)
2025-08-29T11:10:29.042319Z TRACE [172.18.0.5:33632] <-- ErrorResponse {
    severity: "ERROR",
    code: "58000",
    message: "cross-shard queries are disabled",
    detail: Some(
        "query doesn't have a sharding key",
    ),
    context: None,
    file: None,
    routine: None,
}

With the following configuration:

[[databases]]
name = "example"
host = "postgres"
role = "primary"
user = "postgres"
shard = 0

[[databases]]
name = "example"
host = "postgres2"
role = "primary"
user = "postgres"
shard = 1

[[sharded_tables]]
database = "example"
column = "tenant_id"
data_type = "uuid"

[[sharded_mappings]]
database = "example"
column = "tenant_id"
kind = "list"
values = ["00000000-0000-0000-0000-000000000000"]
shard = 0

[[sharded_mappings]]
database = "example"
column = "tenant_id"
kind = "list"
values = ["10000000-0000-0000-0000-000000000000"]
shard = 1

The same thing happens when using /* pgdog_sharding_key: ... */ in a simple statement.

For reference; the tables in this database don't necessarily contain a tenant_id column, all sharding decisions are made purely using /* pgdog_sharding_key */ / SET pgdog.sharding_key. However, I see no other way to define the data_type for the sharding_key than include a sharded_tables entry. Discarding sharded_tables leads to the same behavior.

I assume there is no way of simply omitting the column name in order to match on all tables regardless, and force usage of the annotation?

[levkk]

 Parse(
            Parse {
                name: "__pgdog_2",
                query: " SET pgdog.sharding_key TO '10000000-0000-0000-0000-000000000000'",
                modified: true,
            },
        ),

Our implementation expects your database client to send both BEGIN and SET as simple queries and not prepared statements. I remember you mentioned you were using the jdbc Postgres driver. I think this should look like this:

Statement stmt = conn.createStatement()
stmt.executeQuery("SET pgdog.sharding_key TO '" + key + "'");

Our test suite for jdbc/java is pretty small at the moment, I think there is some room for improvement here and in the docs.

[lc-guy]

For what it's worth, this also happens with the following simple query using a comment:

ClientRequest {
    messages: [
        Query(
            Query {
                query: "/* pgdog_sharding_key: '10000000-0000-0000-0000-000000000000' */ SELECT * FROM \"system\".\"technical_model\";",
            },
        ),
    ],
    route: Route {
        shard: All,
        read: false,
        order_by: [],
        aggregate: Aggregate {
            targets: [],
            group_by: [],
        },
        limit: Limit {
            limit: None,
            offset: None,
        },
        lock_session: false,
        distinct: None,
    },
}
2025-08-29T11:35:38.664954Z DEBUG parser is enabled
2025-08-29T11:35:38.665019Z DEBUG /* pgdog_sharding_key: '10000000-0000-0000-0000-000000000000' */ SELECT * FROM "system"."technical_model";
2025-08-29T11:35:38.665023Z TRACE ParseResult {
    protobuf: ParseResult {
        version: 170004,
        stmts: [
            RawStmt {
                stmt: Some(
                    Node {
                        node: Some(
                            SelectStmt(
                                SelectStmt {
                                    distinct_clause: [],
                                    into_clause: None,
                                    target_list: [
                                        Node {
                                            node: Some(
                                                ResTarget(
                                                    ResTarget {
                                                        name: "",
                                                        indirection: [],
                                                        val: Some(
                                                            Node {
                                                                node: Some(
                                                                    ColumnRef(
                                                                        ColumnRef {
                                                                            fields: [
                                                                                Node {
                                                                                    node: Some(
                                                                                        AStar(
                                                                                            AStar,
                                                                                        ),
                                                                                    ),
                                                                                },
                                                                            ],
                                                                            location: 72,
                                                                        },
                                                                    ),
                                                                ),
                                                            },
                                                        ),
                                                        location: 72,
                                                    },
                                                ),
                                            ),
                                        },
                                    ],
                                    from_clause: [
                                        Node {
                                            node: Some(
                                                RangeVar(
                                                    RangeVar {
                                                        catalogname: "",
                                                        schemaname: "system",
                                                        relname: "technical_model",
                                                        inh: true,
                                                        relpersistence: "p",
                                                        alias: None,
                                                        location: 79,
                                                    },
                                                ),
                                            ),
                                        },
                                    ],
                                    where_clause: None,
                                    group_clause: [],
                                    group_distinct: false,
                                    having_clause: None,
                                    window_clause: [],
                                    values_lists: [],
                                    sort_clause: [],
                                    limit_offset: None,
                                    limit_count: None,
                                    limit_option: Default,
                                    locking_clause: [],
                                    with_clause: None,
                                    op: SetopNone,
                                    all: false,
                                    larg: None,
                                    rarg: None,
                                },
                            ),
                        ),
                    },
                ),
                stmt_location: 0,
                stmt_len: 105,
            },
        ],
    },
    warnings: [],
    tables: [
        (
            "system.technical_model",
            Select,
        ),
    ],
    aliases: {},
    cte_names: [],
    functions: [],
    filter_columns: [],
}
2025-08-29T11:35:38.665263Z DEBUG fingerprint: 18296ba66c13a696
2025-08-29T11:35:38.665269Z DEBUG query router decision: Query(
    Route {
        shard: All,
        read: false,
        order_by: [],
        aggregate: Aggregate {
            targets: [],
            group_by: [],
        },
        limit: Limit {
            limit: None,
            offset: None,
        },
        lock_session: false,
        distinct: None,
    },
)
2025-08-29T11:35:38.665281Z TRACE routing ClientRequest {
    messages: [
        Query(
            Query {
                query: "/* pgdog_sharding_key: '10000000-0000-0000-0000-000000000000' */ SELECT * FROM \"system\".\"technical_model\";",
            },
        ),
    ],
    route: Route {
        shard: All,
        read: false,
        order_by: [],
        aggregate: Aggregate {
            targets: [],
            group_by: [],
        },
        limit: Limit {
            limit: None,
            offset: None,
        },
        lock_session: false,
        distinct: None,
    },
} to Query(
    Route {
        shard: All,
        read: false,
        order_by: [],
        aggregate: Aggregate {
            targets: [],
            group_by: [],
        },
        limit: Limit {
            limit: None,
            offset: None,
        },
        lock_session: false,
        distinct: None,
    },
)
2025-08-29T11:35:38.665301Z TRACE [172.18.0.5:48312] <-- ErrorResponse {
    severity: "ERROR",
    code: "58000",
    message: "cross-shard queries are disabled",
    detail: Some(
        "query doesn't have a sharding key",
    ),
    context: None,
    file: None,
    routine: None,
}

EDIT: Just realized this may be because the regex for pgdog_sharding_key doesn't account for either the simple quotes (or dashes) that would be present for either varchar/uuid values there. Is this out of scope?

[levkk]

EDIT: Just realized this may be because the regex for pgdog_sharding_key doesn't account for either the simple quotes (or dashes) that would be present for either varchar/uuid values there. Is this out of scope?

Let me check, maybe this can be a quick regex fix.

[lc-guy]

 Parse(
            Parse {
                name: "__pgdog_2",
                query: " SET pgdog.sharding_key TO '10000000-0000-0000-0000-000000000000'",
                modified: true,
            },
        ),

Our implementation expects your database client to send both BEGIN and SET as simple queries and not prepared statements. I remember you mentioned you were using the jdbc Postgres driver. I think this should look like this:

Statement stmt = conn.createStatement()
stmt.executeQuery("SET pgdog.sharding_key TO '" + key + "'");

Our test suite for jdbc/java is pretty small at the moment, I think there is some room for improvement here and in the docs.

I've tried a few variations on this.

Running stmt.execute("SET pgdog.sharding_key TO '" + key + "'");:

2025-09-01T10:10:03.019663Z TRACE request buffered [0.0031ms]
ClientRequest {
    messages: [
        Query(
            Query {
                query: "SET pgdog.sharding_key = '10000000-0000-0000-0000-000000000000'",
            },
        ),
    ],
    route: Some(
        Route {
            shard: All,
            read: false,
            order_by: [],
            aggregate: Aggregate {
                targets: [],
                group_by: [],
            },
            limit: Limit {
                limit: None,
                offset: None,
            },
            lock_session: false,
            distinct: None,
        },
    ),
}
2025-09-01T10:10:03.019693Z DEBUG parser is enabled
2025-09-01T10:10:03.019740Z DEBUG SET pgdog.sharding_key = '10000000-0000-0000-0000-000000000000'
2025-09-01T10:10:03.019743Z TRACE ParseResult {
    protobuf: ParseResult {
        version: 170004,
        stmts: [
            RawStmt {
                stmt: Some(
                    Node {
                        node: Some(
                            VariableSetStmt(
                                VariableSetStmt {
                                    kind: VarSetValue,
                                    name: "pgdog.sharding_key",
                                    args: [
                                        Node {
                                            node: Some(
                                                AConst(
                                                    AConst {
                                                        isnull: false,
                                                        location: 25,
                                                        val: Some(
                                                            Sval(
                                                                String {
                                                                    sval: "10000000-0000-0000-0000-000000000000",
                                                                },
                                                            ),
                                                        ),
                                                    },
                                                ),
                                            ),
                                        },
                                    ],
                                    is_local: false,
                                },
                            ),
                        ),
                    },
                ),
                stmt_location: 0,
                stmt_len: 0,
            },
        ],
    },
    warnings: [],
    tables: [],
    aliases: {},
    cte_names: [],
    functions: [],
    filter_columns: [],
}
2025-09-01T10:10:03.019793Z ERROR Parser(RequiresTransaction) [[172.18.0.5:51870]]
2025-09-01T10:10:03.019801Z TRACE [172.18.0.5:51870] <-- ErrorResponse {
    severity: "ERROR",
    code: "42601",
    message: "this command requires a transaction",
    detail: None,
    context: None,
    file: None,
    routine: None,
}
2025-09-01T10:10:03.019808Z TRACE [172.18.0.5:51870] <-- ReadyForQuery {
    status: 'I',
}
2025-09-01T10:10:03.019846Z TRACE 😳
2025-09-01T10:10:03.019851Z DEBUG transaction has nowhere to go
2025-09-01T10:10:03.019854Z DEBUG binding suspended

=> this command requires a transaction, so we need to open a transaction, run the simple statement there, then run the prepared statement afterwards. At least in Java, this requires setting autocommit to off, running the two commands, then restoring it.

Restoring the autocommit with connection.setAutoCommit(true) runs a COMMIT internally in the jdbc driver. pgdog rejects this, as it interprets it as a cross-shard query that it shouldn't allow. (could this be automatically resolved if there is only one transaction open on a specific shard with the current client?)

I attempted setting the autocommit myself, with the following flow: SET AUTOCOMMIT TO OFF => BEGIN; SET pgdog.sharding_key TO <uuid>; => prepared statement => SET AUTOCOMMIT TO ON.

However, this still seems to fail:

2025-09-01T10:34:32.942810Z TRACE request buffered [0.0025ms]
ClientRequest {
    messages: [
        Query(
            Query {
                query: "/* pgdog_sharding_key: '10000000-0000-0000-0000-000000000000' */ SET autocommit = false",
            },
        ),
    ],
    route: Some(
        Route {
            shard: All,
            read: false,
            order_by: [],
            aggregate: Aggregate {
                targets: [],
                group_by: [],
            },
            limit: Limit {
                limit: None,
                offset: None,
            },
            lock_session: false,
            distinct: None,
        },
    ),
}
2025-09-01T10:34:32.942838Z DEBUG parser is enabled
2025-09-01T10:34:32.943131Z DEBUG sharding using lists
2025-09-01T10:34:32.943158Z DEBUG /* pgdog_sharding_key: '10000000-0000-0000-0000-000000000000' */ SET autocommit = false
2025-09-01T10:34:32.943161Z TRACE ParseResult {
    protobuf: ParseResult {
        version: 170004,
        stmts: [
            RawStmt {
                stmt: Some(
                    Node {
                        node: Some(
                            VariableSetStmt(
                                VariableSetStmt {
                                    kind: VarSetValue,
                                    name: "autocommit",
                                    args: [
                                        Node {
                                            node: Some(
                                                AConst(
                                                    AConst {
                                                        isnull: false,
                                                        location: 82,
                                                        val: Some(
                                                            Sval(
                                                                String {
                                                                    sval: "false",
                                                                },
                                                            ),
                                                        ),
                                                    },
                                                ),
                                            ),
                                        },
                                    ],
                                    is_local: false,
                                },
                            ),
                        ),
                    },
                ),
                stmt_location: 0,
                stmt_len: 0,
            },
        ],
    },
    warnings: [],
    tables: [],
    aliases: {},
    cte_names: [],
    functions: [],
    filter_columns: [],
}
2025-09-01T10:34:32.943206Z TRACE routing [
    Query(
        Query {
            query: "/* pgdog_sharding_key: '10000000-0000-0000-0000-000000000000' */ SET autocommit = false",
        },
    ),
] to Set {
    name: "autocommit",
    value: String(
        "false",
    ),
}
2025-09-01T10:34:32.943221Z TRACE [172.18.0.5:59078] <-- CommandComplete {
    command: "SET",
}
2025-09-01T10:34:32.943227Z TRACE [172.18.0.5:59078] <-- ReadyForQuery {
    status: 'I',
}
2025-09-01T10:34:32.943265Z TRACE 😳
2025-09-01T10:34:32.943271Z DEBUG binding suspended
2025-09-01T10:34:32.943556Z TRACE request buffered [0.0014ms]
ClientRequest {
    messages: [
        Query(
            Query {
                query: "BEGIN; SET pgdog.sharding_key TO '10000000-0000-0000-0000-000000000000'",
            },
        ),
    ],
    route: Some(
        Route {
            shard: All,
            read: false,
            order_by: [],
            aggregate: Aggregate {
                targets: [],
                group_by: [],
            },
            limit: Limit {
                limit: None,
                offset: None,
            },
            lock_session: false,
            distinct: None,
        },
    ),
}
2025-09-01T10:34:32.943577Z DEBUG parser is enabled
2025-09-01T10:34:32.943631Z DEBUG BEGIN; SET pgdog.sharding_key TO '10000000-0000-0000-0000-000000000000'
2025-09-01T10:34:32.943635Z TRACE ParseResult {
    protobuf: ParseResult {
        version: 170004,
        stmts: [
            RawStmt {
                stmt: Some(
                    Node {
                        node: Some(
                            TransactionStmt(
                                TransactionStmt {
                                    kind: TransStmtBegin,
                                    options: [],
                                    savepoint_name: "",
                                    gid: "",
                                    chain: false,
                                    location: -1,
                                },
                            ),
                        ),
                    },
                ),
                stmt_location: 0,
                stmt_len: 5,
            },
            RawStmt {
                stmt: Some(
                    Node {
                        node: Some(
                            VariableSetStmt(
                                VariableSetStmt {
                                    kind: VarSetValue,
                                    name: "pgdog.sharding_key",
                                    args: [
                                        Node {
                                            node: Some(
                                                AConst(
                                                    AConst {
                                                        isnull: false,
                                                        location: 33,
                                                        val: Some(
                                                            Sval(
                                                                String {
                                                                    sval: "10000000-0000-0000-0000-000000000000",
                                                                },
                                                            ),
                                                        ),
                                                    },
                                                ),
                                            ),
                                        },
                                    ],
                                    is_local: false,
                                },
                            ),
                        ),
                    },
                ),
                stmt_location: 6,
                stmt_len: 0,
            },
        ],
    },
    warnings: [],
    tables: [],
    aliases: {},
    cte_names: [],
    functions: [],
    filter_columns: [],
}
2025-09-01T10:34:32.943692Z TRACE routing [
    Query(
        Query {
            query: "BEGIN; SET pgdog.sharding_key TO '10000000-0000-0000-0000-000000000000'",
        },
    ),
] to StartTransaction {
    query: Query(
        Query {
            query: "BEGIN; SET pgdog.sharding_key TO '10000000-0000-0000-0000-000000000000'",
        },
    ),
    transaction_type: ReadWrite,
}
2025-09-01T10:34:32.943701Z TRACE [172.18.0.5:59078] <-- CommandComplete {
    command: "BEGIN",
}
2025-09-01T10:34:32.943705Z TRACE [172.18.0.5:59078] <-- ReadyForQuery {
    status: 'T',
}
2025-09-01T10:34:32.943734Z TRACE 😳
2025-09-01T10:34:32.943740Z DEBUG binding suspended
2025-09-01T10:34:32.946309Z TRACE request buffered [0.0191ms]
ClientRequest {
    messages: [
        Parse(
            Parse {
                name: "__pgdog_1",
                query: "CREATE SCHEMA IF NOT EXISTS system",
                modified: true,
            },
        ),
        Bind(
            Bind {
                portal: b"\0",
                statement: b"__pgdog_1\0",
                codes: [],
                params: [],
                results: [],
                original: None,
            },
        ),
        Describe(
            Describe {
                kind: 'P',
                name: "",
            },
        ),
        Execute(
            Execute {
                portal: "",
            },
        ),
        Sync(
            Sync,
        ),
    ],
    route: Some(
        Route {
            shard: All,
            read: false,
            order_by: [],
            aggregate: Aggregate {
                targets: [],
                group_by: [],
            },
            limit: Limit {
                limit: None,
                offset: None,
            },
            lock_session: false,
            distinct: None,
        },
    ),
}
2025-09-01T10:34:32.946351Z DEBUG parser is enabled
2025-09-01T10:34:32.946392Z DEBUG CREATE SCHEMA IF NOT EXISTS system
2025-09-01T10:34:32.946395Z TRACE ParseResult {
    protobuf: ParseResult {
        version: 170004,
        stmts: [
            RawStmt {
                stmt: Some(
                    Node {
                        node: Some(
                            CreateSchemaStmt(
                                CreateSchemaStmt {
                                    schemaname: "system",
                                    authrole: None,
                                    schema_elts: [],
                                    if_not_exists: true,
                                },
                            ),
                        ),
                    },
                ),
                stmt_location: 0,
                stmt_len: 0,
            },
        ],
    },
    warnings: [],
    tables: [],
    aliases: {},
    cte_names: [],
    functions: [],
    filter_columns: [],
}
2025-09-01T10:34:32.946437Z DEBUG fingerprint: afb64cb90f4e0c9c
2025-09-01T10:34:32.946441Z DEBUG query router decision: Query(
    Route {
        shard: All,
        read: false,
        order_by: [],
        aggregate: Aggregate {
            targets: [],
            group_by: [],
        },
        limit: Limit {
            limit: None,
            offset: None,
        },
        lock_session: false,
        distinct: None,
    },
)
2025-09-01T10:34:32.946448Z TRACE routing [
    Parse(
        Parse {
            name: "__pgdog_1",
            query: "CREATE SCHEMA IF NOT EXISTS system",
            modified: true,
        },
    ),
    Bind(
        Bind {
            portal: b"\0",
            statement: b"__pgdog_1\0",
            codes: [],
            params: [],
            results: [],
            original: None,
        },
    ),
    Describe(
        Describe {
            kind: 'P',
            name: "",
        },
    ),
    Execute(
        Execute {
            portal: "",
        },
    ),
    Sync(
        Sync,
    ),
] to Query(
    Route {
        shard: All,
        read: false,
        order_by: [],
        aggregate: Aggregate {
            targets: [],
            group_by: [],
        },
        limit: Limit {
            limit: None,
            offset: None,
        },
        lock_session: false,
        distinct: None,
    },
)
2025-09-01T10:34:32.946469Z TRACE [172.18.0.5:59078] <-- ErrorResponse {
    severity: "ERROR",
    code: "58000",
    message: "cross-shard queries are disabled",
    detail: Some(
        "query doesn't have a sharding key",
    ),
    context: None,
    file: None,
    routine: None,
}
2025-09-01T10:34:32.946480Z TRACE [172.18.0.5:59078] <-- ReadyForQuery {
    status: 'E',
}
2025-09-01T10:34:32.946516Z TRACE 😳
2025-09-01T10:34:32.946525Z DEBUG binding suspended

The shard resolution for the prepared statement seems to ignore the fact that a shard was previously set.

I've also had a case where running the SET command with the sharding key info seems to redirect it to All instead of mapping it to a Set command:

2025-09-01T10:34:32.954411Z TRACE request buffered [0.0021ms]
ClientRequest {
    messages: [
        Query(
            Query {
                query: "/* pgdog_sharding_key: '10000000-0000-0000-0000-000000000000' */ SET autocommit = true",
            },
        ),
    ],
    route: Some(
        Route {
            shard: All,
            read: false,
            order_by: [],
            aggregate: Aggregate {
                targets: [],
                group_by: [],
            },
            limit: Limit {
                limit: None,
                offset: None,
            },
            lock_session: false,
            distinct: None,
        },
    ),
}
2025-09-01T10:34:32.954439Z DEBUG parser is enabled
2025-09-01T10:34:32.954468Z DEBUG sharding using lists
2025-09-01T10:34:32.954498Z DEBUG /* pgdog_sharding_key: '10000000-0000-0000-0000-000000000000' */ SET autocommit = true
2025-09-01T10:34:32.954501Z TRACE ParseResult {
    protobuf: ParseResult {
        version: 170004,
        stmts: [
            RawStmt {
                stmt: Some(
                    Node {
                        node: Some(
                            VariableSetStmt(
                                VariableSetStmt {
                                    kind: VarSetValue,
                                    name: "autocommit",
                                    args: [
                                        Node {
                                            node: Some(
                                                AConst(
                                                    AConst {
                                                        isnull: false,
                                                        location: 82,
                                                        val: Some(
                                                            Sval(
                                                                String {
                                                                    sval: "true",
                                                                },
                                                            ),
                                                        ),
                                                    },
                                                ),
                                            ),
                                        },
                                    ],
                                    is_local: false,
                                },
                            ),
                        ),
                    },
                ),
                stmt_location: 0,
                stmt_len: 0,
            },
        ],
    },
    warnings: [],
    tables: [],
    aliases: {},
    cte_names: [],
    functions: [],
    filter_columns: [],
}
2025-09-01T10:34:32.954549Z TRACE routing [
    Query(
        Query {
            query: "/* pgdog_sharding_key: '10000000-0000-0000-0000-000000000000' */ SET autocommit = true",
        },
    ),
] to Query(
    Route {
        shard: All,
        read: false,
        order_by: [],
        aggregate: Aggregate {
            targets: [],
            group_by: [],
        },
        limit: Limit {
            limit: None,
            offset: None,
        },
        lock_session: false,
        distinct: None,
    },
)
2025-09-01T10:34:32.954565Z TRACE [172.18.0.5:59078] <-- ErrorResponse {
    severity: "ERROR",
    code: "58000",
    message: "cross-shard queries are disabled",
    detail: Some(
        "query doesn't have a sharding key",
    ),
    context: None,
    file: None,
    routine: None,
}
2025-09-01T10:34:32.954573Z TRACE [172.18.0.5:59078] <-- ReadyForQuery {
    status: 'E',
}
2025-09-01T10:34:32.954622Z TRACE 😳
2025-09-01T10:34:32.954633Z DEBUG binding suspended

---

Maybe worth saying that using 413b20c, I was able to run SET pgdog.shard TO xxx inside a prepared statement with no errors. Not sure whether that was possible then with pgdog.sharding_key as well, though.

[levkk]

Got it. I need to give the jdbc driver interaction a real look. We can definitely support it, just a question of correctly anticipating its requests.

[lc-guy]

@levkk I did some additional tests using preferQuerymode=simple (which uses simple protocol for everything, even prepared statements), and found an issue that might be more generalized to transaction handling.

2025-09-03T08:21:45.391366Z TRACE request buffered [0.0040ms]
ClientRequest {
    messages: [
        Query(
            Query {
                query: "BEGIN",
            },
        ),
    ],
    route: Some(
        Route {
            shard: All,
            read: false,
            order_by: [],
            aggregate: Aggregate {
                targets: [],
                group_by: [],
            },
            limit: Limit {
                limit: None,
                offset: None,
            },
            lock_session: false,
            distinct: None,
        },
    ),
}
2025-09-03T08:21:45.391405Z DEBUG parser is enabled
2025-09-03T08:21:45.391444Z DEBUG BEGIN
2025-09-03T08:21:45.391449Z TRACE ParseResult {
    protobuf: ParseResult {
        version: 170004,
        stmts: [
            RawStmt {
                stmt: Some(
                    Node {
                        node: Some(
                            TransactionStmt(
                                TransactionStmt {
                                    kind: TransStmtBegin,
                                    options: [],
                                    savepoint_name: "",
                                    gid: "",
                                    chain: false,
                                    location: -1,
                                },
                            ),
                        ),
                    },
                ),
                stmt_location: 0,
                stmt_len: 0,
            },
        ],
    },
    warnings: [],
    tables: [],
    aliases: {},
    cte_names: [],
    functions: [],
    filter_columns: [],
}
2025-09-03T08:21:45.391477Z TRACE routing [
    Query(
        Query {
            query: "BEGIN",
        },
    ),
] to StartTransaction {
    query: Query(
        Query {
            query: "BEGIN",
        },
    ),
    transaction_type: ReadWrite,
}
2025-09-03T08:21:45.391492Z TRACE [172.18.0.5:53964] <-- CommandComplete {
    command: "BEGIN",
}
2025-09-03T08:21:45.391497Z TRACE [172.18.0.5:53964] <-- ReadyForQuery {
    status: 'T',
}
2025-09-03T08:21:45.391537Z TRACE 😳
2025-09-03T08:21:45.391544Z DEBUG binding suspended
2025-09-03T08:21:45.391563Z TRACE request buffered [0.0008ms]
ClientRequest {
    messages: [
        Query(
            Query {
                query: " SET pgdog.sharding_key TO '10000000-0000-0000-0000-000000000000'",
            },
        ),
    ],
    route: Some(
        Route {
            shard: All,
            read: false,
            order_by: [],
            aggregate: Aggregate {
                targets: [],
                group_by: [],
            },
            limit: Limit {
                limit: None,
                offset: None,
            },
            lock_session: false,
            distinct: None,
        },
    ),
}
2025-09-03T08:21:45.391578Z DEBUG parser is enabled
2025-09-03T08:21:45.391614Z DEBUG  SET pgdog.sharding_key TO '10000000-0000-0000-0000-000000000000'
2025-09-03T08:21:45.391617Z TRACE ParseResult {
    protobuf: ParseResult {
        version: 170004,
        stmts: [
            RawStmt {
                stmt: Some(
                    Node {
                        node: Some(
                            VariableSetStmt(
                                VariableSetStmt {
                                    kind: VarSetValue,
                                    name: "pgdog.sharding_key",
                                    args: [
                                        Node {
                                            node: Some(
                                                AConst(
                                                    AConst {
                                                        isnull: false,
                                                        location: 27,
                                                        val: Some(
                                                            Sval(
                                                                String {
                                                                    sval: "10000000-0000-0000-0000-000000000000",
                                                                },
                                                            ),
                                                        ),
                                                    },
                                                ),
                                            ),
                                        },
                                    ],
                                    is_local: false,
                                },
                            ),
                        ),
                    },
                ),
                stmt_location: 0,
                stmt_len: 0,
            },
        ],
    },
    warnings: [],
    tables: [],
    aliases: {},
    cte_names: [],
    functions: [],
    filter_columns: [],
}
2025-09-03T08:21:45.391659Z DEBUG sharding using lists
2025-09-03T08:21:45.391663Z TRACE routing [
    Query(
        Query {
            query: " SET pgdog.sharding_key TO '10000000-0000-0000-0000-000000000000'",
        },
    ),
] to SetRoute(
    Route {
        shard: Direct(
            1,
        ),
        read: false,
        order_by: [],
        aggregate: Aggregate {
            targets: [],
            group_by: [],
        },
        limit: Limit {
            limit: None,
            offset: None,
        },
        lock_session: false,
        distinct: None,
    },
)
2025-09-03T08:21:45.391675Z TRACE [172.18.0.5:53964] <-- CommandComplete {
    command: "SET",
}
2025-09-03T08:21:45.391679Z TRACE [172.18.0.5:53964] <-- ReadyForQuery {
    status: 'T',
}
2025-09-03T08:21:45.391704Z TRACE 😳
2025-09-03T08:21:45.391708Z DEBUG binding suspended
2025-09-03T08:21:45.391712Z TRACE request buffered [0.0005ms]
ClientRequest {
    messages: [
        Query(
            Query {
                query: " CREATE SCHEMA IF NOT EXISTS system",
            },
        ),
    ],
    route: Some(
        Route {
            shard: All,
            read: false,
            order_by: [],
            aggregate: Aggregate {
                targets: [],
                group_by: [],
            },
            limit: Limit {
                limit: None,
                offset: None,
            },
            lock_session: false,
            distinct: None,
        },
    ),
}
2025-09-03T08:21:45.391723Z DEBUG parser is enabled
2025-09-03T08:21:45.391741Z DEBUG  CREATE SCHEMA IF NOT EXISTS system
2025-09-03T08:21:45.391744Z TRACE ParseResult {
    protobuf: ParseResult {
        version: 170004,
        stmts: [
            RawStmt {
                stmt: Some(
                    Node {
                        node: Some(
                            CreateSchemaStmt(
                                CreateSchemaStmt {
                                    schemaname: "system",
                                    authrole: None,
                                    schema_elts: [],
                                    if_not_exists: true,
                                },
                            ),
                        ),
                    },
                ),
                stmt_location: 0,
                stmt_len: 0,
            },
        ],
    },
    warnings: [],
    tables: [],
    aliases: {},
    cte_names: [],
    functions: [],
    filter_columns: [],
}
2025-09-03T08:21:45.391771Z DEBUG fingerprint: afb64cb90f4e0c9c
2025-09-03T08:21:45.391774Z DEBUG query router decision: Query(
    Route {
        shard: All,
        read: false,
        order_by: [],
        aggregate: Aggregate {
            targets: [],
            group_by: [],
        },
        limit: Limit {
            limit: None,
            offset: None,
        },
        lock_session: false,
        distinct: None,
    },
)
2025-09-03T08:21:45.391782Z TRACE routing [
    Query(
        Query {
            query: " CREATE SCHEMA IF NOT EXISTS system",
        },
    ),
] to Query(
    Route {
        shard: All,
        read: false,
        order_by: [],
        aggregate: Aggregate {
            targets: [],
            group_by: [],
        },
        limit: Limit {
            limit: None,
            offset: None,
        },
        lock_session: false,
        distinct: None,
    },
)
2025-09-03T08:21:45.391789Z DEBUG connecting to backend(s) to serve transaction
2025-09-03T08:21:45.391791Z TRACE transaction routing to Route {
    shard: All,
    read: false,
    order_by: [],
    aggregate: Aggregate {
        targets: [],
        group_by: [],
    },
    limit: Limit {
        limit: None,
        offset: None,
    },
    lock_session: false,
    distinct: None,
}
2025-09-03T08:21:45.391802Z DEBUG server is in "idle" state [postgres:5432, db_name]
2025-09-03T08:21:45.391805Z DEBUG server is in "idle" state [postgres2:5432, db_name]
2025-09-03T08:21:45.391809Z DEBUG client paired with [postgres:5432, db_name,postgres2:5432, db_name] using route [shard=all, role=primary] [0.0101ms]
2025-09-03T08:21:45.391824Z DEBUG syncing 9 params
2025-09-03T08:21:45.391830Z TRACE [172.18.0.3:5432] <-- Query(
    Query {
        query: "RESET \"application_name\"",
    },
)
2025-09-03T08:21:45.391835Z TRACE [172.18.0.3:5432] <-- Query(
    Query {
        query: "RESET \"datestyle\"",
    },
)
2025-09-03T08:21:45.391838Z TRACE [172.18.0.3:5432] <-- Query(
    Query {
        query: "RESET \"extra_float_digits\"",
    },
)
2025-09-03T08:21:45.391841Z TRACE [172.18.0.3:5432] <-- Query(
    Query {
        query: "RESET \"timezone\"",
    },
)
2025-09-03T08:21:45.391843Z TRACE [172.18.0.3:5432] <-- Query(
    Query {
        query: "SET \"application_name\" TO 'PostgreSQL JDBC Driver'",
    },
)
2025-09-03T08:21:45.391846Z TRACE [172.18.0.3:5432] <-- Query(
    Query {
        query: "SET \"datestyle\" TO 'ISO'",
    },
)
2025-09-03T08:21:45.391848Z TRACE [172.18.0.3:5432] <-- Query(
    Query {
        query: "SET \"extra_float_digits\" TO '3'",
    },
)
2025-09-03T08:21:45.391851Z TRACE [172.18.0.3:5432] <-- Query(
    Query {
        query: "SET \"search_path\" TO 'system'",
    },
)
2025-09-03T08:21:45.391854Z TRACE [172.18.0.3:5432] <-- Query(
    Query {
        query: "SET \"timezone\" TO 'Etc/UTC'",
    },
)
2025-09-03T08:21:45.392209Z TRACE [172.18.0.3:5432] <-- Query(
    Query {
        query: "BEGIN",
    },
)
2025-09-03T08:21:45.392351Z TRACE [172.18.0.2:5432] <-- Query(
    Query {
        query: "BEGIN",
    },
)
2025-09-03T08:21:45.392482Z TRACE [172.18.0.2:5432] <-- Query(
    Query {
        query: " CREATE SCHEMA IF NOT EXISTS system",
    },
)
2025-09-03T08:21:45.392682Z TRACE [172.18.0.5:53964] <-- CommandComplete {
    command: "CREATE SCHEMA",
}
2025-09-03T08:21:45.392689Z TRACE [172.18.0.5:53964] <-- ReadyForQuery {
    status: 'T',
}
2025-09-03T08:21:45.392710Z TRACE 😳
2025-09-03T08:21:45.392716Z DEBUG multi-shard binding done

Note that this is ran with cross_shard_disabled = true. I double-checked, and it indeed only sends the query to the right server, even if the logs make it seem like it's being routed to Shard(All) and sent to both backends, so the dispatch seems to work properly.

On the postgres side, however, I get this error:

2025-09-03 08:16:03.167 UTC,"postgres","db_name",334,"172.18.0.4:46350",68b7f932.14e,4,"idle in transaction",2025-09-03 08:15:46 UTC,6/237,0,LOG,00000,"statement:  CREATE SCHEMA IF NOT EXISTS system",,,,,
,,,,"PostgreSQL JDBC Driver","client backend",,0
2025-09-03 08:16:03.170 UTC,"postgres","db_name",334,"172.18.0.4:46350",68b7f932.14e,5,"BEGIN",2025-09-03 08:15:46 UTC,6/237,951,WARNING,25001,"there is already a transaction in progress",,,,,,,,,"Postgre
SQL JDBC Driver","client backend",,2397681704071010949

Without an error getting reported back to pgdog, which treats it as if the CREATE SCHEMA was ran successfully. I imagine it might try to start a transaction twice throughout the process?

This is the code on the java side:

Connection connection = datasource.getConnection();
Statement stmt = connection.createStatement();
stmt.execute(("BEGIN; SET pgdog.sharding_key = '10000000-0000-0000-0000-000000000000'; CREATE SCHEMA IF NOT EXISTS system;");

omitting the BEGIN here gives me an error about the SET only being usable inside a transaction. This makes it seem as if it's entirely impossible to use SET pgdog.sharding_key properly at the moment.

[lc-guy]

If it's possible to give a point of comparison this way, using just pgdog.shard : using https://github.com/orgs/pgdogdev/packages/container/pgdog/498260842?tag=main-413b20c, I was able to do SET pgdog.shard TO 1; CREATE SCHEMA IF NOT EXISTS system (without using BEGIN) in either a regular or prepared statement, and it'd work fine:

2025-09-03T11:03:39.886301Z TRACE request buffered [0.0030ms]
ClientRequest {
    messages: [
        Query(
            Query {
                query: "SET pgdog.shard TO 1; CREATE SCHEMA IF NOT EXISTS system;",
            },
        ),
    ],
    route: Route {
        shard: All,
        read: false,
        order_by: [],
        aggregate: Aggregate {
            targets: [],
            group_by: [],
        },
        limit: Limit {
            limit: None,
            offset: None,
        },
        lock_session: false,
        distinct: None,
    },
}
2025-09-03T11:03:39.886326Z DEBUG parser is enabled
2025-09-03T11:03:39.886392Z DEBUG SET pgdog.shard TO 1; CREATE SCHEMA IF NOT EXISTS system;
2025-09-03T11:03:39.886395Z TRACE ParseResult {
    protobuf: ParseResult {
        version: 170004,
        stmts: [
            RawStmt {
                stmt: Some(
                    Node {
                        node: Some(
                            VariableSetStmt(
                                VariableSetStmt {
                                    kind: VarSetValue,
                                    name: "pgdog.shard",
                                    args: [
                                        Node {
                                            node: Some(
                                                AConst(
                                                    AConst {
                                                        isnull: false,
                                                        location: 19,
                                                        val: Some(
                                                            Ival(
                                                                Integer {
                                                                    ival: 1,
                                                                },
                                                            ),
                                                        ),
                                                    },
                                                ),
                                            ),
                                        },
                                    ],
                                    is_local: false,
                                },
                            ),
                        ),
                    },
                ),
                stmt_location: 0,
                stmt_len: 20,
            },
            RawStmt {
                stmt: Some(
                    Node {
                        node: Some(
                            CreateSchemaStmt(
                                CreateSchemaStmt {
                                    schemaname: "system",
                                    authrole: None,
                                    schema_elts: [],
                                    if_not_exists: true,
                                },
                            ),
                        ),
                    },
                ),
                stmt_location: 21,
                stmt_len: 35,
            },
        ],
    },
    warnings: [],
    tables: [],
    aliases: {},
    cte_names: [],
    functions: [],
    filter_columns: [],
}
2025-09-03T11:03:39.886449Z TRACE routing ClientRequest {
    messages: [
        Query(
            Query {
                query: "SET pgdog.shard TO 1; CREATE SCHEMA IF NOT EXISTS system;",
            },
        ),
    ],
    route: Route {
        shard: All,
        read: false,
        order_by: [],
        aggregate: Aggregate {
            targets: [],
            group_by: [],
        },
        limit: Limit {
            limit: None,
            offset: None,
        },
        lock_session: false,
        distinct: None,
    },
} to Query(
    Route {
        shard: Direct(
            1,
        ),
        read: false,
        order_by: [],
        aggregate: Aggregate {
            targets: [],
            group_by: [],
        },
        limit: Limit {
            limit: None,
            offset: None,
        },
        lock_session: false,
        distinct: None,
    },
)
2025-09-03T11:03:39.886465Z DEBUG server is in "idle" state [postgres2:5432, db_name]
2025-09-03T11:03:39.886470Z DEBUG client paired with [postgres2:5432, db_name] using route [shard=1, role=primary] [0.0071ms]
2025-09-03T11:03:39.886485Z DEBUG syncing 5 params
2025-09-03T11:03:39.886492Z TRACE [172.18.0.2:5432] <-- Query(
    Query {
        query: "SET \"application_name\" TO 'PostgreSQL JDBC Driver'",
    },
)
2025-09-03T11:03:39.886497Z TRACE [172.18.0.2:5432] <-- Query(
    Query {
        query: "SET \"datestyle\" TO 'ISO'",
    },
)
2025-09-03T11:03:39.886500Z TRACE [172.18.0.2:5432] <-- Query(
    Query {
        query: "SET \"extra_float_digits\" TO '3'",
    },
)
2025-09-03T11:03:39.886503Z TRACE [172.18.0.2:5432] <-- Query(
    Query {
        query: "SET \"search_path\" TO 'system'",
    },
)
2025-09-03T11:03:39.886505Z TRACE [172.18.0.2:5432] <-- Query(
    Query {
        query: "SET \"timezone\" TO 'Etc/UTC'",
    },
)
2025-09-03T11:03:39.886944Z TRACE [172.18.0.2:5432] <-- Query(
    Query {
        query: "SET pgdog.shard TO 1; CREATE SCHEMA IF NOT EXISTS system;",
    },
)
2025-09-03T11:03:39.897560Z TRACE [172.18.0.5:33804] <-- CommandComplete {
    command: "SET",
}
2025-09-03T11:03:39.897573Z TRACE [172.18.0.5:33804] <-- CommandComplete {
    command: "CREATE SCHEMA",
}
2025-09-03T11:03:39.897580Z DEBUG [cleanup] no cleanup needed, server in "idle" state [postgres2:5432, db_name]
2025-09-03T11:03:39.897585Z DEBUG transaction finished [11.109ms]
2025-09-03T11:03:39.897587Z DEBUG setting client's "application_name" to 'PostgreSQL JDBC Driver'
2025-09-03T11:03:39.897594Z TRACE [172.18.0.5:33804] <-- ReadyForQuery {
    status: 'I',
}

Note this would only work if I didn't add the BEGIN; if I did, the transaction would fail somehow.

SET started throwing the "can't be used outside of a transaction" error some time after that release. This means I started using BEGIN (bringing up the various errors displayed in this thread), but I think BEGIN was failing even before the most recent per-statement routing changes.

[levkk]

stmt.execute(("BEGIN; SET pgdog.sharding_key = '10000000-0000-0000-0000-000000000000'; CREATE SCHEMA IF NOT EXISTS system;");

We don't currently support handling multi-statement queries. If you split this into 3, it should work as expected:

String[] queries = {"BEGIN", "SET pgdog.sharding_key TO '<uuid>'", "CREATE SCHEMA system"};
for (String query: queries) {
     Statement stmt = connection.createStatement();
     stmt.execute(query);
}