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] https://github.com/brianc/node-postgres/issues/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.

[mattfysh]

Has this regressed?

When upgrading from v0.2.13 a ReadyForQuery response is added to the end of all execProtocolRaw return buffers

As mentioned by @gregnr https://github.com/electric-sql/pglite/issues/223#issuecomment-2317958414

I'm using psycopg which raises the error psycopg.InternalError: got no result from the query when encountering this message unexpectedly

[pmp-p]

| Has this regressed?

@mattfysh : it would be really usefull to have a psycopg3-async test from yours, because indeed one change was made to accomodate psycopg pipelining mode when using pglite via a socket gateway.

[mike-marcacci]

I spent some time today getting the (fantastic) postgres.js client to connect to PGlite via pg-gateway, and I ran into this issue.

It appears that the latest version continues to append ReadyForQuery message to each response, causing undefined behavior and ultimately hanging in that client. @gregnr your filter really saved the day - thanks for that.

I also want to confirm: is this something that clients should handle? I'm not an expert in the postgres wire protocol, but reading through the protocol flow this seems like clearly incorrect behavior on the part of PGlite:

Since a query string could contain several queries (separated by semicolons), there might be several such response sequences before the backend finishes processing the query string. ReadyForQuery is issued when the entire string has been processed and the backend is ready to accept a new query string.

My takeaway is that the client shouldn't need any special handling for this case?

Relevant links:

• My proof-of-concept
• The corresponding client issue

[mattfysh]

Rather concerned this issue hasn't progressed. anything higher than v0.2.13 simply doesn't work with a range of clients due to the incompatibility with the official wire protocol.

[samwillis]

Hey @mattfysh

This has reached the top of my list. I'm going to dig in over the next few days to understand the problem.

[pmp-p]

please someone confirm latest fixes the problem for good.

[joehan]

I think I am still running into this issue on 0.3.3 - when connecting with https://github.com/lib/pq, and running some migration statements generated by Atlas, it seems like extra Ready for Queries are still being sent. I added some logging to take a look at the full exchange:

Front: Q;
Back: I
Back: ZI
Front: QlSELECT setting FROM pg_settings WHERE name IN ('server_version_num', 'crdb_version') ORDER BY name DESC
Back: T setting/H������
Back: NrSLOGVLOGC00000Mgetsockopt(TCP_KEEPCNT) failed: Bad file descriptorFpqcomm.cL1902Rpq_getkeepalivescount
Back: NrSLOGVLOGC00000Mgetsockopt(TCP_KEEPIDLE) failed: Bad file descriptorFpqcomm.cL1734Rpq_getkeepalivesidle
Back: NwSLOGVLOGC00000Mgetsockopt(TCP_KEEPINTVL) failed: Bad file descriptorFpqcomm.cL1819Rpq_getkeepalivesinterval
Back: NvSLOGVLOGC00000Mgetsockopt(TCP_USER_TIMEOUT) failed: Bad file descriptorFpqcomm.cL1977Rpq_gettcpusertimeout
Back: D160004
SELECT 1
Back: ZI
Front: P�
SELECT
	nspname AS schema_name,
	pg_catalog.obj_description(oid) AS comment
FROM
    pg_catalog.pg_namespace
WHERE
    nspname = $1
ORDER BY
    nspname
Back: N�SDEBUGVDEBUGC00000Mparse <unnamed>:
SELECT
	nspname AS schema_name,
	pg_catalog.obj_description(oid) AS comment
FROM
    pg_catalog.pg_namespace
WHERE
    nspname = $1
ORDER BY
    nspnameFpostgres.cL1425Rexec_parse_message
Back: 1
Back: ZI  ## I think this is unexpected?
Front: DS
Back: t

Back: T>schema_name
7@����comment������
Back: ZI.  ## Or maybe this one?
Front: S
Back: ZI
Front: X