Idle connections left open

[jrp-enf]

Using pgx v5.5.0.

I have a utility which uses pgxpool.

main() {
cfg := pgxpool.ParseConfig(...)
db, err := pgxpool.WithNewConfig(ctx, cfg) //(this also happens with just pgx.Connect())
defer db.Close()
rows = db.Query(...)
output, err := pgx.CollectRows(...)
rows.Close()
...print the output...
return
}

Every time I run it, I get a new active connection entry (with the state of idle) according to psql "select * from pg_stat_activity;" on the database:

datid | datname | pid | leader_pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start | query_start | state_change | wait_event_type | wait_event | state | backend_xid | backend_xmin | query_id | query | backend_type
16511 | cjlicenses | 323400 | | 16489 | cj_license_manager | | 10.0.4.5 | | 36688 | 2024-02-26 15:45:03.548654+00 | | 2024-02-26 15:45:04.151007+00 | 2024-02-26 15:45:04.151068+00 | Client | ClientRead | idle | | | | SELECT id, vendor, feature, usage_state, last_state_change, reserved_by_node, used_by_process FROM license_state
...multiple of those lines, adds a new one each time I run the tool...

I've run it to the max_active_connections limit on the database, at which time it fails to connect, after which I had to destroy idle connections by hand to let it continue to work. (For reasons that escape me, gcp postgres at v14 doesn't appear to have the supported idle timeout setting, so there is no way of getting these connections dropped other than by hand.)

I've tried adding a cfg.AfterRelease to return false, which runs but didn't change the behavior.

[jackc]

We'd need a complete example that demonstrates the problem to figure out what is going on.

[jrp-enf]

I found a workaround - calling, after defer db.Close(), calling defer db.Reset() makes it not leak connections according to pg_stat_activity.

So:
CREATE TABLE IF NOT EXISTS license_state (
id TEXT NOT NULL,
vendor TEXT NOT NULL,
feature TEXT NOT NULL,
usage_state TEXT NOT NULL,
last_state_change TIMESTAMPTZ NOT NULL,
reserved_by_node TEXT,
used_by_process TEXT,
metadata JSONB,
PRIMARY KEY (id)
);

import (
	"context"
	"flag"
	"fmt"
	"log/slog"

	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgxpool"
///...
)

type License struct {
	ID string
	Vendor string
	Feature string
	Status string
	LastUpdateTime time.Time
	UserNode *string
	UserProcess *string
}
var (
	connection  = flag.String("connection", "postgresql://cj_license_manager:<managerpassword>@localhost:5433/cjlicenses", "specify a connection") // note this is a ssh tunneled connection, if that matters.
        runList     = flag.Bool("list", false, "List the current licenses")
       ///... other flags
)

func main() {
	flag.Parse()
	ctx := context.Background()
	db, err := pgxpool.New(ctx, *connection)
	if err != nil {
		slog.Error("Error opening database: ", "error", err)
		return
	}
	defer db.Close()
	defer db.Reset() // !! If we don't do this, we seem to leak  connections according to pg_stat_activity. 

	rows, err := db.Query(ctx, sqldb.QueryAllLicenses)
	if err != nil {
		slog.Error("Error querying licenses:", "error", err)
		return
	licenses, err := pgx.CollectRows(rows, func(row pgx.CollectableRow) (*types.License, error) {
		l := &types.License{}
		err := row.Scan(&l.ID, &l.Vendor, &l.Feature, &l.Status, &l.LastUpdateTime, &l.UserNode, &l.UserProcess)
		return l, err
	})
	if err != nil {
		slog.Error("Error collectrows for licenses:", "error", err)
		return
	}
	rows.Close()
	if err := rows.Err(); err != nil {
		slog.Error("Error rows.Err for licenses:", "error", err)
		return
	}
	if *runList {
		for _, l := range licenses {
			if *runList {
				fmt.Printf("License: %v, %v, %v\n", l.ID, l.Vendor, l.Feature)
			}
               }
      }
}

[jackc]

Are you saying that there are still connections open after the program has exited without the defer db.Reset()? That shouldn't be possible... 🤔

[jrp-enf]

That's what I found, yes. I thought it was suspicious as well.

[jackc]

The example code you gave doesn't run as is. I modified it a bit to get it to run, but I am unable to reproduce the issue.

There must be something else going on though. Calling Reset() shouldn't make a difference as the deferred pool Close() should shutdown the connections in an orderly fashion.

And beyond that, the only way I can imagine a connection existing after the program has ended is if it was connected to a PG server on another machine and the program was killed in such a way as to prevent the TCP connection being cleaned up (such as literally pulling the plug on the machine).

To be honest, I highly doubt the issue is with pgx.