Repository: ponder-sh/ponder
Pull request #1237 (2024-11-12 19:06:21)

fix: non-unique instance_id upserts `_ponder_meta` and drops old tables


Patch:
diff --git a/.changeset/olive-baboons-cross.md b/.changeset/olive-baboons-cross.md
new file mode 100644
index 00000000..05ba43cb
--- /dev/null
+++ b/.changeset/olive-baboons-cross.md
@@ -0,0 +1,5 @@
+---
+"@ponder/core": patch
+---
+
+Fixed a bug with database instance id.
diff --git a/packages/core/src/database/index.ts b/packages/core/src/database/index.ts
index 347dbb0a..e21f3cd4 100644
--- a/packages/core/src/database/index.ts
+++ b/packages/core/src/database/index.ts
@@ -825,7 +825,12 @@ export const createDatabase = (args: {
               a.heartbeat_at < b.heartbeat_at ? -1 : 1,
             )[0]!;
 
-            if (isFirstAttempt && args.common.options.command !== "dev") {
+            if (
+              isFirstAttempt &&
+              args.common.options.command !== "dev" &&
+              (crashRecoveryApp === undefined ||
+                crashRecoveryApp.is_locked === 1)
+            ) {
               return {
                 status: "locked",
                 expiry:
@@ -842,6 +847,8 @@ export const createDatabase = (args: {
             await tx
               .insertInto("_ponder_meta")
               .values({ key: `status_${args.instanceId}`, value: null })
+              // @ts-ignore
+              .onConflict((oc) => oc.column("key").doUpdateSet({ value: null }))
               .execute();
             await tx
               .insertInto("_ponder_meta")
@@ -849,8 +856,32 @@ export const createDatabase = (args: {
                 key: `app_${args.instanceId}`,
                 value: newApp,
               })
+              .onConflict((oc) =>
+                oc
+                  .column("key")
+                  // @ts-ignore
+                  .doUpdateSet({ value: newApp }),
+              )
               .execute();
 
+            // drop tables in case of non-unique instance_id
+
+            for (const tableName of getTableNames(
+              args.schema,
+              newApp.instance_id,
+            )) {
+              await tx.schema
+                .dropTable(tableName.sql)
+                .cascade()
+                .ifExists()
+                .execute();
+              await tx.schema
+                .dropTable(tableName.reorg)
+                .cascade()
+                .ifExists()
+                .execute();
+            }
+
             for (let i = 0; i < args.statements.enums.sql.length; i++) {
               await sql
                 .raw(args.statements.enums.sql[i]!)


Test patch:
diff --git a/packages/core/src/database/index.test.ts b/packages/core/src/database/index.test.ts
index cd0864ba..e62e9984 100644
--- a/packages/core/src/database/index.test.ts
+++ b/packages/core/src/database/index.test.ts
@@ -436,6 +436,51 @@ test("setup() with the same build ID succeeds if the lock doesn't expires after
   await databaseTwo.kill();
 });
 
+test("setup() with the same instance ID upserts", async (context) => {
+  context.common.options.databaseHeartbeatInterval = 750;
+  context.common.options.databaseHeartbeatTimeout = 500;
+
+  const database = createDatabase({
+    common: context.common,
+    schema: { account },
+    databaseConfig: context.databaseConfig,
+    instanceId: "1234",
+    buildId: "abc",
+    ...buildSchema({
+      schema: { account },
+      instanceId: "1234",
+    }),
+  });
+  await database.setup();
+  await database.unlock();
+  await database.kill();
+
+  const databaseTwo = createDatabase({
+    common: context.common,
+    schema: { account },
+    databaseConfig: context.databaseConfig,
+    instanceId: "1234",
+    buildId: "abc",
+    ...buildSchema({
+      schema: { account },
+      instanceId: "1234",
+    }),
+  });
+
+  const { checkpoint } = await databaseTwo.setup();
+
+  expect(checkpoint).toMatchObject(encodeCheckpoint(zeroCheckpoint));
+
+  const metadata = await databaseTwo.qb.internal
+    .selectFrom("_ponder_meta")
+    .selectAll()
+    .execute();
+
+  expect(metadata).toHaveLength(3);
+
+  await databaseTwo.kill();
+});
+
 test("setup() drops old tables", async (context) => {
   for (let i = 0; i < 5; i++) {
     const database = createDatabase({