Skip to content
This repository was archived by the owner on Sep 30, 2024. It is now read-only.

dynamically select only for pg_stat_activity with datname from the ap…#60771

Merged
DaedalusG merged 2 commits into
mainfrom
wg/bug-autoupgrade-checkForDisconnects
Feb 28, 2024
Merged

dynamically select only for pg_stat_activity with datname from the ap…#60771
DaedalusG merged 2 commits into
mainfrom
wg/bug-autoupgrade-checkForDisconnects

Conversation

@DaedalusG

@DaedalusG DaedalusG commented Feb 28, 2024 •
edited
Loading

Copy link
Copy Markdown
Contributor

dynamically select only for pg_stat_activity with datname from the ap…plication making the request i.e. sourcegraph-autoupgrader

Test plan

Created test db with multiple dbs in one postgres instance to reproduce bug. Confirmed migrator runs autoupgrade correctly with upgradeTest auto

Details 
➜  sourcegraph git:(wg/upgrade-test-tool-post-v5.3.0) ✗ VERSION=5.3.2 bazel run //testing/tools/upgradetest:sh_upgradetest_run --config darwin-docker --stamp --workspace_status_command=./dev/bazel_stamp_vars.sh -- auto
INFO: Analyzed target //testing/tools/upgradetest:sh_upgradetest_run (0 packages loaded, 0 targets configured).
INFO: Found 1 target...
Target //testing/tools/upgradetest:sh_upgradetest_run up-to-date:
  bazel-bin/testing/tools/upgradetest/sh_upgradetest_run
Aspect @@rules_rust//rust/private:clippy.bzl%rust_clippy_aspect of //testing/tools/upgradetest:sh_upgradetest_run up-to-date (nothing to build)
INFO: Elapsed time: 0.497s, Critical Path: 0.02s
INFO: 1 process: 1 internal.
INFO: Build completed successfully, 1 total action
INFO: Running command line: bazel-bin/testing/tools/upgradetest/sh_upgradetest_run testing/tools/upgradetest/upgradetest-darwin-arm64 cmd/migrator/image_tarball.sh cmd/frontend/image_tarball.sh internal/database/_codeinsights_squashed.sql internal/database/_codeintel_squashed.sql internal/database/_frontend_squashed.sql internal/database/_schema.codeinsights.json internal/database/_schema.codeinsights.md internal/database/_schema.codeintel.json internal/database/_schema.codeintel.md internal/database/_schema.json internal/database/_schema.md auto
Loaded image: migrator:candidate
Loaded image: frontend:candidate
Latest stable release version:  5.3.1
Latest minor version:  5.3.0
Target version:  5.3.2
Migrator image used to upgrade:  migrator:candidate stamped as 5.3.2
Auto Versions: [3.39.0 3.39.1 3.40.0 3.40.1 3.40.2 3.41.0 3.41.1 3.42.0 3.42.1 3.42.2 3.43.0 3.43.1 3.43.2 4.0.0 4.0.1 4.1.0 4.1.1 4.1.2 4.1.3 4.2.0 4.2.1 4.3.0 4.3.1 4.4.0 4.4.1 4.4.2 4.5.0 4.5.1 5.0.0 5.0.1 5.0.2 5.0.3 5.0.4 5.0.5 5.0.6 5.1.0 5.1.1 5.1.2 5.1.3 5.1.4 5.1.5 5.1.6 5.1.7 5.1.8 5.1.9 5.2.0 5.2.1 5.2.2 5.2.3 5.2.4 5.2.5 5.2.6 5.2.7 5.3.0 5.3.1]
auto:  3.42.2
auto:  3.40.1
auto:  3.41.1
auto:  3.39.1
auto:  3.42.0
auto:  3.39.0
auto:  3.42.1
auto:  3.40.2
auto:  3.41.0
auto:  3.40.0
auto:  3.43.0
auto:  3.43.1
auto:  3.43.2
auto:  4.0.0
auto:  4.0.1
auto:  4.5.0
auto:  4.5.1
auto:  5.0.0
auto:  5.0.1
auto:  5.0.2
auto:  5.0.3
auto:  5.0.4
auto:  5.0.5
auto:  5.0.6
auto:  5.1.0
auto:  5.1.1
auto:  5.1.2
auto:  5.1.3
auto:  5.1.4
auto:  5.1.5
auto:  5.1.6
auto:  5.1.7
auto:  5.1.8
auto:  5.1.9
auto:  5.2.0
auto:  5.2.1
auto:  5.2.2
auto:  5.2.3
auto:  5.2.4
auto:  5.2.5
auto:  5.2.6
auto:  5.2.7
auto:  5.3.0
auto:  5.3.1
--- 🕵️  Auto Upgrade Tests:
✅ 3.39.0 Passed -- 1m37.33225625s
✅ 3.39.1 Passed -- 1m36.811294833s
✅ 3.40.0 Passed -- 1m34.82412575s
✅ 3.40.1 Passed -- 1m37.6525085s
✅ 3.40.2 Passed -- 1m32.453865584s
✅ 3.41.0 Passed -- 1m32.681966417s
✅ 3.41.1 Passed -- 1m30.872435917s
✅ 3.42.0 Passed -- 1m34.710823292s
✅ 3.42.1 Passed -- 1m32.958949459s
✅ 3.42.2 Passed -- 1m30.051301459s
✅ 3.43.0 Passed -- 1m25.609245084s
✅ 3.43.1 Passed -- 1m23.898958625s
✅ 3.43.2 Passed -- 1m27.412701083s
✅ 4.0.0 Passed -- 1m26.18332275s
✅ 4.0.1 Passed -- 1m27.560715167s
✅ 4.5.0 Passed -- 1m26.157183833s
✅ 4.5.1 Passed -- 1m26.070184333s
✅ 5.0.0 Passed -- 1m20.245605042s
✅ 5.0.1 Passed -- 1m20.633817334s
✅ 5.0.2 Passed -- 1m22.539950166s
✅ 5.0.3 Passed -- 1m5.783293875s
✅ 5.0.4 Passed -- 1m5.938274416s
✅ 5.0.5 Passed -- 1m10.93517375s
✅ 5.0.6 Passed -- 1m10.139386166s
✅ 5.1.0 Passed -- 1m28.900240459s
✅ 5.1.1 Passed -- 1m29.04735325s
✅ 5.1.2 Passed -- 1m27.66593275s
✅ 5.1.3 Passed -- 1m28.228765541s
✅ 5.1.4 Passed -- 1m10.506759375s
✅ 5.1.5 Passed -- 1m8.733253167s
✅ 5.1.6 Passed -- 1m11.447142917s
✅ 5.1.7 Passed -- 1m13.843541916s
✅ 5.1.8 Passed -- 1m12.381715167s
✅ 5.1.9 Passed -- 1m11.258214s
✅ 5.2.0 Passed -- 1m11.607926333s
✅ 5.2.1 Passed -- 1m9.665071292s
✅ 5.2.2 Passed -- 1m5.539902792s
✅ 5.2.3 Passed -- 1m6.818803833s
✅ 5.2.4 Passed -- 1m3.87366575s
✅ 5.2.5 Passed -- 1m5.070555125s
✅ 5.2.6 Passed -- 54.289142208s
✅ 5.2.7 Passed -- 52.922970292s
✅ 5.3.0 Passed -- 47.086401542s
✅ 5.3.1 Passed -- 47.9220515s

latveriadb=> \l
                                  List of databases
    Name    |     Owner     | Encoding |  Collate   |   Ctype    | Access privileges
------------+---------------+----------+------------+------------+-------------------
 latveriadb | viktor_vaughn | UTF8     | en_US.utf8 | en_US.utf8 |
 postgres   | sg            | UTF8     | en_US.utf8 | en_US.utf8 |
 sg         | sg            | UTF8     | en_US.utf8 | en_US.utf8 |
 template0  | sg            | UTF8     | en_US.utf8 | en_US.utf8 | =c/sg            +
            |               |          |            |            | sg=CTc/sg
 template1  | sg            | UTF8     | en_US.utf8 | en_US.utf8 | =c/sg            +
            |               |          |            |            | sg=CTc/sg
(5 rows)
latveriadb=> select * from pg_stat_activity;
 datid |  datname   | pid  | usesysid |    usename    |     application_name      | client_addr  | client_hostname | client_port |         backend_start         |          xact_start           |          query_start          |         state_change          | wait_event_type | wait_event | state  | backend_xid | backend_xmin |                              query                               |  backend_type
-------+------------+------+----------+---------------+---------------------------+--------------+-----------------+-------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-----------------+------------+--------+-------------+--------------+------------------------------------------------------------------+----------------
       |            |   28 |          |               |                           |              |                 |             |                               |                               |                               |                               |                 |            |        |             |              | <insufficient privilege>                                         |
       |            |   32 |       10 | sg            |                           |              |                 |             |                               |                               |                               |                               |                 |            |        |             |              | <insufficient privilege>                                         |
 21848 | latveriadb | 6195 |    21847 | viktor_vaughn |                           | 192.168.65.1 |                 |       34675 | 2024-02-28 06:12:15.043684+00 |                               | 2024-02-28 06:12:15.076805+00 | 2024-02-28 06:12:15.097273+00 | Client          | ClientRead | idle   |             |              | INSERT INTO DoombotLocations (location) VALUES ('Latveria City') | client backend
 16384 | sg         | 5193 |       10 | sg            | worker                    |              |                 |             |                               |                               |                               |                               |                 |            |        |             |              | <insufficient privilege>                                         |
 21848 | latveriadb | 6168 |    21847 | viktor_vaughn |                           | 192.168.65.1 |                 |       34673 | 2024-02-28 06:12:04.923682+00 |                               | 2024-02-28 06:12:04.949039+00 | 2024-02-28 06:12:04.966495+00 | Client          | ClientRead | idle   |             |              | INSERT INTO DoombotLocations (location) VALUES ('Latveria City') | client backend
 16384 | sg         | 5880 |       10 | sg            | worker                    |              |                 |             |                               |                               |                               |                               |                 |            |        |             |              | <insufficient privilege>                                         |
 16384 | sg         | 4023 |       10 | sg            | repo-updater              |              |                 |             |                               |                               |                               |                               |                 |            |        |             |              | <insufficient privilege>                                         |
 16384 | sg         | 6072 |       10 | sg            | frontend                  |              |                 |             |                               |                               |                               |                               |                 |            |        |             |              | <insufficient privilege>                                         |
 16384 | sg         | 4627 |       10 | sg            | worker                    |              |                 |             |                               |                               |                               |                               |                 |            |        |             |              | <insufficient privilege>                                         |
 16384 | sg         | 5953 |       10 | sg            | frontend                  |              |                 |             |                               |                               |                               |                               |                 |            |        |             |              | <insufficient privilege>                                         |
 16384 | sg         |  895 |       10 | sg            | worker                    |              |                 |             |                               |                               |                               |                               |                 |            |        |             |              | <insufficient privilege>                                         |
 16384 | sg         | 5383 |       10 | sg            | worker                    |              |                 |             |                               |                               |                               |                               |                 |            |        |             |              | <insufficient privilege>                                         |
 16384 | sg         | 6218 |       10 | sg            | worker                    |              |                 |             |                               |                               |                               |                               |                 |            |        |             |              | <insufficient privilege>                                         |
 16384 | sg         | 5740 |       10 | sg            | worker                    |              |                 |             |                               |                               |                               |                               |                 |            |        |             |              | <insufficient privilege>                                         |
 16384 | sg         | 6003 |       10 | sg            | precise-code-intel-worker |              |                 |             |                               |                               |                               |                               |                 |            |        |             |              | <insufficient privilege>                                         |
 16384 | sg         | 5194 |       10 | sg            | worker                    |              |                 |             |                               |                               |                               |                               |                 |            |        |             |              | <insufficient privilege>                                         |
 21848 | latveriadb | 6076 |    21847 | viktor_vaughn |                           | 192.168.65.1 |                 |       34662 | 2024-02-28 05:35:49.614379+00 |                               | 2024-02-28 05:35:49.649851+00 | 2024-02-28 05:35:49.666872+00 | Client          | ClientRead | idle   |             |              | INSERT INTO DoombotLocations (location) VALUES ('Latveria City') | client backend
 16384 | sg         | 5667 |       10 | sg            | frontend                  |              |                 |             |                               |                               |                               |                               |                 |            |        |             |              | <insufficient privilege>                                         |
 16384 | sg         | 3030 |       10 | sg            | repo-updater              |              |                 |             |                               |                               |                               |                               |                 |            |        |             |              | <insufficient privilege>                                         |
 16384 | sg         | 4370 |       10 | sg            | worker                    |              |                 |             |                               |                               |                               |                               |                 |            |        |             |              | <insufficient privilege>                                         |
 21848 | latveriadb | 6099 |    21847 | viktor_vaughn |                           | 192.168.65.1 |                 |       34663 | 2024-02-28 06:11:39.566507+00 |                               | 2024-02-28 06:11:39.591175+00 | 2024-02-28 06:11:39.608164+00 | Client          | ClientRead | idle   |             |              | INSERT INTO DoombotLocations (location) VALUES ('Latveria City') | client backend
 21848 | latveriadb | 6103 |    21847 | viktor_vaughn |                           | 192.168.65.1 |                 |       34664 | 2024-02-28 06:11:44.615924+00 |                               | 2024-02-28 06:11:44.661813+00 | 2024-02-28 06:11:44.684598+00 | Client          | ClientRead | idle   |             |              | INSERT INTO DoombotLocations (location) VALUES ('Latveria City') | client backend
 16384 | sg         | 6114 |       10 | sg            | repo-updater              |              |                 |             |                               |                               |                               |                               |                 |            |        |             |              | <insufficient privilege>                                         |
 21848 | latveriadb | 6135 |    21847 | viktor_vaughn | psql                      |              |                 |          -1 | 2024-02-28 06:11:47.450731+00 | 2024-02-28 06:13:04.724536+00 | 2024-02-28 06:13:04.724536+00 | 2024-02-28 06:13:04.724543+00 |                 |            | active |             |         2303 | select * from pg_stat_activity;                                  | client backend
 21848 | latveriadb | 6137 |    21847 | viktor_vaughn |                           | 192.168.65.1 |                 |       34670 | 2024-02-28 06:11:49.693084+00 |                               | 2024-02-28 06:11:49.740474+00 | 2024-02-28 06:11:49.764192+00 | Client          | ClientRead | idle   |             |              | INSERT INTO DoombotLocations (location) VALUES ('Latveria City') | client backend
 21848 | latveriadb | 6139 |    21847 | viktor_vaughn |                           | 192.168.65.1 |                 |       34671 | 2024-02-28 06:11:54.771756+00 |                               | 2024-02-28 06:11:54.820938+00 | 2024-02-28 06:11:54.843276+00 | Client          | ClientRead | idle   |             |              | INSERT INTO DoombotLocations (location) VALUES ('Latveria City') | client backend
 21848 | latveriadb | 6164 |    21847 | viktor_vaughn |                           | 192.168.65.1 |                 |       34672 | 2024-02-28 06:11:59.84962+00  |                               | 2024-02-28 06:11:59.895297+00 | 2024-02-28 06:11:59.919917+00 | Client          | ClientRead | idle   |             |              | INSERT INTO DoombotLocations (location) VALUES ('Latveria City') | client backend
 21848 | latveriadb | 6191 |    21847 | viktor_vaughn |                           | 192.168.65.1 |                 |       34674 | 2024-02-28 06:12:09.963254+00 |                               | 2024-02-28 06:12:10.013262+00 | 2024-02-28 06:12:10.038566+00 | Client          | ClientRead | idle   |             |              | INSERT INTO DoombotLocations (location) VALUES ('Latveria City') | client backend
 21848 | latveriadb | 6220 |    21847 | viktor_vaughn |                           | 192.168.65.1 |                 |       34676 | 2024-02-28 06:12:20.100239+00 |                               | 2024-02-28 06:12:20.120581+00 | 2024-02-28 06:12:20.137198+00 | Client          | ClientRead | idle   |             |              | INSERT INTO DoombotLocations (location) VALUES ('Latveria City') | client backend
 21848 | latveriadb | 6222 |    21847 | viktor_vaughn |                           | 192.168.65.1 |                 |       34677 | 2024-02-28 06:12:25.145456+00 |                               | 2024-02-28 06:12:25.19588+00  | 2024-02-28 06:12:25.219512+00 | Client          | ClientRead | idle   |             |              | INSERT INTO DoombotLocations (location) VALUES ('Latveria City') | client backend
 16384 | sg         | 6247 |       10 | sg            | worker                    |              |                 |             |                               |                               |                               |                               |                 |            |        |             |              | <insufficient privilege>                                         |
 16384 | sg         | 6249 |       10 | sg            | worker                    |              |                 |             |                               |                               |                               |                               |                 |            |        |             |              | <insufficient privilege>                                         |
 21848 | latveriadb | 6251 |    21847 | viktor_vaughn |                           | 192.168.65.1 |                 |       34678 | 2024-02-28 06:12:30.223477+00 |                               | 2024-02-28 06:12:30.25505+00  | 2024-02-28 06:12:30.277769+00 | Client          | ClientRead | idle   |             |              | INSERT INTO DoombotLocations (location) VALUES ('Latveria City') | client backend
 21848 | latveriadb | 6255 |    21847 | viktor_vaughn |                           | 192.168.65.1 |                 |       34679 | 2024-02-28 06:12:35.281869+00 |                               | 2024-02-28 06:12:35.315403+00 | 2024-02-28 06:12:35.335008+00 | Client          | ClientRead | idle   |             |              | INSERT INTO DoombotLocations (location) VALUES ('Latveria City') | client backend
 21848 | latveriadb | 6278 |    21847 | viktor_vaughn |                           | 192.168.65.1 |                 |       34680 | 2024-02-28 06:12:40.335993+00 |                               | 2024-02-28 06:12:40.355682+00 | 2024-02-28 06:12:40.371879+00 | Client          | ClientRead | idle   |             |              | INSERT INTO DoombotLocations (location) VALUES ('Latveria City') | client backend
 21848 | latveriadb | 6282 |    21847 | viktor_vaughn |                           | 192.168.65.1 |                 |       34681 | 2024-02-28 06:12:45.375044+00 |                               | 2024-02-28 06:12:45.398016+00 | 2024-02-28 06:12:45.4206+00   | Client          | ClientRead | idle   |             |              | INSERT INTO DoombotLocations (location) VALUES ('Latveria City') | client backend
 21848 | latveriadb | 6305 |    21847 | viktor_vaughn |                           | 192.168.65.1 |                 |       34682 | 2024-02-28 06:12:50.426943+00 |                               | 2024-02-28 06:12:50.468478+00 | 2024-02-28 06:12:50.492621+00 | Client          | ClientRead | idle   |             |              | INSERT INTO DoombotLocations (location) VALUES ('Latveria City') | client backend
 21848 | latveriadb | 6309 |    21847 | viktor_vaughn |                           | 192.168.65.1 |                 |       34683 | 2024-02-28 06:12:55.496213+00 |                               | 2024-02-28 06:12:55.52196+00  | 2024-02-28 06:12:55.53981+00  | Client          | ClientRead | idle   |             |              | INSERT INTO DoombotLocations (location) VALUES ('Latveria City') | client backend
 21848 | latveriadb | 6332 |    21847 | viktor_vaughn |                           | 192.168.65.1 |                 |       34684 | 2024-02-28 06:13:00.54746+00  |                               | 2024-02-28 06:13:00.593595+00 | 2024-02-28 06:13:00.620576+00 | Client          | ClientRead | idle   |             |              | INSERT INTO DoombotLocations (location) VALUES ('Latveria City') | client backend
 16384 | sg         |  212 |       10 | sg            |                           |              |                 |             |                               |                               |                               |                               |                 |            |        |             |              | <insufficient privilege>                                         |
       |            |   26 |          |               |                           |              |                 |             |                               |                               |                               |                               |                 |            |        |             |              | <insufficient privilege>                                         |
       |            |   25 |          |               |                           |              |                 |             |                               |                               |                               |                               |                 |            |        |             |              | <insufficient privilege>                                         |
       |            |   27 |          |               |                           |              |                 |             |                               |                               |                               |                               |                 |            |        |             |              | <insufficient privilege>                                         |
(43 rows)

@DaedalusG
dynamically select only for pg_stat_activity with datname from the ap…
2ec2225 
…plication making the request i.e. sourcegraph-autoupgrader
@DaedalusG DaedalusG requested a review from Strum355 February 28, 2024 06:35
@cla-bot cla-bot Bot added the cla-signed label Feb 28, 2024
Strum355
Strum355 approved these changes Feb 28, 2024
View reviewed changes

@Strum355 Strum355 left a comment

Copy link
Copy Markdown
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

full send

@DaedalusG DaedalusG requested a review from airamare01 February 28, 2024 08:54
@DaedalusG DaedalusG marked this pull request as ready for review February 28, 2024 09:29
@DaedalusG DaedalusG merged commit d04f2fd into main Feb 28, 2024
@DaedalusG DaedalusG deleted the wg/bug-autoupgrade-checkForDisconnects branch February 28, 2024 09:45
sourcegraph-release-bot pushed a commit that referenced this pull request Feb 28, 2024
@DaedalusG @github-actions
dynamically select only for pg_stat_activity with datname from the ap… (
8110731 
#60771)

dynamically select only for pg_stat_activity with datname from the application making the request i.e. sourcegraph-autoupgrader

(cherry picked from commit d04f2fd)
@sourcegraph-release-bot sourcegraph-release-bot mentioned this pull request Feb 28, 2024
Merged
This was referenced Mar 7, 2024
Closed
Closed
DaedalusG added a commit that referenced this pull request Mar 8, 2024
@sourcegraph-release-bot @DaedalusG
[Backport 5.3] dynamically select only for pg_stat_activity with datn…
9bc9f75 
…ame from the ap… (#60773)

dynamically select only for pg_stat_activity with datname from the ap… (#60771)

dynamically select only for pg_stat_activity with datname from the application making the request i.e. sourcegraph-autoupgrader

(cherry picked from commit d04f2fd)

Co-authored-by: Warren Gifford <warren@sourcegraph.com>
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.

Reviewers

@airamare01 airamare01 Awaiting requested review from airamare01

1 more reviewer

@Strum355 Strum355 Strum355 approved these changes

Reviewers whose approvals may not affect merge requirements

Assignees

No one assigned

Labels

backport 5.3 backported-to-5.3 cla-signed

Projects

None yet

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

2 participants

@DaedalusG @Strum355