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. — John Powers
– John Powers, Commented Feb 11, 2020 at 19:16

Ry- · Accepted Answer · 2018-01-03 00:19:15Z

3

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.

answered Jan 3, 2018 at 0:19

Ry-♦

226k56 gold badges496 silver badges504 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related