BrokenPipe: Broken pipe (os error 32) not handled in reconnect logic

[meyer9]

I'm currently getting a crash sometimes when connecting to a remote database.

BrokenPipe: Broken pipe (os error 32)
    at write (ext:deno_net/01_net.js:34:21)
    at TcpConn.write (ext:deno_net/01_net.js:97:12)
    at BufWriter.flush (https://deno.land/[email protected]/io/buffer.ts:745:40)
    at Connection.#preparedQuery (https://deno.land/x/[email protected]/connection/connection.ts:864:27)
    at eventLoopTick (ext:core/01_core.js:183:11)
    at async Connection.query (https://deno.land/x/[email protected]/connection/connection.ts:951:16)
    at async Client.#executeQuery (https://deno.land/x/[email protected]/client.ts:245:12)
    at async Client.queryObject (https://deno.land/x/[email protected]/client.ts:433:12)
    at async Db.executeSql (file:///src/_shared/queue/db.ts:7:20)
    at async Manager.createJob (file:///src/_shared/packages/pg-boss/manager.js:490:20)

Seems like there's a check for connection errors that will reconnect, but not broken pipe errors.

[tsaxking]

I am getting this one too. Is there any update on it?

[bombillazo]

Hey, would be helpfully to check what version is being used and if it happens in the newest.

[tsaxking]

Sure thing, I'm using 1.17.0, but I now see there's 1.17.2, I'm switching now, but I won't be able to recreate the problem for a few hours. My deno version is 1.40.3.

BrokenPipe: Broken pipe (os error 32)
    at write (ext:deno_net/01_net.js:58:21)
    at TcpConn.write (ext:deno_net/01_net.js:130:12)
    at BufWriter.flush (https://deno.land/[email protected]/io/buffer.ts:745:40)
    at Connection.#preparedQuery (https://deno.land/x/[email protected]/connection/connection.ts:864:27)
    at eventLoopTick (ext:core/01_core.js:64:7)
    at async Connection.query (https://deno.land/x/[email protected]/connection/connection.ts:951:16)
    at async Client.#executeQuery (https://deno.land/x/[email protected]/client.ts:245:12)
    at async Client.queryObject (https://deno.land/x/[email protected]/client.ts:433:12)
    at async file:///.../master/server/utilities/databases.ts:738:32
    at async attemptAsync (file:///.../master/shared/check.ts:134:23) {
  name: "BrokenPipe",
  code: "EPIPE"
}

[dylanpyle]

Same here, using 0.19.2

[mahdiyari]

Latest versions of deno and postgres as of writing this comment. Issue is still there. It happens with transactions that take too long.

[mahdiyari]

I found the actual reason behind it. Whenever you create a transaction and then leave that transaction idle longer than idle_in_transaction_session_timeout, Posgresql server will close the session. And that causes the above error.

[dylanpyle]

I think that's not the only case it can happen; for example, we've been hitting this on Google Cloud SQL where the default value for idle_in_transaction_session_timeout is apparently 0 (no timeout).

I believe the fundamental issue here is closer to #355 (comment) ; certain types of errors are just not correctly recovered from in cases like transaction.begin() calls. For what it's worth, we ended up with some kinda-gross brute force error handling around a number of database calls, which has appeared to reliably fix it for the last couple of months.

We're written a client wrapper around database access, where every call to things like:

transaction.queryObject
transaction.begin()
transaction.commit()
transaction.rollback()
pool.connect()

— has a .catch(handleQueryError) chained off it, approximately:

class DbClient {
  ...

  private pool: Pool | null = null;

  public getPool = async (): Promise<PoolClient> => {
    if (!this.pool) {
      this.pool = new Pool(this.connectionString, this.connectionPoolSize);
    }

    return await this.pool.connect()
      .catch(this.handleQueryError);
  };

  public closePool = async (): Promise<void> => {
    if (!this.pool) {
      throw new Error("Connection pool has not been created");
    }

    await this.pool.end();
    this.pool = null;
  };

  private handleQueryError = async (err: Error): Promise<never> => {
    const isConnectionSlotError = err instanceof PostgresError &&
      err.message.includes("remaining connection slots are reserved");

    const isPotentialConnectionError = err instanceof Deno.errors.BrokenPipe ||
      err instanceof Deno.errors.ConnectionRefused ||
      err instanceof Deno.errors.ConnectionReset ||
      err instanceof Deno.errors.ConnectionAborted ||
      err instanceof ConnectionError ||
      isConnectionSlotError;

    if (isPotentialConnectionError) {
      await this.closePool();
    }

    throw err;
  };

  ... etc etc ...
}