launchpad-reviewers team mailing list archive

[Colin Watson <mp+438771@xxxxxxxxxxxxxxxxxx>]

Colin Watson has proposed merging ~cjwatson/launchpad:db-kafka-offset into launchpad:db-devel.

Commit message:
Add KafkaOffset table

Requested reviews:
  William Grant (wgrant): db
  Launchpad code reviewers (launchpad-reviewers): db

For more details, see:
https://code.launchpad.net/~cjwatson/launchpad/+git/launchpad/+merge/438771

In order to consume events from Kafka, we'll need a way to keep track of our position in any given event stream.  This is tracked by topic name and partition (with the number of partitions depending on the number of parallel consumers of any given topic).  Updating a table of (topic, partition, offset) in the same transaction as we handle whatever else we need to do for that event gives us exactly-once event delivery, and seems to integrate quite nicely into the rest of Launchpad.
-- 
Your team Launchpad code reviewers is requested to review the proposed merge of ~cjwatson/launchpad:db-kafka-offset into launchpad:db-devel.

diff --git a/database/schema/patch-2211-18-0.sql b/database/schema/patch-2211-18-0.sql
new file mode 100644
index 0000000..c0c5e5d
--- /dev/null
+++ b/database/schema/patch-2211-18-0.sql
@@ -0,0 +1,16 @@
+-- Copyright 2023 Canonical Ltd.  This software is licensed under the
+-- GNU Affero General Public License version 3 (see the file LICENSE).
+
+SET client_min_messages=ERROR;
+
+CREATE TABLE KafkaOffset (
+    id serial PRIMARY KEY,
+    topic text NOT NULL,
+    partition integer NOT NULL,
+    stored_offset integer NOT NULL
+);
+
+CREATE UNIQUE INDEX kafkaoffset__topic__partition__key
+    ON KafkaOffset (topic, partition);
+
+INSERT INTO LaunchpadDatabaseRevision VALUES (2211, 18, 0);