1
/*
2
 *  This file is part of CoCalc: Copyright © 2020 Sagemath, Inc.
3
 *  License: MS-RSL – see LICENSE.md for details
4
 */
5

6
// This manages deleting patches. The underlying problem is that there could be a large number of patches, which stalls the DB.
7
// It's better to keep the number of row deletions small, to speed up the operation, only lock less rows for a shorter amount of time, etc.
8

9
import getLogger from "@cocalc/backend/logger";
10
import type { PostgreSQL } from "./types";
11
import { delay } from "awaiting";
12

13
const logger = getLogger("database:delete-patches");
14

15
// max number of patches to delete at once – 10000 should take a few seconds
16
const MAX_AT_ONCE = parseInt(
17
  process.env.SYNCSTRING_DELETE_MAX_AT_ONCE ?? "10000",
18
);
19
// delay between deleting a chunk of patches
20
const DELAY_S = parseInt(process.env.SYNCSTRING_DELETE_DELAY_CHUNK_S ?? "1");
21

22
interface DeletePatchesOpts {
23
  db: PostgreSQL;
24
  string_id: string;
25
  cb?: Function;
26
}
27

28
async function patchset_limit(opts: {
29
  db: PostgreSQL;
30
  string_id: string;
31
}): Promise<string | undefined> {
32
  const { db, string_id } = opts;
33
  const q = await db.async_query({
34
    query: "SELECT time FROM patches",
35
    where: { "string_id = $::CHAR(40)": string_id },
36
    limit: 1,
37
    offset: MAX_AT_ONCE,
38
  });
39
  if (q.rows.length == 0) {
40
    return undefined;
41
  } else {
42
    return q.rows[0].time;
43
  }
44
}
45

46
export async function delete_patches(opts: DeletePatchesOpts): Promise<void> {
47
  const { db, string_id, cb } = opts;
48

49
  while (true) {
50
    const limit = await patchset_limit({ db, string_id });
51

52
    logger.debug(
53
      `deleting patches string_id='${string_id}' until limit='${limit}'`,
54
    );
55
    const where = { "string_id = $::CHAR(40)": string_id };
56
    if (limit != null) {
57
      where["time <= $::TIMESTAMP"] = limit;
58
    }
59
    await db.async_query({
60
      query: "DELETE FROM patches",
61
      where,
62
      timeout_s: 300, // delete ops could take a bit
63
    });
64
    if (limit != null) {
65
      await delay(DELAY_S * 1000);
66
    } else {
67
      break;
68
    }
69
  }
70

71
  if (typeof cb === "function") cb();
72
}
73

74