How to change the config of pgxpool which is in use

[sasakiyori]

Assume that user name is changed when pgxpool is working, and the program can catch this. Some connections are running long sql inside the pgxpool, if it want to create new connection, error will occur because the user name in Config is the old one.

If I don't want to create another pool(I have no good ideas how to maintain the old pool, especially in the acquire session scene), the direct idea is to change config of pgxpool instantly. But pgxpool.Config() returns the cloned config for safety.

The hook BeforeConnect can make a tricky solution like:

cfg, err := pgxpool.ParseConfig(dsn)
if err != nil {
  panic(err)
}
cfg.BeforeConnect = func(ctx context.Context, cfg *pgx.ConnConfig) error {
  cfg.User = getNewUserFunc()
  return nil
}
pool, err := pgxpool.NewWithConfig(ctx, cfg)
if err != nil {
  panic(err)
}

As above code, the change of config is defined in the initialization phase, it is hard for maintaining.

Are there any better solutions?

[jackc]

I think I would create a wrapper around the *pgxpool.Pool that handles creating and closing the *pgxpool.Pool when the configuration changes. Then all the application code would use this wrapper rather than the *pgxpool.Pool directly.

[sasakiyori]

Thanks for suggestions.

My implementation is like below, looks good. Though it needs some tests and optimization.

type MyPool struct {
	sync.RWMutex
	ctx  context.Context
	DSN  string
	curr *pgxpool.Pool
	old  []*pgxpool.Pool
}

func (p *MyPool) ChangeCfg(dsn string) error {
	p.Lock()
	defer p.Unlock()

	p.DSN = dsn
	cfg, err := pgxpool.ParseConfig(p.DSN)
	if err != nil {
		return err
	}
	p.old = append(p.old, p.curr)
	p.curr, err = pgxpool.NewWithConfig(p.ctx, cfg)
	if err != nil {
		return err
	}
	return nil
}

func (p *MyPool) PeriodicallyPurge() {
	p.Lock()
	defer p.Unlock()

	p.old = slices.DeleteFunc(p.old, func(pool *pgxpool.Pool) bool {
		pool.Close()
		return pool.Stat().AcquiredConns() == 0
	})
}