Gorm Can't use preload when number of parameters exceeds 65535

Your Question

Hello everybody. I'm facing the same problem described in this issue and I would be very grateful if anyone could give me some guidance on how can I work around this. Is the usage of preload for queries with a large number of parameters not recommended? Could you please suggest the better approach for cases like this? Thank you!

The document you expected this should be explained

https://gorm.io/docs/preload.html

Expected answer

I would like to know if there is a better approach to this use case, when preload queries have more than 65535 parameters

Comment From: a631807682

I think Preload just provides a convenient query method, but it may not be suitable when querying a large amount of data. This is not only a problem of database parameter limitations, but may also include issues such as whether it causes index failure and whether a large amount of IO is generated. For these situations, I think developers need to deal with it. Developers can reduce main table records through paging to reduce related table records, or they can use the Scopes API to implement things similar to Preload in batches.

Comment From: lincolnjpg

@a631807682 Could you give me an example on how I could use the Scopes API to do this?

Comment From: just-hms

@lincolnjpg have a look at https://github.com/just-hms/gorm-any

The error extended protocol limited to 65535 parameters is caused by to the fact that gorm.Statemtent's AddVar binds an array as a list of parameters, if the provided array is bigger then 65535, then the postgres' parameters limit is reached.

A solution is to use create a custom clause which binds an array as a single variable, this is all the code you need

var (
    whereClause     = clause.Where{}.Name()
    postgresDialect = postgres.Dialector{}.Name()
)

// ANY is a custom implementation of the clause.IN which binds array of Values directly to a single variable
// it has been implemented to address the limitation of "protocol limited to 65535 parameters".
type ANY struct {
    clause.IN
}

// UseAny configures the DB to use the ANY type for IN clauses to resolve parameter limitations.
func UseAny(db *gorm.DB) {
    currentDialect := db.Dialector.Name()
    if currentDialect != postgresDialect {
        log.Printf("ANY clause not supported with %q dialect", currentDialect)
        return
    }

    db.ClauseBuilders[whereClause] = func(c clause.Clause, builder clause.Builder) {
        where := c.Expression.(clause.Where)
        for i, expr := range where.Exprs {
            if in, ok := expr.(clause.IN); ok {
                where.Exprs[i] = ANY{IN: in}
            }
        }
        c.Build(builder)
    }
}

// Build constructs the postgres ANY clause, used to make queries with large value lists work
func (c ANY) Build(builder clause.Builder) {
    // Only replace clause.IN with ANY for value lists, not subqueries
    hasNonValue := slices.ContainsFunc(c.Values, func(v any) bool {
        switch v.(type) {
        case sql.NamedArg, clause.Column, clause.Table, clause.Interface, clause.Expression, []any, *gorm.DB:
            return true
        }
        return false
    })

    // use clause.IN as default
    if hasNonValue || len(c.Values) <= 1 {
        c.IN.Build(builder)
        return
    }

    builder.WriteQuoted(c.Column)
    stmt := builder.(*gorm.Statement)

    // actual binding of the array
    // replacing `IN ($1, $2, $3)` with `= ANY ($1)`
    // which then translates to `= ANY([element, element2, element3, ...])`
    _, _ = builder.WriteString(" = ANY (")
    addBulk(stmt, c.Values)
    _, _ = builder.WriteString(")")
}

// addBulk integrates a list of values into the query, leveraging postgres's array binding support
func addBulk(stmt *gorm.Statement, v any) {
    stmt.Vars = append(stmt.Vars, v)
    stmt.DB.Dialector.BindVarTo(stmt, stmt, v)
}

Comment From: github-actions[bot]

This issue has been automatically marked as stale because it has been open 360 days with no activity. Remove stale label or comment or this will be closed in 180 days

Comment From: EnriqueL8

@just-hms Thanks for sharing the above snippet, super useful! How can it handle SQL String where clauses? The in, ok := expr.(clause.IN) line assumes that the WHERE clause has been parsed into an IN structure but a string is just stored as an expression

Comment From: icedream

This is absolutely still an issue, not a stale one. Thanks for providing a workaround though @just-hms.