Ci::DeleteObjectsWorker cannot execute UPDATE in a read-only transaction on Patroni replica
Summary
GitLab support is investigating a customer's Patroni cluster, and in the logs provided, one of the secondaries logged an ERROR: cannot execute UPDATE in a read-only transaction. GitLab team members can read more in the ticket though the focus of the ticket is not in this issue.
Customer is running 14.5.2
Steps to reproduce
Unknown. Customer's running an Omnibus deployment, including a Patroni cluster.
Example Project
What is the current bug behavior?
GitLab attempted to write to one of the read-only replicas
What is the expected correct behavior?
I assume Database Load Balancing must be involved here? This query shouldn't have been routed to the replicas.
Relevant logs and/or screenshots
On a patroni node, both the patroni log entries and the postgresql log entries are written to the same log:
2022-01-17_13:00:09.87344 2022-01-17 13:00:09,872 INFO: no action. I am a secondary (***) and following a leader (***)
2022-01-17_13:00:13.44459 ERROR: cannot execute UPDATE in a read-only transaction
2022-01-17_13:00:13.44462 STATEMENT: /*application:sidekiq,correlation_id:xx,jid:xx,endpoint_id:Ci::DeleteObjectsWorker,db_config_name:main*/
UPDATE "ci_deleted_objects" SET "pick_up_at" = '2022-01-17 13:10:13.437369' WHERE "ci_deleted_objects"."id"
IN (SELECT "ci_deleted_objects"."id" FROM "ci_deleted_objects"
WHERE (pick_up_at < '2022-01-17 13:00:13.436626')
ORDER BY "ci_deleted_objects"."pick_up_at" ASC LIMIT 100 FOR UPDATE SKIP LOCKED) RETURNING *
2022-01-17_13:00:19.97431 2022-01-17 13:00:19,973 INFO: no action. I am a secondary (***) and following a leader (***)Output of checks
Results of GitLab environment info
Expand for output related to GitLab environment info
(For installations with omnibus-gitlab package run and paste the output of: `sudo gitlab-rake gitlab:env:info`) (For installations from source run and paste the output of: `sudo -u git -H bundle exec rake gitlab:env:info RAILS_ENV=production`)
Results of GitLab application Check
Expand for output related to the GitLab application check
(For installations with omnibus-gitlab package run and paste the output of:
sudo gitlab-rake gitlab:check SANITIZE=true)(For installations from source run and paste the output of:
sudo -u git -H bundle exec rake gitlab:check RAILS_ENV=production SANITIZE=true)(we will only investigate if the tests are passing)