Node.js and Postgres: Temporary table "relation does not exist" on chained query

Question

I'm trying to create a temporary table, then execute a SELECT query on it using the node-pg library. Using promises and .then(), I wait for the table to be created (or it seems that should be the case), then do my selection query:

  const tempText = `
    CREATE TEMPORARY TABLE IF NOT EXISTS temp1 AS
    (
      SELECT colA, colB
      FROM foo
      GROUP BY colA, colB
    )
  `;

  const queryText = `
    SELECT colA, COUNT(colA) as total
    FROM temp1
    GROUP BY colA
    ORDER BY COUNT(colA) DESC
  `;
  // using pooling
  return db.query({ text: tempText, values })
    .then(() => {
      return db.query(queryText).then(({ rows }) => rows);
    })
    .catch((err) => { throw err; });

This code is all lives inside a GraphQL resolver. The trouble is, the first time I hit it via an API, I get "relation \"temp1\" does not exist", But all subsequent calls are successful and return data. Why would this be? I'm waiting for the first query to finish before doing the SELECT

Combine the two statements with the WITH operator as explained [here](https://stackoverflow.com/questions/2974057/move-data-from-one-table-to-another-postgresql-edition). — John Powers, Feb 11 '20 at 19:16

score 3 · Accepted Answer · answered Jan 03 '18 at 00:19

Temporary tables are specific to a session, and separate db.query() calls aren’t guaranteed to run in the same session, since db is a pool that acquires a client separately for each query() call. Grab a client explicitly:

return db.connect().then(async client => {
  try {
    await client.query({ text: tempText, values });
    const result = await client.query(queryText);
    return result.rows;
  } finally {
    client.release();
  }
});

Note also that .catch((err) => { throw err; }) doesn’t do anything.

Node.js and Postgres: Temporary table "relation does not exist" on chained query

1 Answers1