Skip to content

Fix flaky usage_data_spec.rb test failure

Stan Hu requested to merge sh-fix-flaky-usage-metrics-tests into master

What does this MR do and why?

This spec creates projects with two different dates: 31 days ago and 3 days from today. CountImportedProjectsMetric then attempts to count projects in batches by finding the min and max ID range of projects based on their created_at column.

However, if multiple projects have the same created_at value, PostgreSQL may return inconsistent results with the following query:

SELECT id FROM projects ORDER BY created_at DESC LIMIT 1

To fix this, we need to use a secondary order expression:

For the min ID, we want ORDER BY created_at ASC, id ASC.

For the max ID, we want ORDER BY created_at DESC, id DESC.

As mentioned in the PostgreSQL docs (https://www.postgresql.org/docs/current/queries-limit.html):

The query optimizer takes LIMIT into account when generating query plans, so you are very likely to get different plans (yielding different row orders) depending on what you give for LIMIT and OFFSET. Thus, using different LIMIT/OFFSET values to select different subsets of a query result will give inconsistent results unless you enforce a predictable result ordering with ORDER BY. This is not a bug; it is an inherent consequence of the fact that SQL does not promise to deliver the results of a query in any particular order unless ORDER BY is used to constrain the order.

Relates to #391972 (closed)

How to test locally

I reproduced this by emulating the behavior of CI using Docker images: #390313 (comment 1283606446), but in retrospect a clean PostgreSQL 14 database might have been the best way to reproduce the issue.

I've included the instructions in case someone wants to do something similar in the future:

  1. Spin up separate containers:
mkdir -p /tmp/gitlab
docker network create -d bridge test
docker run -d --network test --network-alias redis --name redis redis:6.2-alpine
docker run -d --network test --network-alias redis --name rediscluster registry.gitlab.com/gitlab-org/gitlab-build-images:redis-cluster-6.2.12
docker run -d --network test -e POSTGRES_HOST_AUTH_METHOD=trust --name postgres registry.gitlab.com/gitlab-org/gitlab-build-images:postgres-14-pgvector-0.4.1
docker run --network test --name gitlab -v /tmp/gitlab:/tmp/gitlab -it registry.gitlab.com/gitlab-org/gitlab-build-images/debian-bullseye-ruby-3.0.patched-golang-1.19-rust-1.65-node-18.16-postgresql-14:rubygems-3.4-git-2.36-lfs-2.9-chrome-113-yarn-1.22-graphicsmagick-1.3.36 bash
  1. Inside the container above, clone the repo:
git clone --depth 1 https://gitlab.com/gitlab-org/gitlab.git /tmp/gitlab
  1. Run these scripts:
cd /tmp/gitlab
export GOPATH=/tmp/gitlab/.go
mkdir -p $GOPATH
source scripts/utils.sh
source scripts/prepare_build.sh
scripts/setup-test-env
scripts/gitaly-test-build
mkdir -p tmp/run
  1. Run the spec:
bundle exec rspec spec/lib/gitlab/usage_data_spec.rb:262

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Edited by Stan Hu

Merge request reports