WingZer0o•8mo ago

Postgres Deno Connection Pool Idle timeout

I am using NeonPostgres and connecting to the database in Deno using the following below and executing this way. This connection is running in a Discord bot using Discordeno. I am entirely ruling out local network setup however the discord bot itself never disconnects. So that brings me here. After a certain time frame that I haven't determined quite yet. Maybe 20 minutes or so?

import { Pool } from "https://deno.land/x/postgres@v0.19.3/mod.ts";

const pool = new Pool({
  database: Deno.env.get("POSTGRES_DATABASE"),
  hostname: Deno.env.get("POSTGRES_HOST"),
  port: 5432,
  user: Deno.env.get("POSTGRES_USER"),
  password: Deno.env.get("POSTGRES_PASSWORD"),
  connection: {
    attempts: 10,
  },
  options: {
    
  }
}, 10);
export { pool };

import { QueryArrayResult } from "https://deno.land/x/postgres@v0.19.3/mod.ts";
import { pool } from "../database/database.ts";

export class ChatMessageRepository {

    public static async getChatMessagesByChannelIdAndUserId(channelId: bigint, userId: bigint): Promise<QueryArrayResult<unknown[]>> {
        const client = await pool.connect();
        const chatMesages = await client.queryArray(
            `SELECT "Content", "IsBot" FROM public."ChatMessages" WHERE "ChannelId" = ${channelId} AND "UserId" = ${userId} ORDER BY "CreatedAt" ASC;`,
          );
        client.release();
        return chatMesages;
    }
}

import { Pool } from "https://deno.land/x/postgres@v0.19.3/mod.ts";

const pool = new Pool({
  database: Deno.env.get("POSTGRES_DATABASE"),
  hostname: Deno.env.get("POSTGRES_HOST"),
  port: 5432,
  user: Deno.env.get("POSTGRES_USER"),
  password: Deno.env.get("POSTGRES_PASSWORD"),
  connection: {
    attempts: 10,
  },
  options: {
    
  }
}, 10);
export { pool };

import { QueryArrayResult } from "https://deno.land/x/postgres@v0.19.3/mod.ts";
import { pool } from "../database/database.ts";

export class ChatMessageRepository {

    public static async getChatMessagesByChannelIdAndUserId(channelId: bigint, userId: bigint): Promise<QueryArrayResult<unknown[]>> {
        const client = await pool.connect();
        const chatMesages = await client.queryArray(
            `SELECT "Content", "IsBot" FROM public."ChatMessages" WHERE "ChannelId" = ${channelId} AND "UserId" = ${userId} ORDER BY "CreatedAt" ASC;`,
          );
        client.release();
        return chatMesages;
    }
}

ConnectionError: The session was terminated unexpectedly
0|chatbot  |       throw new ConnectionError("The session was terminated unexpectedly");

ConnectionError: The session was terminated unexpectedly
0|chatbot  |       throw new ConnectionError("The session was terminated unexpectedly");

1 Reply

Deno AI Helper•8mo ago

I wasn't able to find the answer to your question in the official Deno documentation. However, based on the code snippet you provided, I noticed a potential issue that you might want to look into. In the Pool configuration, you have a connection property with an attempts field. However, according to the standard usage of the Pool class from the postgres module, there is no connection property used in the configuration object. Instead, you might want to specify the maxRetryCount property directly in the configuration object if you're trying to set the number of retry attempts for the pool. Here's a revised version of the pool configuration without the connection property:

const pool = new Pool({
  database: Deno.env.get("POSTGRES_DATABASE"),
  hostname: Deno.env.get("POSTGRES_HOST"),
  port: 5432,
  user: Deno.env.get("POSTGRES_USER"),
  password: Deno.env.get("POSTGRES_PASSWORD"),
  // Removed the connection property and added maxRetryCount if needed
  maxRetryCount: 10,
}, 10);

const pool = new Pool({
  database: Deno.env.get("POSTGRES_DATABASE"),
  hostname: Deno.env.get("POSTGRES_HOST"),
  port: 5432,
  user: Deno.env.get("POSTGRES_USER"),
  password: Deno.env.get("POSTGRES_PASSWORD"),
  // Removed the connection property and added maxRetryCount if needed
  maxRetryCount: 10,
}, 10);

Please note that this advice is not based on the Deno documentation, and I would advise you to double-check the accuracy of the advice given. If the issue persists, you may want to consider looking into the idle_timeout setting of the pool or the configuration of your PostgreSQL server to ensure that it's not closing idle connections prematurely.

Helpful Links: Connect to Postgres - Deno by Example Deno 1.15 Release Notes Connecting to databases | Deno Docs Deno 1.19 Release Notes Deno 1.34: deno compile supports npm packages How to Build a GraphQL Server with Deno Web Streams at the Edge Deno 1.18 Release Notes