Harden mutation admission and storage observation

sql/002_pool_limit_guard.sql

@@ -0,0 +1,56 @@
+ALTER TABLE mutations
+    DROP CONSTRAINT IF EXISTS mutations_kind_check,
+    DROP CONSTRAINT IF EXISTS mutations_declared_size_check,
+    DROP CONSTRAINT IF EXISTS mutations_prior_size_check,
+    DROP CONSTRAINT IF EXISTS mutations_growth_bytes_check,
+    DROP CONSTRAINT IF EXISTS mutations_state_check,
+    DROP CONSTRAINT IF EXISTS mutations_external_state_check;
+
+ALTER TABLE mutations
+    ADD CONSTRAINT mutations_kind_check
+        CHECK (kind IN ('put', 'delete')),
+    ADD CONSTRAINT mutations_declared_size_check
+        CHECK (declared_size >= 0),
+    ADD CONSTRAINT mutations_prior_size_check
+        CHECK (prior_size >= 0),
+    ADD CONSTRAINT mutations_growth_bytes_check
+        CHECK (growth_bytes >= 0),
+    ADD CONSTRAINT mutations_state_check
+        CHECK (state IN ('prepared', 'applying', 'succeeded', 'failed', 'rejected')),
+    ADD CONSTRAINT mutations_external_state_check
+        CHECK (external_state IN ('idle', 'pending', 'settled'));
+
+ALTER TABLE key_locks
+    ADD COLUMN IF NOT EXISTS acquired_at TIMESTAMPTZ NOT NULL DEFAULT now(),
+    DROP CONSTRAINT IF EXISTS key_locks_user_id_kind_operation_id_fkey;
+
+ALTER TABLE key_locks
+    ADD CONSTRAINT key_locks_user_id_kind_operation_id_fkey
+        FOREIGN KEY (user_id, kind, operation_id)
+        REFERENCES mutations (user_id, kind, operation_id);
+
+ALTER TABLE objects_meta
+    DROP CONSTRAINT IF EXISTS objects_meta_size_bytes_check;
+
+ALTER TABLE objects_meta
+    ADD CONSTRAINT objects_meta_size_bytes_check
+        CHECK (size_bytes >= 0);
+
+CREATE INDEX IF NOT EXISTS mutations_active_key_idx
+    ON mutations (object_key)
+    WHERE state IN ('prepared', 'applying');
+
+CREATE INDEX IF NOT EXISTS mutations_active_idx
+    ON mutations (state)
+    WHERE state IN ('prepared', 'applying');
+
+UPDATE pools
+SET limit_bytes = used_bytes + reserved_bytes
+WHERE used_bytes + reserved_bytes > limit_bytes;
+
+ALTER TABLE pools
+    DROP CONSTRAINT IF EXISTS pools_live_bytes_within_limit;
+
+ALTER TABLE pools
+    ADD CONSTRAINT pools_live_bytes_within_limit
+        CHECK (used_bytes + reserved_bytes <= limit_bytes);