Skip to content

Commit 751f4c4

Browse files
authored
Miscellaneous database cleanup (#1115)
Here, some miscellaneous database cleanup that we've been tracking for a while. Nothing here is worth sending out by itself, but the next time we do a database migration for something else, we can bundle these changes in and send them out for free. Fixes #874, #875, and #1012.
1 parent ae303ec commit 751f4c4

28 files changed

Lines changed: 278 additions & 367 deletions

‎riverdriver/river_driver_interface.go‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -906,7 +906,7 @@ func MigrationLineMainTruncateTables(version int) []string {
906906
case 5, 6:
907907
return []string{"river_job", "river_leader", "river_queue", "river_client", "river_client_queue"}
908908
case 0, 7:
909-
return []string{"river_job", "river_leader", "river_queue", "river_client", "river_client_queue", "river_notification"}
909+
return []string{"river_job", "river_leader", "river_queue", "river_notification"}
910910
}
911911

912912
panic(fmt.Sprintf("unrecognized migration version: %d", version))

‎riverdriver/riverdatabasesql/internal/dbsqlc/models.go‎

Lines changed: 0 additions & 19 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎riverdriver/riverdatabasesql/internal/dbsqlc/river_client.sql.go‎

Lines changed: 0 additions & 53 deletions
This file was deleted.

‎riverdriver/riverdatabasesql/internal/dbsqlc/river_client_queue.sql.go‎

Lines changed: 0 additions & 62 deletions
This file was deleted.

‎riverdriver/riverdatabasesql/internal/dbsqlc/sqlc.yaml‎

Lines changed: 0 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -3,8 +3,6 @@ sql:
33
- engine: "postgresql"
44
queries:
55
- ../../../riverpgxv5/internal/dbsqlc/pg_misc.sql
6-
- ../../../riverpgxv5/internal/dbsqlc/river_client.sql
7-
- ../../../riverpgxv5/internal/dbsqlc/river_client_queue.sql
86
- ../../../riverpgxv5/internal/dbsqlc/river_job.sql
97
- ../../../riverpgxv5/internal/dbsqlc/river_leader.sql
108
- ../../../riverpgxv5/internal/dbsqlc/river_migration.sql
@@ -13,8 +11,6 @@ sql:
1311
- ../../../riverpgxv5/internal/dbsqlc/schema.sql
1412
schema:
1513
- ../../../riverpgxv5/internal/dbsqlc/pg_misc.sql
16-
- ../../../riverpgxv5/internal/dbsqlc/river_client.sql
17-
- ../../../riverpgxv5/internal/dbsqlc/river_client_queue.sql
1814
- ../../../riverpgxv5/internal/dbsqlc/river_job.sql
1915
- ../../../riverpgxv5/internal/dbsqlc/river_leader.sql
2016
- ../../../riverpgxv5/internal/dbsqlc/river_migration.sql

‎riverdriver/riverdatabasesql/migration/main/007_notification_outbox_and_sqlite_json_to_jsonb.down.sql‎

Lines changed: 0 additions & 10 deletions
This file was deleted.
Lines changed: 56 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,56 @@
1+
--
2+
-- SQL cleanup rollback.
3+
--
4+
5+
--
6+
-- Add back unused tables `river_client` and `river_client_queue`.
7+
--
8+
9+
CREATE UNLOGGED TABLE /* TEMPLATE: schema */river_client (
10+
id text PRIMARY KEY NOT NULL,
11+
created_at timestamptz NOT NULL DEFAULT now(),
12+
metadata jsonb NOT NULL DEFAULT '{}',
13+
paused_at timestamptz,
14+
updated_at timestamptz NOT NULL,
15+
CONSTRAINT name_length CHECK (char_length(id) > 0 AND char_length(id) < 128)
16+
);
17+
18+
CREATE UNLOGGED TABLE /* TEMPLATE: schema */river_client_queue (
19+
river_client_id text NOT NULL REFERENCES /* TEMPLATE: schema */river_client (id) ON DELETE CASCADE,
20+
name text NOT NULL,
21+
created_at timestamptz NOT NULL DEFAULT now(),
22+
max_workers bigint NOT NULL DEFAULT 0,
23+
metadata jsonb NOT NULL DEFAULT '{}',
24+
num_jobs_completed bigint NOT NULL DEFAULT 0,
25+
num_jobs_running bigint NOT NULL DEFAULT 0,
26+
updated_at timestamptz NOT NULL,
27+
PRIMARY KEY (river_client_id, name),
28+
CONSTRAINT name_length CHECK (char_length(name) > 0 AND char_length(name) < 128),
29+
CONSTRAINT num_jobs_completed_zero_or_positive CHECK (num_jobs_completed >= 0),
30+
CONSTRAINT num_jobs_running_zero_or_positive CHECK (num_jobs_running >= 0)
31+
);
32+
33+
--
34+
-- Revert addition of `DEFAULT 25` to `river_job.max_attempts`.
35+
--
36+
37+
ALTER TABLE /* TEMPLATE: schema */river_job
38+
ALTER COLUMN max_attempts DROP DEFAULT;
39+
40+
--
41+
-- Changes `river_queue.updated_at` to revert the default of `CURRENT_TIMESTAMP`.
42+
--
43+
44+
ALTER TABLE /* TEMPLATE: schema */river_queue
45+
ALTER COLUMN updated_at DROP DEFAULT;
46+
47+
--
48+
-- SQLite JSONB conversion rollback.
49+
--
50+
-- No-op. PostgreSQL already stores River JSON columns as jsonb.
51+
52+
--
53+
-- Notification outbox rollback.
54+
--
55+
56+
DROP TABLE /* TEMPLATE: schema */river_notification;

riverdriver/riverpgxv5/migration/main/007_notification_outbox_and_sqlite_json_to_jsonb.up.sql renamed to riverdriver/riverdatabasesql/migration/main/007_notification_outbox_sqlite_jsonb_and_sql_cleanup.up.sql

Lines changed: 25 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -17,3 +17,28 @@ CREATE INDEX river_notification_topic_id_idx ON /* TEMPLATE: schema */river_noti
1717
-- SQLite JSONB conversion.
1818
--
1919
-- No-op. PostgreSQL already stores River JSON columns as jsonb.
20+
21+
--
22+
-- SQL cleanup.
23+
--
24+
25+
--
26+
-- Drop unused tables `river_client` and `river_client_queue`.
27+
--
28+
29+
DROP TABLE /* TEMPLATE: schema */river_client_queue;
30+
DROP TABLE /* TEMPLATE: schema */river_client;
31+
32+
--
33+
-- Adds `DEFAULT 25` to `river_job.max_attempts`.
34+
--
35+
36+
ALTER TABLE /* TEMPLATE: schema */river_job
37+
ALTER COLUMN max_attempts SET DEFAULT 25;
38+
39+
--
40+
-- Changes `river_queue.updated_at` to have a default of `CURRENT_TIMESTAMP`.
41+
--
42+
43+
ALTER TABLE /* TEMPLATE: schema */river_queue
44+
ALTER COLUMN updated_at SET DEFAULT CURRENT_TIMESTAMP;

‎riverdriver/riverdrivertest/migration.go‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -58,7 +58,7 @@ func exerciseMigration[TTx any](ctx context.Context, t *testing.T,
5858
t.Parallel()
5959

6060
driver, _ := driverWithSchema(ctx, t, nil)
61-
expectedLatestTables := []string{"river_job", "river_leader", "river_queue", "river_client", "river_client_queue", "river_notification"}
61+
expectedLatestTables := []string{"river_job", "river_leader", "river_queue", "river_notification"}
6262

6363
require.Empty(t, driver.GetMigrationTruncateTables(riverdriver.MigrationLineMain, 1))
6464
require.Equal(t, []string{"river_job", "river_leader"},

‎riverdriver/riverpgxv5/internal/dbsqlc/models.go‎

Lines changed: 0 additions & 19 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

0 commit comments

Comments
 (0)