README.rst

About

Investigate parameterized INSERT statements with RETURNING clauses using node-postgres.

• https://node-postgres.com/
• https://github.com/brianc/node-postgres

Observations

When running this program using --parameterized --returning, we observe bad behavior like:

(node:14790) TypeError: Cannot read property 'name' of undefined
    at Result.parseRow (/Users/amo/dev/crate/troubleshooting/npm-pg-001/node_modules/pg/lib/result.js:66:34)
    at Query.handleDataRow (/Users/amo/dev/crate/troubleshooting/npm-pg-001/node_modules/pg/lib/query.js:87:26)
    at Client._handleDataRow (/Users/amo/dev/crate/troubleshooting/npm-pg-001/node_modules/pg/lib/client.js:345:22)
    at Connection.emit (events.js:315:20)
    at /Users/amo/dev/crate/troubleshooting/npm-pg-001/node_modules/pg/lib/connection.js:115:12
    at Parser.parse (/Users/amo/dev/crate/troubleshooting/npm-pg-001/node_modules/pg-protocol/dist/parser.js:40:17)
    at Socket.<anonymous> (/Users/amo/dev/crate/troubleshooting/npm-pg-001/node_modules/pg-protocol/dist/index.js:10:42)
    at Socket.emit (events.js:315:20)
    at addChunk (_stream_readable.js:309:12)
    at readableAddChunk (_stream_readable.js:284:9)
(node:14790) [DEP0018] DeprecationWarning: Unhandled promise rejections are deprecated. In the future, promise rejections that are not handled will terminate the Node.js process with a non-zero exit code.
    at emitDeprecationWarning (internal/process/promises.js:180:11)
    at processPromiseRejections (internal/process/promises.js:249:13)
    at processTicksAndRejections (internal/process/task_queues.js:94:32)

When running this program using --parameterized --returning --improve, the program occasionally succeeds, but there is still flakyness oscillating between the error above, success and this error:

/Users/amo/dev/crate/troubleshooting/npm-pg-001/node_modules/pg/lib/client.js:345
    this.activeQuery.handleDataRow(msg)
                     ^

TypeError: Cannot read property 'handleDataRow' of null
    at Client._handleDataRow (/Users/amo/dev/crate/troubleshooting/npm-pg-001/node_modules/pg/lib/client.js:345:22)
    at Connection.emit (events.js:315:20)
    at /Users/amo/dev/crate/troubleshooting/npm-pg-001/node_modules/pg/lib/connection.js:115:12
    at Parser.parse (/Users/amo/dev/crate/troubleshooting/npm-pg-001/node_modules/pg-protocol/dist/parser.js:40:17)
    at Socket.<anonymous> (/Users/amo/dev/crate/troubleshooting/npm-pg-001/node_modules/pg-protocol/dist/index.js:10:42)
    at Socket.emit (events.js:315:20)
    at addChunk (_stream_readable.js:309:12)
    at readableAddChunk (_stream_readable.js:284:9)
    at Socket.Readable.push (_stream_readable.js:223:10)
    at TCP.onStreamRead (internal/stream_base_commons.js:188:23)

Success with "native"

When using the "native" driver through --parameterized --returning --native, the program will always succeed.

Evaluation

• Using --native is currently the best option.
• Using --binary does not have any influence.
• --improve is just a nasty workaround adding two SELECT 1 statements to the mix. This is obviously not a solution (the error nevertheless still happens anyway) but is here in order to demonstrate that the program succeeds sometimes. We can figure from this that the behavior somehow seems to depend on async timing details.

cratedb-nodejs-parameterized-returning.js

/*
About
=====
Investigate parameterized INSERT statements with RETURNING clauses using `node-postgres`.

- https://node-postgres.com/
- https://github.com/brianc/node-postgres


Setup
=====
::

  npx yarn add arg pg


Setup "pg-native"
=================
::

  export PATH=/usr/local/Cellar/libpq/13.1/bin:$PATH
  npx yarn add pg-native


Synopsis
========
::

  # Run database.
  docker run -it --publish 5432:5432 crate:4.3.2
  
  # Run workload program.
  node --trace-warnings cratedb-nodejs-parameterized-returning.js [--parameterized] [--returning] [--native] [--binary] [--improve]

*/


const arg = require('arg');
var pg;
var pool;


// The database workload.
async function workload(options) {
  const client = await pool.connect()
  await client.query("DROP TABLE IF EXISTS testdrive; CREATE TABLE testdrive (foo TEXT);")
  
  if (!options.returning) {
    if (!options.parameterized)
      await client.query("INSERT INTO testdrive (foo) VALUES ('bar')")

    else
      await client.query("INSERT INTO testdrive (foo) VALUES ($1)", ["bar"])
  
  } else {
    if (!options.parameterized)
      await client.query({text: "INSERT INTO testdrive (foo) VALUES ('bar') RETURNING *", rowMode: "array"})

    else
      await client.query({text: "INSERT INTO testdrive (foo) VALUES ($1) RETURNING *", rowMode: "array"}, ["bar"])
  }

  // Those lines will slightly improve behavior with "--parameterized --returning".
  if (options.improve) {
    await client.query("SELECT 1")
    await client.query("SELECT 1")
  }

  await client.query("REFRESH TABLE testdrive")
  var result = await client.query("SELECT * FROM testdrive")
  console.log(result.rows);
  
  await client.release()
  await pool.end()
}


// Just a little helper.
function normalize_options(options) {
  var normalized = {};
  for (const [key, value] of Object.entries(options)) {
    normalized[key.replace("--", "")] = value;
  }
  return normalized;
}


function main() {

  // Parse command line arguments.
  const options = normalize_options(arg({
    '--native': Boolean,
    '--binary': Boolean,
    '--parameterized': Boolean,
    '--returning': Boolean,
    '--improve': Boolean,
  }));

  // Import library. Either pure-JS or native.
  if (options.native)
    pg = require("pg").native;
  else
    pg = require("pg");

  // Compute database connection options. URI plus (optionally) binary serialization.
  var dboptions = {connectionString: "postgresql://crate@localhost"};
  if (options.binary)
    dboptions.binary = true;

  // Create a pool.
  pool = new pg.Pool(dboptions);

  // Run the database workload.
  workload(options);

}


if (require.main === module) {
  main();
}