Skip to content

Long queries with parameters #303

Closed
Closed
@eugene1g

Description

@eugene1g

I have a custom query builder to help users run complex queries against the database using parameters to apply various filters. This query builder can generate really long queries, but not long enough to be a problem for PG. However, running those long queries with postgres doesn't work right now when those queries use parameters.

My real code is too complex to be useful, but here's a simple contrived example -

// test.mjs
import postgres from "postgres";

const sql = postgres({
  // The issue happens when two queries are executed sequentially over the same connection
  // It also happens with any number of connections, but easier to demo with a single network connection to schedule the queries predictably
  max: 1,
});

const padding = ".".repeat(16_342);

const res = await Promise.all([
  // For the bug to surface, the query needs to be 16,359 chars or longer
  // We'll create a "large" query to go first over the wire to PG
  // If we reduce the padding even by one character, everything works as expected (try removing the whitespace in the closing ` */`
  sql.unsafe(`SELECT 0+$1 /*${padding} */`, [1]),
  // Any following queries will fail when it runs over the same network connection
  sql.unsafe(`SELECT 0+$1+$2+$3`, [1, 2, 3]),
]);

console.log(res);
process.exit(0);

The specific error I get is -

PostgresError: bind message supplies 1 parameters, but prepared statement "" requires 2
    at ErrorResponse (..../postgres/cjs/src/connection.js:775:26)
    at handle (..../node_modules/postgres/cjs/src/connection.js:484:6)
    at Socket.data (..../node_modules/postgres/cjs/src/connection.js:328:9)
    at Socket.emit (node:events:527:28)
    at Socket.emit (node:domain:475:12)
    at addChunk (node:internal/streams/readable:324:12)
    at readableAddChunk (node:internal/streams/readable:297:9)
    at Socket.Readable.push (node:internal/streams/readable:234:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23)

It vaguely feels like the problem would be in the network serialization logic - perhaps some Buffer is overflowing or not being cleared - but I haven't narrowed this down further.

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions