Low-level prepared statements API (PQprepare, PQdescribePrepared)

[benny-medflyt]

From the docs it looks like prepared statements can only be created and then immediately executed.

I want to create a prepared statement without executing it, and I also need the equivalent of PQdescribePrepared

I am trying to do something similar to the \gdesc command of psql: https://github.com/postgres/postgres/blob/e24a815c1c8550fcba5cc5aeb0d130db46570872/src/bin/psql/common.c#L1569

[benny-medflyt]

Looks like this is a duplicate of #1236

[benny-medflyt]

@brianc could you please provide some pointers or guidance on how one would go about implementing this in node-pg? Thank you

[brianc]

@benny-medflyt sure no problemo

I think what you want for prepare is here

and describe is implemented here

The Connection class is basically an event emitter. Some of the patterns in there are not 100% idiomatic to exact node standards these days because they originated before node had some of the idioms it has today, so apologies if it's a bit hard to follow. If you wanted to add a more "blessed" api to the Client instance for something like client.describe and client.prepare I'd be down with that, as using client.connection is pretty awkward and not documented. Right now these things are more so wrapped up in Client behaviors as they're used to execute prepared statements and so on, but I could see exposing them as valuable. The one caveat is if there's no analog (or exposed analog) in the native bindings then we should probably just throw on the native client and say "Not implemented in native bindings currently! pull requests welcome!" though there should be support for these functions in node-libpq - actually it looks like prepare is there but describe doesn't exist yet.

[benny-medflyt]

Thanks. I've had some time to look at all this, and I've made some progress.

I will share some code I was able to come up with.

Here is a function to create a prepared statement (without executing it):

/**
 * Submits a request to create a prepared statement
 *
 * See:
 * <https://www.postgresql.org/docs/current/libpq-exec.html#LIBPQ-PQPREPARE>
 *
 * @param {pg.Client} client
 * @param {string} name
 * @param {string} text
 * @param {Function} cb
 */
function pgPrepareQuery(client, name, text, cb) {
    const PrepareQuery = function () {
        pg.Query.call(this, {});
    }

    PrepareQuery.prototype = Object.create(pg.Query.prototype);
    PrepareQuery.prototype.constructor = PrepareQuery;

    PrepareQuery.prototype.submit = function (connection) {
        connection.parse({
            name: name,
            text: text,
            types: []
        }, false);

        connection.sync();
    }

    client.query(new PrepareQuery(), cb);
}

The "describe prepared" is more difficult, because Connection.prototype.parseMessage is missing a case for the 't' (0x74 ParameterDescription).

What I have chosen to do for now is to monkey patch this function, so that it just ignores that message:

/**
 * Patches the connection object so that it won't crash when it receives a
 * `ParameterDescription (B)` message from the backend. (The message will be
 * ignored)
 */
function pgMonkeyPatchConnection(connection) {
    const origParseMessage = connection.parseMessage;
    connection.parseMessage = function (buffer) {
        if (this._reader.header == 0x74) { // 't'
            this.offset = 0
            var length = buffer.length + 4

            return {
                name: 'parameterDescription',
                length: length
            };
        } else {
            return origParseMessage.call(this, buffer);
        }
    }
}

After a connection has been monkey-patched, we can issue a "describe prepared" statement. Here is a function that does it:

/**
 * Submits a request to obtain information about the specified prepared
 * statement
 *
 * See:
 * <https://www.postgresql.org/docs/current/libpq-exec.html#LIBPQ-PQDESCRIBEPREPARED>
 *
 * @param {pg.Client} client
 * @param {string} name
 * @param {Function} cb The result may be `null` if the prepared statement
 * returns no results (for example an INSERT statement)
 */
function pgDescribePrepared(client, name, cb) {
    var rowDescription = null;

    const DescribePrepared = function () {
        pg.Query.call(this, {
        }, function (err) {
            if (err) {
                cb(err);
                return;
            }
            cb(null, rowDescription);
        });
    }

    DescribePrepared.prototype = Object.create(pg.Query.prototype);
    DescribePrepared.prototype.constructor = DescribePrepared;

    DescribePrepared.prototype.submit = function (connection) {
        connection.describe({
            type: 'S',
            name: name
        });

        connection.sync();
    }

    DescribePrepared.prototype.handleRowDescription = function (msg) {
        rowDescription = msg;
    };

    client.query(new DescribePrepared());
}

This will successfully provide the information about the result columns of the prepared statement. Unfortunately, because we are ignoring the "ParameterDescription" message from the backend, we don't get the info about the parameters of the prepared statement. Adding code to parse the "ParameterDescription" message (instead of ignoring it) should not be too difficult.

For now, this code is working for my needs. It would be great if the functionality could be added to this library, but I am still a bit confused as to how best to add it.

[kklin]

We needed to do the same thing, here's an updated version of the code that uses Promises and the new pg interfaces.

Thanks @benny-medflyt for the approach!

import pg, { Client, Connection } from "pg";

type DescribePreparedMessage = {
  fields: {
    name: string;
    dataTypeID: number;
  }[];
};

async function main() {
  const client = new Client();
  await client.connect();

  await client.query("prepare q as select 1::text as foo");
  console.log(await pgDescribePrepared(client, "q"));
  await client.end();
}

class DescribePrepared extends pg.Query {
  constructor(private name: string, private cb: (res, err) => void) {
    super({});
  }

  submit(connection: Connection) {
    connection.describe({
      type: "S",
      name: this.name,
    });
    connection.sync();
  }

  handleError(err) {
    this.cb(null, err);
  }

  handleRowDescription(msg) {
    this.cb(msg, null);
  }
}

function pgDescribePrepared(client, name): Promise<DescribePreparedMessage> {
  return new Promise((resolve, reject) => {
    client.query(
      new DescribePrepared(name, (res, err) => {
        if (err) {
          reject(err);
        } else {
          resolve(res);
        }
      }),
    );
  });
}

main();

[brianc]

The "describe prepared" is more difficult, because Connection.prototype.parseMessage is missing a case for the 't' (0x74 ParameterDescription).

I thought I added that a while ago. I can add that (and should) to the protocol parser pretty easily.

[kklin]

@brianc I assume you did 😄 I didn't have to do any monkey patching on the message parsing side, I just added the new Query with a custom submit and handlers

[abenhamdine]

if someone plans to work on this, IMHO it would be relevant to add feature to release prepared statements as well, see https://github.com/brianc/node-postgres/issues/1889