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/std@0.160.0/io/buffer.ts:745:40)
    at Connection.#preparedQuery (https://deno.land/x/postgres@v0.17.0/connection/connection.ts:864:27)
    at eventLoopTick (ext:core/01_core.js:183:11)
    at async Connection.query (https://deno.land/x/postgres@v0.17.0/connection/connection.ts:951:16)
    at async Client.#executeQuery (https://deno.land/x/postgres@v0.17.0/client.ts:245:12)
    at async Client.queryObject (https://deno.land/x/postgres@v0.17.0/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/std@0.160.0/io/buffer.ts:745:40)
    at Connection.#preparedQuery (https://deno.land/x/postgres@v0.17.0/connection/connection.ts:864:27)
    at eventLoopTick (ext:core/01_core.js:64:7)
    at async Connection.query (https://deno.land/x/postgres@v0.17.0/connection/connection.ts:951:16)
    at async Client.#executeQuery (https://deno.land/x/postgres@v0.17.0/client.ts:245:12)
    at async Client.queryObject (https://deno.land/x/postgres@v0.17.0/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 ...
}

(edit: update, this did improve things, but there were still some holes - we reluctantly switched back to npm's pg a few months later and have avoided this whole category of issues since then)

[songkeys]

I'm also running on Google Cloud SQL. I just have to restart my service every hour to solve this issue.

[asciiwhite]

I also have an similar issue with a BrokenPipe Error with v0.19.5 and Deno 2.3.6.

BrokenPipe: Broken pipe (os error 32)
    at Object.write (ext:deno_web/06_streams.js:1181:20)
    at Module.invokeCallbackFunction (ext:deno_webidl/00_webidl.js:1116:16)
    at WritableStreamDefaultController.writeAlgorithm (ext:deno_web/06_streams.js:3952:14)
    at writableStreamDefaultControllerProcessWrite (ext:deno_web/06_streams.js:4522:55)
    at writableStreamDefaultControllerAdvanceQueueIfNeeded (ext:deno_web/06_streams.js:4425:5)
    at writableStreamDefaultControllerWrite (ext:deno_web/06_streams.js:4569:3)
    at writableStreamDefaultWriterWrite (ext:deno_web/06_streams.js:4716:3)
    at WritableStreamDefaultWriter.write (ext:deno_web/06_streams.js:6618:12)
    at Connection.#appendArgumentsToMessage (file:///home/cvix/workspace/postgres/connection/connection.ts:829:30)
    at Connection.#preparedQuery (file:///home/cvix/workspace/postgres/connection/connection.ts:888:41)
    at Connection.#query(file:///home/cvix/workspace/postgres/connection/connection.ts:980:41)

The catch block in Connection.query() does not handle the BrokenPipe error and so does not end the connection which is important for an correct startup() call for following query() calls. So a reconnection is not handled correctly.

A local fix inside Connection.query() and Connection.end() solved the issue.

Connection.query()

catch (e) {
      if (e instanceof ConnectionError ||
          e instanceof Deno.errors.BrokenPipe) {
        await this.end();
      }
      throw e;
}

Connection.end()

if (this.connected) {
      const terminationMessage = new Uint8Array([0x58, 0x00, 0x00, 0x00, 0x04]);
      try {
        await this.#connWritable.write(terminationMessage);
        await this.#connWritable.ready;
      } catch (_e) {
        // This steps can fail if the underlying connection was closed ungracefully
      } finally {
        this.#closeConnection();
        this.#onDisconnection();
      }
 }

[ntninja]

Hallo! I have created #510, that should fix this properly by attempting to reconnect once when receiving BrokenPipe for free-standing commands and throwing TransactionError in transaction commands.

You can temporarily use the patched version by replacing "jsr:@db/postgres@0.19.5" with "https://esm.sh/gh/ntninja/deno-db-postgres" in your deno.json dependency list.