Unexpected NULL timestamp during some queries

[mdhornet90]

I'm using pg v8.12.0, and I'm noticing that in some cases where I'm executing queries a timestamp (with time zone) that's expected to have a value always returns NULL.

The error manifests when I run a large SELECT * FROM table WHERE id in ('about', 'onehundred', 'ids'); query. If I pick one id out of the array and use pg to run that same query again with only one element in the array, I get the timestamp as expected. I also ran this same one-hundred-elem-array query directly against Postgres and could not reproduce this issue, so it seems to be solely the fault of pg. Any ideas what might be happening?

Here's how I'm using pg for reference:

declare namespace DB {
  class User {
    email: string;
    email_verified: boolean;
    password: string;
    attempt_count: number;
    batch_id?: number;
    uploaded_at?: Date; // This timestamptz always returns a value
    job_id?: string;
    linked_at?: Date; // This one never does
  }
}
...
export async function executeQuery<O, I = DB.User>(
  pool: pg.Pool,
  queryString: string,
  transformFn?: (e: I) => O
): Promise<O[]> {
  const client = await pool.connect();
  try {
    const { rows } = await client.query(queryString);
    if (transformFn) {
      return rows.map(transformFn);
    }
    return rows;
  } catch (err: any) {
    logError(err);
    throw err;
  } finally {
    client.release();
  }
}

// USAGE:
await executeInexpensiveQuery(
    pool,
    `SELECT * FROM upload_data WHERE id IN (${userIds.map(id => `'${id}'`).join(',')});`,
)

I know the docs say to not inline those parameters but none of this is injectable. Still, I can try the prescribed way and report back.

[mdhornet90]

I have some updates based on further testing:

1. This problem actually presents itself with just one element in that dynamic array. I have no idea why a formatted string would cause problem as opposed to a hard-coded string.
2. Using values instead of the inline mapping makes no difference.

[brianc]

thanks for reporting this & sorry you're running into that. would you be able to supply a fully self-contained & exeutable node script (I have postgres locally. dont need docker compose or anything) that reproduces the issue? if so i canwrite & test & fix it.

On Tue, Jul 2, 2024 at 11:56 AM Chris @.***> wrote:

I have some updates based on further testing:

1. This problem actually presents itself with just one element in that dynamic array. I have no idea why a formatted string would cause problem as opposed to a hard-coded string.
2. Using values instead of the inline mapping makes no difference.

— Reply to this email directly, view it on GitHub https://github.com/brianc/node-postgres/issues/3274#issuecomment-2203841515, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAAMHINCLQLUVQD62ZBLCNTZKLLUBAVCNFSM6AAAAABKH4QZOCVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDEMBTHA2DCNJRGU . You are receiving this because you are subscribed to this thread.Message ID: @.***>

[mdhornet90]

I appreciate the fast response. I have some client deadlines I need to meet but as soon as I have a free moment I'll send over a POC.

Also just some more context in case this makes a difference or jogs your memory: I'm using the pg-query-stream package for an initial large request against the Pool, and send the results of that query through an awaited pipeline. In every case I interact with the database, I follow the pattern above of connecting a client to the pool and releasing it when I'm done.