Can't seem to use prepared statements using libpq

[willemvlh]

I'm trying to use pglite with a libpq-based application. I'm using pg-gateway as a proxy.

Following sample C code snippet (which calls PQprepare and PQexecPrepared) seems not work with pglite.

  int main(int argc, char** argv) {
    char conninfo[200];
    const char *port = argv[1];
    sprintf(conninfo, "host=localhost port=%s dbname=postgres user=psi password=abc", port);
    PGconn     *conn;
    PGresult   *res;

    const char *paramValues[1];
    paramValues[0] = argv[2];

    conn = PQconnectdb(conninfo);

    const char *stmtName = "my_statement";
    const char *sql = "select $1";

    res = PQprepare(conn, stmtName, sql, 1, NULL);
    PQclear(res);
    res = PQexecPrepared(conn, stmtName, 1, paramValues, NULL, NULL, 0);

    if (PQresultStatus(res) != PGRES_TUPLES_OK) {
        fprintf(stderr, "Execution of statement failed: %s", PQerrorMessage(conn));
        PQclear(res);
        PQfinish(conn);
        exit(1);
    }

    printf("%s\n", PQgetvalue(res, 0, 0));
    PQclear(res);
    PQfinish(conn);
    return 0;
}

When I run this using a normal Postgres database I get the expected result:

willem@FH3T244:~/test$ ./pg 5432 hello
hello
willem@FH3T244:~/test$

When I use pglite, I get this:

willem@FH3T244:~/test$ ./pg 5433 hello
DEBUG:  parse my_statement: select $1
DEBUG:  bind <unnamed> to my_statement
Execution of statement failed: 

Code snippet from the server, mostly borrowed from https://github.com/supabase-community/pg-gateway?tab=readme-ov-file#pglite:

 async onMessage(data, { isAuthenticated }) {
            if (!isAuthenticated) {
                return false;
            }
            try {
                const d = await db.execProtocolRaw(data);
                socket.write(d);
            } catch (err) {
                console.log(err)
                connection.sendError(err);
                connection.sendReadyForQuery();
            }
...

Seems to happen on both Linux and Windows. When I don't use prepared statements (e.g. by using PQexec), it seems to work as expected.

[davebond]

I think we're having the same issue.

We're using node-postgres [0] -> pg-gateway next branch [1] -> pgLite

Without prepared statements this works fine, all prepared statements fail.

When using the native driver (libpq) queries fail and the logs are filled with these notices:

message type 0x32 arrived from server while idle
message type 0x5a arrived from server while idle
message type 0x54 arrived from server while idle
message type 0x5a arrived from server while idle
message type 0x44 arrived from server while idle
message type 0x43 arrived from server while idle
message type 0x5a arrived from server while idle

It also fails when using the javascript driver with the following:

TypeError: Cannot read properties of null (reading 'handleRowDescription')

[2]

According to [3] activeQuery should only be null once a readyForQuery message has been received and this could be caused by the messages being received out of order. This is only an issue (so far) with prepared queries so I would assume there is a fault somewhere in handling the extended query protocol [4].

I've not narrowed down whether this is caused by pg-gateway or pgLite but hopefully this context is useful to someone.

[0] https://node-postgres.com/
[1] https://github.com/supabase-community/pg-gateway/tree/next
[2] https://github.com/brianc/node-postgres/blob/54eb0fa216aaccd727765641e7d1cf5da2bc483d/packages/pg/lib/client.js#L359
[3] brianc/node-postgres#3174
[4] https://www.postgresql.org/docs/current/protocol-flow.html#PROTOCOL-FLOW-EXT-QUERY

[gregnr]

I noticed that PGlite will return a ReadyForQuery after every message in the extended query series vs only at the end after a Sync.

Here is what I saw when connecting from pg (JS) -> pg-gateway -> PGlite:

... startup/auth messages

Backend message 'ReadyForQuery' <Buffer 5a 00 00 00 05 49>
Frontend message 'Parse' <Buffer 50 00 00 00 22 00 53 45 4c 45 43 54 20 24 31 3a 3a 74 65 78 74 20 61 73 20 6d 65 73 73 61 67 65 00 00 00>
Frontend message 'Bind' <Buffer 42 00 00 00 1e 00 00 00 01 00 00 00 01 00 00 00 0c 48 65 6c 6c 6f 20 77 6f 72 6c 64 21 00 00>
Frontend message 'Describe' <Buffer 44 00 00 00 06 50 00>
Frontend message 'Execute' <Buffer 45 00 00 00 09 00 00 00 00 00>
Frontend message 'Sync' <Buffer 53 00 00 00 04>
Backend message 'ParseComplete' <Buffer 31 00 00 00 04>
Backend message 'ReadyForQuery' <Buffer 5a 00 00 00 05 49>
Backend message 'BindComplete' <Buffer 32 00 00 00 04>
Backend message 'ReadyForQuery' <Buffer 5a 00 00 00 05 49>
Backend message 'RowDescriptionMessage' <Buffer 54 00 00 00 20 00 01 6d 65 73 73 61 67 65 00 00 00 00 00 00 00 00 00 00 19 ff ff ff ff ff ff 00 00>
Backend message 'ReadyForQuery' <Buffer 5a 00 00 00 05 49>
Backend message 'DataRow' <Buffer 44 00 00 00 16 00 01 00 00 00 0c 48 65 6c 6c 6f 20 77 6f 72 6c 64 21>
Backend message 'CommandComplete' <Buffer 43 00 00 00 0d 53 45 4c 45 43 54 20 31 00>
Backend message 'ReadyForQuery' <Buffer 5a 00 00 00 05 49>
Frontend message 'Terminate' <Buffer 58 00 00 00 04>

vs. pg -> pg-gateway -> vanilla PG instance running in Docker:

... startup/auth messages

Backend message 'ReadyForQuery' <Buffer 5a 00 00 00 05 49>
Frontend message 'Parse' <Buffer 50 00 00 00 22 00 53 45 4c 45 43 54 20 24 31 3a 3a 74 65 78 74 20 61 73 20 6d 65 73 73 61 67 65 00 00 00>
Frontend message 'Bind' <Buffer 42 00 00 00 1e 00 00 00 01 00 00 00 01 00 00 00 0c 48 65 6c 6c 6f 20 77 6f 72 6c 64 21 00 00>
Frontend message 'Describe' <Buffer 44 00 00 00 06 50 00>
Frontend message 'Execute' <Buffer 45 00 00 00 09 00 00 00 00 00>
Frontend message 'Sync' <Buffer 53 00 00 00 04>
Backend message 'ParseComplete' <Buffer 31 00 00 00 04>
Backend message 'BindComplete' <Buffer 32 00 00 00 04>
Backend message 'RowDescriptionMessage' <Buffer 54 00 00 00 20 00 01 6d 65 73 73 61 67 65 00 00 00 00 00 00 00 00 00 00 19 ff ff ff ff ff ff 00 00>
Backend message 'DataRow' <Buffer 44 00 00 00 16 00 01 00 00 00 0c 48 65 6c 6c 6f 20 77 6f 72 6c 64 21>
Backend message 'CommandComplete' <Buffer 43 00 00 00 0d 53 45 4c 45 43 54 20 31 00>
Backend message 'ReadyForQuery' <Buffer 5a 00 00 00 05 49>
Frontend message 'Terminate' <Buffer 58 00 00 00 04>

The extra ReadyForQuery messages from PGlite after ParseComplete, BindComplete, and RowDescriptionMessage seem to be what is tripping up clients. I tested manually removing these extra messages and it fixed the problem.

The above were tested using this pg JS snippet:

import pg from 'pg';

const { Client } = pg;
const client = new Client(
  'postgresql://postgres:postgres@localhost:5432/postgres',
);
await client.connect();

const res = await client.query('SELECT $1::text as message', ['Hello world!']);
console.log(res.rows);
await client.end();

[joehan]

@gregnr you mentioned:

The extra ReadyForQuery messages from PGlite after ParseComplete, BindComplete, and RowDescriptionMessage seem to be what is tripping up clients. I tested manually removing these extra messages and it fixed the problem.

Any chance you could share the code you used to test this? I'd love to crib from it while this issue is being fixed.

[gregnr]

@joehan if you're willing to use an experimental version of pg-gateway, I've created a branch feat/pglite-extended-query-patch with the patch. You can see how it's used via this test:
https://github.com/supabase-community/pg-gateway/blob/6834ab6db34b03fe51be6cb6296b587acea54ccd/packages/pg-gateway/test/node/tcp.test.ts#L28-L45

This is based off of the Web standard APIs PR which isn't merged yet, so unfortunately there's no release for this code yet (you would need to clone and build yourself).

I did try back porting the patch to work with the current v0.2.4 version of pg-gateway but kept hitting other upstream bugs that I'm guessing were inherently fixed in the Web standard APIs branch. Hoping to get that PR merged and released soon.