Why doesn't Postgres complete IDLE transactions?

Question

PostgreSQL

I work with Postgres through the node-postgres low-level library using a connection pool .

I noticed that for each request to the database, a new IDLE transaction is opened, and even for the same requests:


This quickly leads to exceeding the number of maximum connections and the postgres stops responding.

This is how I create a connection pool instance:

const store = new StoreDB({
        user: 'dship',
        host: 'localhost',
        database: 'dship',
        password: '111',
        port: 5432,
        // Max of idle-transactions
        max: 20,
        idleTimeoutMillis: 1000,
        connectionTimeoutMillis: 500,
    })

Here is an example call:

export class StoreDB {
    constructor(config) {
        this.pool = new Pool(config);
        this.pool.on('error', (err, client) => {
            console.error('StoreDB Postgres error', err);
        })
    }

    async _query(sql) {
        try {
            const client = await this.pool.connect();
            return client.query(sql);
        } catch (e) {
            throw new Error(`StoreDB -> _query() ->`, e.message);
        }
    }
}

Could this be related to the fact that I am calling functions/procedures? Function example:

CREATE OR REPLACE FUNCTION helpdesk.tabof_active_issues(p_json json)
 RETURNS TABLE(t_id integer, t_creation_date timestamp with time zone, t_theme_title character varying, t_summary character varying, t_status_name character varying, t_status_color_code_hex character varying, t_status_update timestamp with time zone)
 LANGUAGE plpgsql
AS $function$
BEGIN
  RETURN QUERY SELECT
    i.id,
    i.creation_date,
    t.title,
    i.summary,
    s.title,
    s.color_code_hex,
    i.status_update_date
  FROM helpdesk.issue i
    JOIN helpdesk.issue_theme t
      ON	i.theme_id = t.id
    JOIN helpdesk.issue_status s
      ON i.status_id = s.id
  WHERE i.state = 1
    AND s.is_archived = 0
    AND i.user_id = (p_json ->> 'userKey')::integer
  ORDER BY i.creation_date;

  RETURN;
END;
$function$
;

Answer 1

idle != idle in transaction. These are fundamentally different statuses.
As you yourself wrote, you have a connection pool on your application. Therefore, this is a question for the application, what are you doing wrong with the library, if for each request it tries to open a new connection to the database, instead of using a free connection from its pool.
In particular, where is the release() call?