Query with shard comment not detected

[lc-guy]

Having a weird issue with querying in this SQL statement using tag main:

2025-08-14T15:18:44.437708Z TRACE 😳
2025-08-14T15:18:44.437716Z DEBUG binding suspended
2025-08-14T15:18:44.442990Z TRACE request buffered [0.0119ms]
Buffer {
    buffer: [
        Parse(
            Parse {
                name: "__pgdog_16",
                query: "/* pgdog_shard: 1 */ SELECT * FROM \"system\".\"technical_model\"",
                modified: true,
            },
        ),
        Bind(
            Bind {
                portal: b"\0",
                statement: b"__pgdog_16\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,
        ),
    ],
}
2025-08-14T15:18:44.443025Z DEBUG parser is enabled
2025-08-14T15:18:44.443028Z DEBUG /* pgdog_shard: 1 */ SELECT * FROM "system"."technical_model"
2025-08-14T15:18:44.443030Z 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: 28,
                                                                        },
                                                                    ),
                                                                ),
                                                            },
                                                        ),
                                                        location: 28,
                                                    },
                                                ),
                                            ),
                                        },
                                    ],
                                    from_clause: [
                                        Node {
                                            node: Some(
                                                RangeVar(
                                                    RangeVar {
                                                        catalogname: "",
                                                        schemaname: "system",
                                                        relname: "technical_model",
                                                        inh: true,
                                                        relpersistence: "p",
                                                        alias: None,
                                                        location: 35,
                                                    },
                                                ),
                                            ),
                                        },
                                    ],
                                    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: 0,
            },
        ],
    },
    warnings: [],
    tables: [
        (
            "system.technical_model",
            Select,
        ),
    ],
    aliases: {},
    cte_names: [],
    functions: [],
    filter_columns: [],
}
2025-08-14T15:18:44.443194Z DEBUG fingerprint: 18296ba66c13a696
2025-08-14T15:18:44.443200Z DEBUG query router decision: Query(
    Route {
        shard: All,
        read: true,
        order_by: [],
        aggregate: Aggregate {
            targets: [],
            group_by: [],
        },
        limit: Limit {
            limit: None,
            offset: None,
        },
        lock_session: false,
        distinct: None,
    },
)
2025-08-14T15:18:44.443216Z TRACE [172.18.0.5:47460] <-- 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,
}

Somehow doesn't detect the shard comment and thinks it has to route it everywhere. Other requests are routing to the correct shard fine (1).

[levkk]

Comments in prepared statements are ignored. This is because adding comments there would make the prepared statement cache very large, both on your end(your driver creates a cache in-memory) and on ours.

[lc-guy]

Comments in prepared statements are ignored. This is because adding comments there would make the prepared statement cache very large, both on your end(your driver creates a cache in-memory) and on ours.

I am fairly sure this is a regular, non-prepared statement (at least 99% sure having the code in front of me, unless my ORM is doing something very out of left field). Is there anything in the log that gives this away as a prepared statement?

[niclaflamme]

Parse + Bind + Execute + Sync has a prepared statement.
Otherwise, the protocol message would be Query.

Most ORMs default to prepared statements to prevent SQL injections.
What is your ORM?

[lc-guy]

I've figured it out, PEBKAC error. :)

This happens on Java, using the official JDBC Postgres driver. Not an ORM issue, actually, it's lower down.

The parameter preferQueryMode is set, by default, to extended, which will use the extended protocol for ALL queries, including simple SELECTs and stuff with no bindings. I had to change it to extendedForPrepared, which only uses it for PreparedStatement objects.

Might be worth mentioning somewhere in the docs? This risks tripping up people using pgdog with Java applications.

[levkk]

Documented in pgdogdev/docs#65