Offset pagination: Missing `LIMIT` clause
https://sentry.gitlab.net/gitlab/staginggitlabcom/issues/1099495/
PG::QueryCanceled: ERROR: canceling statement due to statement timeout
...
ActiveRecord::QueryCanceled: PG::QueryCanceled: ERROR: canceling statement due to statement timeout
: SELECT "projects".* FROM "projects" WHERE "projects"."visibility_level" = 20 AND "projects"."pending_delete" = FALSE ORDER BY "projects"."created_at" DESC, "projects"."id" DESC
This happens for requests to the Projects API, e.g. https://staging.gitlab.com/api/v4/projects
.
The endpoint limits the result set but the limit isn't applied for all queries involved. When running this locally, we should see:
Project Load (0.6ms) SELECT "projects".* FROM "projects" WHERE "projects"."visibility_level" = $1 AND "projects"."pending_delete" = $2 ORDER BY "projects"."created_at" DESC, "projects"."id" DESC LIMIT $3 OFFSET $4 [["visibility_level", 20], ["pending_delete", false], ["LIMIT", 20], ["OFFSET", 0]]
↳ app/services/projects/batch_count_service.rb:13
But instead, this became:
Project Load (1.4ms) SELECT "projects".* FROM "projects" WHERE "projects"."visibility_level" = $1 AND "projects"."pending_delete" = $2 ORDER BY "projects"."created_at" DESC, "projects"."id" DESC [["visibility_level", 20], ["pending_delete", false]]
↳ app/services/projects/batch_count_service.rb:13
Note the LIMIT is still applied correctly to the query actually loading projects:
Project Load (0.6ms) SELECT "projects".* FROM "projects" WHERE "projects"."visibility_level" = $1 AND "projects"."pending_delete" = $2 ORDER BY "projects"."created_at" DESC, "projects"."id" DESC LIMIT $3 OFFSET $4 [["visibility_level", 20], ["pending_delete", false], ["LIMIT", 20], ["OFFSET", 0]]
↳ lib/api/projects.rb:98
Edited by Andreas Brandl