CODEWITHSUNDEEP
postgresqlgogo-gormgo-ginpgbouncer
hangofki
hangofki

Reputation: 11

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.

Upvotes: 0

Views: 1181

Answers (1)

Eduard Telenkov
Eduard Telenkov

Reputation: 1

db, err := gorm.Open(postgres.New(postgres.Config{
        DSN:                  dsn,
        **PreferSimpleProtocol: true,**
    }), &gorm.Config{
        NamingStrategy: schema.NamingStrategy{
            TablePrefix:   "wa.",
            SingularTable: false,
        },
    })

Upvotes: -1

Related Questions