Skip to content

Eager loading queries can exceed Postgres max number of parameters #850

Open
@mschristensen

Description

@mschristensen

If you're having a generation problem please answer these questions before submitting your issue. Thanks!

What version of SQLBoiler are you using (sqlboiler --version)?

SQLBoiler v4.2.0

What is your database and version (eg. Postgresql 10)

Postgresql 10

If this happened at generation time what was the full SQLBoiler command you used to generate your models? (if not applicable leave blank)

If this happened at runtime what code produced the issue? (if not applicable leave blank)

func main() {
	usages, err := dbmodels.IsrcUsages(
		dbmodels.IsrcUsageWhere.UsageReportID.EQ(30),
		qm.Load(
			dbmodels.IsrcUsageRels.Isrc,
		),
	).All(ctx, dbClient)
	if err != nil {
		panic(errors.Wrap(err, "get isrc usages failed"))
	}
	logger.Info(len(usages))
}

What is the output of the command above with the -d flag added to it? (Provided you are comfortable sharing this, it contains a blueprint of your schema)

Please provide a relevant database schema so we can replicate your issue (Provided you are comfortable sharing this)

CREATE TABLE IF NOT EXISTS isrc (
    id serial PRIMARY KEY,
    isrc text UNIQUE NOT NULL
);

CREATE TABLE IF NOT EXISTS isrc_usage (
    id serial PRIMARY KEY,
    usage_report_id int NOT NULL REFERENCES usage_report (id) ON DELETE CASCADE,
    isrc_id int NOT NULL REFERENCES isrc (id) ON DELETE CASCADE,
    UNIQUE (usage_report_id, isrc_id)
);

Further information. What did you do, what did you expect?

In this example, if there are 10 ISRC usages for the given usage_report_id, then eager loading the ISRCs generates queries like:

SELECT * FROM "isrc_usage" WHERE ("isrc_usage"."usage_report_id" = $1);

SELECT * FROM "isrc" WHERE ("isrc"."id" IN ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10));

The latter query will fail if the number of ISRCs exceeds a 16 bit integer (which pg uses to count query parameters), resulting in an error like:

failed to eager load Isrc: extended protocol limited to 65535 parameters

It would be fantastic if SQL boiler could intelligently adapt the generated query if the # parameters is too large.

Metadata

Metadata

Assignees

No one assigned

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions