ERROR: prepared statement_* doesn't exists

I am 4 days can't resolve my issue and sadly, but chatGPT also weak on this.

In short: I have Go, Gin-gonic v1.9.0 API which using Gorm ORM v1.24.5 and Postgresql server v14 hosted on the Digitalocean. I also enabled the PgBouncer feature with pool_mode = transaction for a specific database.

Question: I can't fully understand how should I correctly use Gorm ORM with transaction pool mode to handle each API request and after sql query executed be able to return connection to the pgbouncer pool. I checked that ORM using jackc/pgx under hood but that library have pgxpool and ability to Acquire & Release connection, and I didn't find any information how Gorm cover it.

Why title about prepared statements? Before I set the options: PrepareStmt: false and PreferSimpleProtocol: true, mostly received errors: prepared statement_* already exists. Based on docs from PgBouncer: http://www.pgbouncer.org/faq.html#how-to-use-prepared-statements-with-transaction-pooling, it's not possible to use prepared statements with transaction mode so I disable it. When I deployed my API (only one endpoint) to tests with a team, we watch how at least every fourth sql query failed with ERROR: prepared statement_6230 doesn't exists. When I executed:

SELECT * FROM pg_prepared_statements; 

I see a list of prepared statements which a living ~ 30 min, guess it's until connection alive. Using DEALLOCATE ALL only drops current statements.

My endpoint and db connection where hidden not relevant code.

func main() { ... client := setupPostgresql() ... r.GET("/deploy/:id", func(c *gin.Context) { id := c.Param("id") var deploy Deploy // small model timeoutCtx, cancel := context.WithTimeout(c.Request.Context(), 5*time.Second) defer cancel() if err := client.WithContext(timeoutCtx).Where("id = ?", id).First(&deploy).Error; err != nil { if errors.Is(err, gorm.ErrRecordNotFound) { c.AbortWithStatusJSON(http.StatusNotFound, gin.H{"error": "Deploy with id: " + id + " not found"}) return } else { log.Panic(err) // it will be covered by gin recover } } c.AbortWithStatusJSON(http.StatusOK, deploy) }) ... } func setupPostgresql() *gorm.DB { dsn := "host=" + AppConfig.DBHost + " user=" + AppConfig.DBUser + " password=" + AppConfig.DBPass + " dbname=" + AppConfig.DBName + " port=" + AppConfig.Port + " sslmode=" + AppConfig.SSL client, err := gorm.Open(postgres.New(postgres.Config{ DSN: dsn, PreferSimpleProtocol: true, }), &gorm.Config{ SkipDefaultTransaction: false, DisableAutomaticPing: true, PrepareStmt: false, NowFunc: func() time.Time { return time.Now().UTC() }, Logger: logger.Default.LogMode(logger.Silent), }) ... underlyingDB, _ := client.DB() underlyingDB.SetMaxIdleConns(11) underlyingDB.SetMaxOpenConns(11) underlyingDB.SetConnMaxIdleTime(15 * time.Minute) underlyingDB.SetConnMaxLifetime(30 * time.Minute) return client } 

Infrastructure example I expected to correctly configure Gorm ORM to use PgBouncer in transaction mode.