Postgres, step 2a: ON CONFLICT instead of INSERT OR IGNORE/REPLACE d1bf2d698777
Twelve INSERT OR IGNORE become INSERT ... ON CONFLICT DO NOTHING; the five INSERT OR REPLACE (redirects, the Diffusion index's state, sign-in links, artifacts) become ON CONFLICT (primary key) DO UPDATE SET the other columns. Both are SQLite and Postgres. Backups' restore keeps its own (SQLite only for now). 247 tests on SQLite. Refs #211
- Author
- Andy McCall @andymccall <[email protected]>
- via
- agent
Claude Code <[email protected]> · delegate for changes, until 2026-11-13 19:00Z · key ea5f78160088
- When
- · 2026-10-16 11:22 UTC
- Signature
- ✔ signed by Claude Code
- Hash
d1bf2d698777c051db9fb656317446513bc0b8ba3c7aadf3c65891c4a96a52c3- Depends on
- 18e381ea88c2 19bb31ff1f5c 42fa95540468 5d90e0e045e2 6a1780558353 83940d53664f 941abdd6bb4c b6b7e4cf6b24 cc1cd197df1d d23464c0ac60 de505df5cf8d e1ec9fbf538e f677d6270298
- Depended on by
- 439ecd8708a1
- In bookmarks
- api-unknown-fields cancel-landed-runs diffusion-pin-bookmark draft-ready main oh-comment-body-file proposal-list-fast
- In tags
- v0.6.0 v0.6.1 v0.7.0 v0.8.0 v0.9.0 v0.9.1 v0.9.11 v0.9.15 v0.9.16 v0.9.19 v0.9.2 v0.9.20 v0.9.21 v0.9.22 v0.9.23 v0.9.24 v0.9.3 v0.9.4 v0.9.5 v0.9.6 v0.9.7 v0.9.8 v0.9.9
- Edits
- 25 atoms, 0 tree operations, 10 files
Browse the tree at this change
modified crates/hub/src/db/accounts.rs +2 −2
| … | 262 unchanged lines |
||
| 263 | 263 | /// is retired so nobody can register it and pass for the account. |
|
| 264 | 264 | pub async fn delete_account(pool: &Pool, id: i64, username: &str) -> sqlx::Result<()> { |
|
| 265 | 265 | let mut tx = super::begin_write(pool).await?; |
|
| 266 | - | sqlx::query("INSERT OR IGNORE INTO retired_usernames (username, retired_ms) VALUES ($1, $2)") |
|
| 266 | + | sqlx::query("INSERT INTO retired_usernames (username, retired_ms) VALUES ($1, $2) ON CONFLICT DO NOTHING") |
|
| 267 | 267 | .bind(username) |
|
| 268 | 268 | .bind(now_ms()) |
|
| 269 | 269 | .execute(&mut *tx) |
|
| … | 144 unchanged lines |
||
| 414 | 414 | } |
|
| 415 | 415 | ||
| 416 | 416 | pub async fn add_team_member(pool: &Pool, team_id: i64, account_id: i64) -> sqlx::Result<()> { |
|
| 417 | - | sqlx::query("INSERT OR IGNORE INTO team_members (team_id, account_id) VALUES ($1, $2)") |
|
| 417 | + | sqlx::query("INSERT INTO team_members (team_id, account_id) VALUES ($1, $2) ON CONFLICT DO NOTHING") |
|
| 418 | 418 | .bind(team_id) |
|
| 419 | 419 | .bind(account_id) |
|
| 420 | 420 | .execute(pool) |
|
| … | 538 unchanged lines |
modified crates/hub/src/db/flow.rs +1 −1
| … | 1023 unchanged lines |
||
| 1024 | 1024 | ||
| 1025 | 1025 | pub async fn add_artifact(pool: &Pool, a: &Artifact) -> sqlx::Result<()> { |
|
| 1026 | 1026 | sqlx::query( |
|
| 1027 | - | "INSERT OR REPLACE INTO flow_artifacts (job_id, name, path, blob, size, content, created_ms) VALUES ($1, $2, $3, $4, $5, $6, $7)", |
|
| 1027 | + | "INSERT INTO flow_artifacts (job_id, name, path, blob, size, content, created_ms) VALUES ($1, $2, $3, $4, $5, $6, $7) ON CONFLICT (job_id, name) DO UPDATE SET path = excluded.path, blob = excluded.blob, size = excluded.size, content = excluded.content, created_ms = excluded.created_ms", |
|
| 1028 | 1028 | ) |
|
| 1029 | 1029 | .bind(a.job_id) |
|
| 1030 | 1030 | .bind(&a.name) |
|
| … | 115 unchanged lines |
modified crates/hub/src/db/issues.rs +3 −3
| … | 248 unchanged lines |
||
| 249 | 249 | ||
| 250 | 250 | pub async fn set_issue_label(pool: &Pool, issue_id: i64, label_id: i64, on: bool) -> sqlx::Result<()> { |
|
| 251 | 251 | if on { |
|
| 252 | - | sqlx::query("INSERT OR IGNORE INTO issue_labels (issue_id, label_id) VALUES ($1, $2)") |
|
| 252 | + | sqlx::query("INSERT INTO issue_labels (issue_id, label_id) VALUES ($1, $2) ON CONFLICT DO NOTHING") |
|
| 253 | 253 | .bind(issue_id) |
|
| 254 | 254 | .bind(label_id) |
|
| 255 | 255 | .execute(pool) |
|
| … | 41 unchanged lines |
||
| 297 | 297 | ||
| 298 | 298 | pub async fn set_proposal_label(pool: &Pool, proposal_id: i64, label_id: i64, on: bool) -> sqlx::Result<()> { |
|
| 299 | 299 | if on { |
|
| 300 | - | sqlx::query("INSERT OR IGNORE INTO proposal_labels (proposal_id, label_id) VALUES ($1, $2)") |
|
| 300 | + | sqlx::query("INSERT INTO proposal_labels (proposal_id, label_id) VALUES ($1, $2) ON CONFLICT DO NOTHING") |
|
| 301 | 301 | .bind(proposal_id) |
|
| 302 | 302 | .bind(label_id) |
|
| 303 | 303 | .execute(pool) |
|
| … | 21 unchanged lines |
||
| 325 | 325 | ||
| 326 | 326 | pub async fn set_assignee(pool: &Pool, issue_id: i64, account_id: i64, on: bool) -> sqlx::Result<()> { |
|
| 327 | 327 | if on { |
|
| 328 | - | sqlx::query("INSERT OR IGNORE INTO issue_assignees (issue_id, account_id) VALUES ($1, $2)") |
|
| 328 | + | sqlx::query("INSERT INTO issue_assignees (issue_id, account_id) VALUES ($1, $2) ON CONFLICT DO NOTHING") |
|
| 329 | 329 | .bind(issue_id) |
|
| 330 | 330 | .bind(account_id) |
|
| 331 | 331 | .execute(pool) |
|
| … | 68 unchanged lines |
modified crates/hub/src/db/notify.rs +1 −1
| … | 267 unchanged lines |
||
| 268 | 268 | } |
|
| 269 | 269 | ||
| 270 | 270 | pub async fn subscribe(pool: &Pool, account_id: i64, kind: &str, target_id: i64) -> sqlx::Result<()> { |
|
| 271 | - | sqlx::query("INSERT OR IGNORE INTO subscriptions (account_id, kind, target_id) VALUES ($1, $2, $3)") |
|
| 271 | + | sqlx::query("INSERT INTO subscriptions (account_id, kind, target_id) VALUES ($1, $2, $3) ON CONFLICT DO NOTHING") |
|
| 272 | 272 | .bind(account_id) |
|
| 273 | 273 | .bind(kind) |
|
| 274 | 274 | .bind(target_id) |
|
| … | 71 unchanged lines |
modified crates/hub/src/db/projects.rs +2 −2
| … | 81 unchanged lines |
||
| 82 | 82 | let old: Project = sqlx::query_as("SELECT * FROM projects WHERE id = $1").bind(id).fetch_one(&mut *tx).await?; |
|
| 83 | 83 | let number: i64 = |
|
| 84 | 84 | sqlx::query_scalar("SELECT COALESCE(MAX(number), 0) + 1 FROM projects WHERE owner = $1").bind(to).fetch_one(&mut *tx).await?; |
|
| 85 | - | sqlx::query("INSERT OR REPLACE INTO project_redirects (owner, number, project_id) VALUES ($1, $2, $3)") |
|
| 85 | + | sqlx::query("INSERT INTO project_redirects (owner, number, project_id) VALUES ($1, $2, $3) ON CONFLICT (owner, number) DO UPDATE SET project_id = excluded.project_id") |
|
| 86 | 86 | .bind(&old.owner) |
|
| 87 | 87 | .bind(old.number) |
|
| 88 | 88 | .bind(id) |
|
| … | 137 unchanged lines |
||
| 226 | 226 | .fetch_one(pool) |
|
| 227 | 227 | .await?; |
|
| 228 | 228 | let r = sqlx::query( |
|
| 229 | - | "INSERT OR IGNORE INTO project_cards (project_id, column_id, position, kind, repo_id, number, note, added_by, created_ms) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)", |
|
| 229 | + | "INSERT INTO project_cards (project_id, column_id, position, kind, repo_id, number, note, added_by, created_ms) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9) ON CONFLICT DO NOTHING", |
|
| 230 | 230 | ) |
|
| 231 | 231 | .bind(project_id) |
|
| 232 | 232 | .bind(c.column_id) |
|
| … | 86 unchanged lines |
modified crates/hub/src/db/proposals.rs +2 −2
| … | 95 unchanged lines |
||
| 96 | 96 | /// Take the next number of a repository's shared number space. |
|
| 97 | 97 | pub async fn next_number(pool: &Pool, repo_id: i64) -> sqlx::Result<i64> { |
|
| 98 | 98 | let mut tx = super::begin_write(pool).await?; |
|
| 99 | - | sqlx::query("INSERT OR IGNORE INTO numbers (repo_id, next) VALUES ($1, 1)").bind(repo_id).execute(&mut *tx).await?; |
|
| 99 | + | sqlx::query("INSERT INTO numbers (repo_id, next) VALUES ($1, 1) ON CONFLICT DO NOTHING").bind(repo_id).execute(&mut *tx).await?; |
|
| 100 | 100 | let n: i64 = sqlx::query_scalar("SELECT next FROM numbers WHERE repo_id = $1").bind(repo_id).fetch_one(&mut *tx).await?; |
|
| 101 | 101 | sqlx::query("UPDATE numbers SET next = next + 1 WHERE repo_id = $1").bind(repo_id).execute(&mut *tx).await?; |
|
| 102 | 102 | tx.commit().await?; |
|
| … | 267 unchanged lines |
||
| 370 | 370 | /// Ask an account to review a proposal; false when it was already asked. |
|
| 371 | 371 | pub async fn request_review(pool: &Pool, proposal_id: i64, account_id: i64, by: &str) -> sqlx::Result<bool> { |
|
| 372 | 372 | let r = sqlx::query( |
|
| 373 | - | "INSERT OR IGNORE INTO proposal_review_requests (proposal_id, account_id, requested_by, requested_ms) VALUES ($1, $2, $3, $4)", |
|
| 373 | + | "INSERT INTO proposal_review_requests (proposal_id, account_id, requested_by, requested_ms) VALUES ($1, $2, $3, $4) ON CONFLICT DO NOTHING", |
|
| 374 | 374 | ) |
|
| 375 | 375 | .bind(proposal_id) |
|
| 376 | 376 | .bind(account_id) |
|
| … | 28 unchanged lines |
modified crates/hub/src/db/repos.rs +1 −1
| … | 160 unchanged lines |
||
| 161 | 161 | .execute(pool) |
|
| 162 | 162 | .await?; |
|
| 163 | 163 | sqlx::query("DELETE FROM repo_redirects WHERE old_owner = $1 AND old_name = $2").bind(new_owner).bind(name).execute(pool).await?; |
|
| 164 | - | sqlx::query("INSERT OR REPLACE INTO repo_redirects (old_owner, old_name, repo_id, moved_ms) VALUES ($1, $2, $3, $4)") |
|
| 164 | + | sqlx::query("INSERT INTO repo_redirects (old_owner, old_name, repo_id, moved_ms) VALUES ($1, $2, $3, $4) ON CONFLICT (old_owner, old_name) DO UPDATE SET repo_id = excluded.repo_id, moved_ms = excluded.moved_ms") |
|
| 165 | 165 | .bind(old_owner) |
|
| 166 | 166 | .bind(name) |
|
| 167 | 167 | .bind(id) |
|
| … | 195 unchanged lines |
modified crates/hub/src/db/runners.rs +1 −1
| … | 120 unchanged lines |
||
| 121 | 121 | ||
| 122 | 122 | /// The row of a runner configured with a hub.toml token, made on first sight. |
|
| 123 | 123 | pub async fn configured(pool: &Pool, token_hash: &str, name: &str) -> sqlx::Result<Runner> { |
|
| 124 | - | sqlx::query("INSERT OR IGNORE INTO flow_runners (name, token_hash, source, created_ms) VALUES ($1, $2, 'config', $3)") |
|
| 124 | + | sqlx::query("INSERT INTO flow_runners (name, token_hash, source, created_ms) VALUES ($1, $2, 'config', $3) ON CONFLICT DO NOTHING") |
|
| 125 | 125 | .bind(name) |
|
| 126 | 126 | .bind(token_hash) |
|
| 127 | 127 | .bind(now_ms()) |
|
| … | 322 unchanged lines |
modified crates/hub/src/diffusion.rs +1 −1
| … | 145 unchanged lines |
||
| 146 | 146 | .await |
|
| 147 | 147 | .map_err(|e| format!("insert {path}: {e}"))?; |
|
| 148 | 148 | } |
|
| 149 | - | sqlx::query("INSERT OR REPLACE INTO diffusion_indexed (consumer_id, bookmark, heads, updated_ms) VALUES ($1, $2, $3, $4)") |
|
| 149 | + | sqlx::query("INSERT INTO diffusion_indexed (consumer_id, bookmark, heads, updated_ms) VALUES ($1, $2, $3, $4) ON CONFLICT (consumer_id, bookmark) DO UPDATE SET heads = excluded.heads, updated_ms = excluded.updated_ms") |
|
| 150 | 150 | .bind(row.id) |
|
| 151 | 151 | .bind(bookmark) |
|
| 152 | 152 | .bind(heads_key(heads)) |
|
| … | 1383 unchanged lines |
modified crates/hub/src/signin.rs +1 −1
| … | 312 unchanged lines |
||
| 313 | 313 | } |
|
| 314 | 314 | ||
| 315 | 315 | pub async fn link(pool: &Pool, provider: &str, subject: &str, account_id: i64, login: &str) -> sqlx::Result<()> { |
|
| 316 | - | sqlx::query("INSERT OR REPLACE INTO account_logins (provider, subject, account_id, login, linked_ms) VALUES ($1, $2, $3, $4, $5)") |
|
| 316 | + | sqlx::query("INSERT INTO account_logins (provider, subject, account_id, login, linked_ms) VALUES ($1, $2, $3, $4, $5) ON CONFLICT (provider, subject) DO UPDATE SET account_id = excluded.account_id, login = excluded.login, linked_ms = excluded.linked_ms") |
|
| 317 | 317 | .bind(provider) |
|
| 318 | 318 | .bind(subject) |
|
| 319 | 319 | .bind(account_id) |
|
| … | 65 unchanged lines |