Replace planning-era names and squash initial schema
Use descriptive test and executable names so implementation surfaces do not depend on journal organization. Consolidate the unreleased migration history into the authoritative initial schema because no deployed database requires an upgrade path.
This commit is contained in:
parent
0053860c95
commit
15eb0038d2
4 changed files with 299 additions and 436 deletions
86
sql/001_initial_schema.sql
Normal file
86
sql/001_initial_schema.sql
Normal file
|
|
@ -0,0 +1,86 @@
|
|||
CREATE TABLE pools (
|
||||
pool_id TEXT PRIMARY KEY,
|
||||
limit_bytes BIGINT NOT NULL CHECK (limit_bytes > 0),
|
||||
used_bytes BIGINT NOT NULL DEFAULT 0 CHECK (used_bytes >= 0),
|
||||
reserved_bytes BIGINT NOT NULL DEFAULT 0 CHECK (reserved_bytes >= 0),
|
||||
CONSTRAINT pools_live_bytes_within_limit
|
||||
CHECK (used_bytes + reserved_bytes <= limit_bytes)
|
||||
);
|
||||
|
||||
INSERT INTO pools (pool_id, limit_bytes, used_bytes, reserved_bytes)
|
||||
VALUES ('shared', 10737418240, 0, 0);
|
||||
|
||||
CREATE TABLE mutations (
|
||||
user_id TEXT NOT NULL,
|
||||
kind TEXT NOT NULL,
|
||||
operation_id TEXT NOT NULL,
|
||||
pool_id TEXT NOT NULL REFERENCES pools (pool_id),
|
||||
object_key TEXT NOT NULL,
|
||||
relative_path TEXT NOT NULL,
|
||||
declared_size BIGINT NOT NULL,
|
||||
content_sha256 TEXT,
|
||||
prior_size BIGINT NOT NULL,
|
||||
prior_sha256 TEXT,
|
||||
growth_bytes BIGINT NOT NULL,
|
||||
state TEXT NOT NULL,
|
||||
external_state TEXT NOT NULL DEFAULT 'idle',
|
||||
dispatch_started_at TIMESTAMPTZ,
|
||||
request_deadline_at TIMESTAMPTZ,
|
||||
worker_lease_until TIMESTAMPTZ,
|
||||
settled_at TIMESTAMPTZ,
|
||||
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
||||
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
||||
PRIMARY KEY (user_id, kind, operation_id),
|
||||
CONSTRAINT mutations_kind_check
|
||||
CHECK (kind IN ('put', 'delete')),
|
||||
CONSTRAINT mutations_declared_size_check
|
||||
CHECK (declared_size >= 0),
|
||||
CONSTRAINT mutations_prior_size_check
|
||||
CHECK (prior_size >= 0),
|
||||
CONSTRAINT mutations_growth_bytes_check
|
||||
CHECK (growth_bytes >= 0),
|
||||
CONSTRAINT mutations_state_check
|
||||
CHECK (state IN ('prepared', 'applying', 'succeeded', 'failed', 'rejected')),
|
||||
CONSTRAINT mutations_external_state_check
|
||||
CHECK (external_state IN ('idle', 'pending', 'settled')),
|
||||
CONSTRAINT mutations_dispatch_timing_check CHECK (
|
||||
external_state <> 'pending'
|
||||
OR (
|
||||
dispatch_started_at IS NOT NULL
|
||||
AND request_deadline_at IS NOT NULL
|
||||
AND worker_lease_until IS NOT NULL
|
||||
)
|
||||
)
|
||||
);
|
||||
|
||||
CREATE INDEX mutations_active_key_idx
|
||||
ON mutations (object_key)
|
||||
WHERE state IN ('prepared', 'applying');
|
||||
|
||||
CREATE INDEX mutations_active_idx
|
||||
ON mutations (state)
|
||||
WHERE state IN ('prepared', 'applying');
|
||||
|
||||
CREATE INDEX mutations_recovery_idx
|
||||
ON mutations (worker_lease_until, request_deadline_at, updated_at)
|
||||
WHERE state IN ('prepared', 'applying');
|
||||
|
||||
CREATE TABLE key_locks (
|
||||
object_key TEXT PRIMARY KEY,
|
||||
user_id TEXT NOT NULL,
|
||||
kind TEXT NOT NULL,
|
||||
operation_id TEXT NOT NULL,
|
||||
acquired_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
||||
FOREIGN KEY (user_id, kind, operation_id)
|
||||
REFERENCES mutations (user_id, kind, operation_id)
|
||||
);
|
||||
|
||||
CREATE TABLE objects_meta (
|
||||
object_key TEXT PRIMARY KEY,
|
||||
pool_id TEXT NOT NULL REFERENCES pools (pool_id),
|
||||
size_bytes BIGINT NOT NULL,
|
||||
content_sha256 TEXT NOT NULL,
|
||||
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
||||
CONSTRAINT objects_meta_size_bytes_check
|
||||
CHECK (size_bytes >= 0)
|
||||
);
|
||||
|
|
@ -1,61 +0,0 @@
|
|||
-- Durable quota and mutation protocol state for Nuthatch.
|
||||
|
||||
CREATE TABLE IF NOT EXISTS pools (
|
||||
pool_id TEXT PRIMARY KEY,
|
||||
limit_bytes BIGINT NOT NULL CHECK (limit_bytes > 0),
|
||||
used_bytes BIGINT NOT NULL DEFAULT 0 CHECK (used_bytes >= 0),
|
||||
reserved_bytes BIGINT NOT NULL DEFAULT 0 CHECK (reserved_bytes >= 0)
|
||||
);
|
||||
|
||||
INSERT INTO pools (pool_id, limit_bytes, used_bytes, reserved_bytes)
|
||||
VALUES ('shared', 10737418240, 0, 0)
|
||||
ON CONFLICT (pool_id) DO NOTHING;
|
||||
|
||||
CREATE TABLE IF NOT EXISTS mutations (
|
||||
user_id TEXT NOT NULL,
|
||||
kind TEXT NOT NULL CHECK (kind IN ('put', 'delete')),
|
||||
operation_id TEXT NOT NULL,
|
||||
pool_id TEXT NOT NULL REFERENCES pools (pool_id),
|
||||
object_key TEXT NOT NULL,
|
||||
relative_path TEXT NOT NULL,
|
||||
declared_size BIGINT NOT NULL CHECK (declared_size >= 0),
|
||||
content_sha256 TEXT,
|
||||
prior_size BIGINT NOT NULL CHECK (prior_size >= 0),
|
||||
prior_sha256 TEXT,
|
||||
growth_bytes BIGINT NOT NULL CHECK (growth_bytes >= 0),
|
||||
state TEXT NOT NULL CHECK (
|
||||
state IN ('prepared', 'applying', 'succeeded', 'failed', 'rejected')
|
||||
),
|
||||
external_state TEXT NOT NULL DEFAULT 'idle' CHECK (
|
||||
external_state IN ('idle', 'pending', 'settled')
|
||||
),
|
||||
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
||||
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
||||
PRIMARY KEY (user_id, kind, operation_id)
|
||||
);
|
||||
|
||||
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');
|
||||
|
||||
CREATE TABLE IF NOT EXISTS key_locks (
|
||||
object_key TEXT PRIMARY KEY,
|
||||
user_id TEXT NOT NULL,
|
||||
kind TEXT NOT NULL,
|
||||
operation_id TEXT NOT NULL,
|
||||
acquired_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
||||
FOREIGN KEY (user_id, kind, operation_id)
|
||||
REFERENCES mutations (user_id, kind, operation_id)
|
||||
);
|
||||
|
||||
CREATE TABLE IF NOT EXISTS objects_meta (
|
||||
object_key TEXT PRIMARY KEY,
|
||||
pool_id TEXT NOT NULL REFERENCES pools (pool_id),
|
||||
size_bytes BIGINT NOT NULL CHECK (size_bytes >= 0),
|
||||
content_sha256 TEXT NOT NULL,
|
||||
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
||||
);
|
||||
|
|
@ -1,56 +0,0 @@
|
|||
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);
|
||||
|
|
@ -1,33 +0,0 @@
|
|||
ALTER TABLE mutations
|
||||
ADD COLUMN IF NOT EXISTS dispatch_started_at TIMESTAMPTZ,
|
||||
ADD COLUMN IF NOT EXISTS request_deadline_at TIMESTAMPTZ,
|
||||
ADD COLUMN IF NOT EXISTS worker_lease_until TIMESTAMPTZ,
|
||||
ADD COLUMN IF NOT EXISTS settled_at TIMESTAMPTZ;
|
||||
|
||||
DO $$
|
||||
BEGIN
|
||||
IF EXISTS (
|
||||
SELECT 1
|
||||
FROM mutations
|
||||
WHERE external_state = 'pending'
|
||||
) THEN
|
||||
RAISE EXCEPTION
|
||||
'recovery settlement migration requires no pending mutations';
|
||||
END IF;
|
||||
END
|
||||
$$;
|
||||
|
||||
ALTER TABLE mutations
|
||||
ADD CONSTRAINT mutations_dispatch_timing_check CHECK (
|
||||
(external_state <> 'pending') OR
|
||||
(dispatch_started_at IS NOT NULL AND
|
||||
request_deadline_at IS NOT NULL AND
|
||||
worker_lease_until IS NOT NULL)
|
||||
) NOT VALID;
|
||||
|
||||
ALTER TABLE mutations
|
||||
VALIDATE CONSTRAINT mutations_dispatch_timing_check;
|
||||
|
||||
CREATE INDEX IF NOT EXISTS mutations_recovery_idx
|
||||
ON mutations (worker_lease_until, request_deadline_at, updated_at)
|
||||
WHERE state IN ('prepared', 'applying');
|
||||
Loading…
Reference in a new issue