mirror of
https://github.com/block/buzz.git
synced 2026-10-27 20:41:57 +00:00
1c7bcac25c
Contract step for BUZZ-315. Migration 0061 drops the 32 community_write_fence_* triggers (partition clones follow) and the five fence-only functions: enforce_community_write_fence, attach_community_write_fence, community_write_fence_excluded_table, community_write_allowed and assert_community_write_allowed. It runs under SET LOCAL lock_timeout = '5s' so the AccessExclusive DDL cannot queue serving traffic indefinitely. The desired-state schema, reconcile script and migration source assertions follow. New inventories leave the legacy fenced_tables snapshot empty, and the live-fence catalog reader is deleted. community_deletion_lock_key and the community tombstone stay. Deploy gate: merge only after the app-side admission PR, the tenant-admitted fleet sweeps and the fence-object tolerance PR are deployed on every pod, with no rollback below them expected. Signed-off-by: Elrond <28d6302a099e5225b02c4155ac4236e4912603df2ab08dbfc2f4fef08ce598c8@buzz.block.builderlab.xyz>
237 lines
10 KiB
SQL
237 lines
10 KiB
SQL
-- Reconcile schema details that pgschema does not preserve.
|
|
--
|
|
-- pgschema reconciles DDL, but it does not execute seed DML or preserve every
|
|
-- table storage parameter from schema/schema.sql. It also currently emits
|
|
-- partition children as standalone CREATE TABLE statements. Every pgschema
|
|
-- apply caller must run this idempotent script so fresh bootstraps converge on
|
|
-- the same live database contract as migration-managed databases.
|
|
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM pg_inherits
|
|
WHERE inhparent = 'events'::regclass
|
|
AND inhrelid = 'events_p_past'::regclass
|
|
) THEN
|
|
-- pgschema may copy parent triggers onto standalone children. Drop
|
|
-- those copies before ATTACH; PostgreSQL recreates inherited parent
|
|
-- triggers while attaching and rejects same-named child triggers.
|
|
DROP TRIGGER IF EXISTS events_enqueue_push_match ON events_p_past;
|
|
DROP TRIGGER IF EXISTS events_refresh_channel_ttl ON events_p_past;
|
|
DROP TRIGGER IF EXISTS events_created_at_floor ON events_p_past;
|
|
DROP TRIGGER IF EXISTS trg_events_guard_channel_roster_snapshot ON events_p_past;
|
|
ALTER TABLE events ATTACH PARTITION events_p_past
|
|
FOR VALUES FROM (MINVALUE) TO ('2026-01-01');
|
|
END IF;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM pg_inherits
|
|
WHERE inhparent = 'events'::regclass
|
|
AND inhrelid = 'events_p2026_01'::regclass
|
|
) THEN
|
|
DROP TRIGGER IF EXISTS events_enqueue_push_match ON events_p2026_01;
|
|
DROP TRIGGER IF EXISTS events_refresh_channel_ttl ON events_p2026_01;
|
|
DROP TRIGGER IF EXISTS events_created_at_floor ON events_p2026_01;
|
|
DROP TRIGGER IF EXISTS trg_events_guard_channel_roster_snapshot ON events_p2026_01;
|
|
ALTER TABLE events ATTACH PARTITION events_p2026_01
|
|
FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
|
|
END IF;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM pg_inherits
|
|
WHERE inhparent = 'events'::regclass
|
|
AND inhrelid = 'events_p2026_02'::regclass
|
|
) THEN
|
|
DROP TRIGGER IF EXISTS events_enqueue_push_match ON events_p2026_02;
|
|
DROP TRIGGER IF EXISTS events_refresh_channel_ttl ON events_p2026_02;
|
|
DROP TRIGGER IF EXISTS events_created_at_floor ON events_p2026_02;
|
|
DROP TRIGGER IF EXISTS trg_events_guard_channel_roster_snapshot ON events_p2026_02;
|
|
ALTER TABLE events ATTACH PARTITION events_p2026_02
|
|
FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
|
|
END IF;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM pg_inherits
|
|
WHERE inhparent = 'events'::regclass
|
|
AND inhrelid = 'events_p2026_03'::regclass
|
|
) THEN
|
|
DROP TRIGGER IF EXISTS events_enqueue_push_match ON events_p2026_03;
|
|
DROP TRIGGER IF EXISTS events_refresh_channel_ttl ON events_p2026_03;
|
|
DROP TRIGGER IF EXISTS events_created_at_floor ON events_p2026_03;
|
|
DROP TRIGGER IF EXISTS trg_events_guard_channel_roster_snapshot ON events_p2026_03;
|
|
ALTER TABLE events ATTACH PARTITION events_p2026_03
|
|
FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');
|
|
END IF;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM pg_inherits
|
|
WHERE inhparent = 'events'::regclass
|
|
AND inhrelid = 'events_p2026_04'::regclass
|
|
) THEN
|
|
DROP TRIGGER IF EXISTS events_enqueue_push_match ON events_p2026_04;
|
|
DROP TRIGGER IF EXISTS events_refresh_channel_ttl ON events_p2026_04;
|
|
DROP TRIGGER IF EXISTS events_created_at_floor ON events_p2026_04;
|
|
DROP TRIGGER IF EXISTS trg_events_guard_channel_roster_snapshot ON events_p2026_04;
|
|
ALTER TABLE events ATTACH PARTITION events_p2026_04
|
|
FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');
|
|
END IF;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM pg_inherits
|
|
WHERE inhparent = 'events'::regclass
|
|
AND inhrelid = 'events_p2026_05'::regclass
|
|
) THEN
|
|
DROP TRIGGER IF EXISTS events_enqueue_push_match ON events_p2026_05;
|
|
DROP TRIGGER IF EXISTS events_refresh_channel_ttl ON events_p2026_05;
|
|
DROP TRIGGER IF EXISTS events_created_at_floor ON events_p2026_05;
|
|
DROP TRIGGER IF EXISTS trg_events_guard_channel_roster_snapshot ON events_p2026_05;
|
|
ALTER TABLE events ATTACH PARTITION events_p2026_05
|
|
FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');
|
|
END IF;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM pg_inherits
|
|
WHERE inhparent = 'events'::regclass
|
|
AND inhrelid = 'events_p2026_06'::regclass
|
|
) THEN
|
|
DROP TRIGGER IF EXISTS events_enqueue_push_match ON events_p2026_06;
|
|
DROP TRIGGER IF EXISTS events_refresh_channel_ttl ON events_p2026_06;
|
|
DROP TRIGGER IF EXISTS events_created_at_floor ON events_p2026_06;
|
|
DROP TRIGGER IF EXISTS trg_events_guard_channel_roster_snapshot ON events_p2026_06;
|
|
ALTER TABLE events ATTACH PARTITION events_p2026_06
|
|
FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');
|
|
END IF;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM pg_inherits
|
|
WHERE inhparent = 'events'::regclass
|
|
AND inhrelid = 'events_p_future'::regclass
|
|
) THEN
|
|
DROP TRIGGER IF EXISTS events_enqueue_push_match ON events_p_future;
|
|
DROP TRIGGER IF EXISTS events_refresh_channel_ttl ON events_p_future;
|
|
DROP TRIGGER IF EXISTS events_created_at_floor ON events_p_future;
|
|
DROP TRIGGER IF EXISTS trg_events_guard_channel_roster_snapshot ON events_p_future;
|
|
ALTER TABLE events ATTACH PARTITION events_p_future
|
|
FOR VALUES FROM ('2026-07-01') TO (MAXVALUE);
|
|
END IF;
|
|
|
|
-- When pgschema creates partition children as standalone tables, it also
|
|
-- preserves the parent's identity column on delivery_log children. PostgreSQL
|
|
-- rejects attaching a child table that has its own identity column, so each
|
|
-- delivery_log attach path drops that standalone identity first. Raw
|
|
-- schema-created partitions are already attached, so these branches do not
|
|
-- run against inherited partition columns.
|
|
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM pg_inherits
|
|
WHERE inhparent = 'delivery_log'::regclass
|
|
AND inhrelid = 'delivery_log_p_past'::regclass
|
|
) THEN
|
|
ALTER TABLE delivery_log_p_past ALTER COLUMN id DROP IDENTITY IF EXISTS;
|
|
ALTER TABLE delivery_log ATTACH PARTITION delivery_log_p_past
|
|
FOR VALUES FROM (MINVALUE) TO ('2026-03-01');
|
|
END IF;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM pg_inherits
|
|
WHERE inhparent = 'delivery_log'::regclass
|
|
AND inhrelid = 'delivery_log_p2026_03'::regclass
|
|
) THEN
|
|
ALTER TABLE delivery_log_p2026_03 ALTER COLUMN id DROP IDENTITY IF EXISTS;
|
|
ALTER TABLE delivery_log ATTACH PARTITION delivery_log_p2026_03
|
|
FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');
|
|
END IF;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM pg_inherits
|
|
WHERE inhparent = 'delivery_log'::regclass
|
|
AND inhrelid = 'delivery_log_p2026_04'::regclass
|
|
) THEN
|
|
ALTER TABLE delivery_log_p2026_04 ALTER COLUMN id DROP IDENTITY IF EXISTS;
|
|
ALTER TABLE delivery_log ATTACH PARTITION delivery_log_p2026_04
|
|
FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');
|
|
END IF;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM pg_inherits
|
|
WHERE inhparent = 'delivery_log'::regclass
|
|
AND inhrelid = 'delivery_log_p2026_05'::regclass
|
|
) THEN
|
|
ALTER TABLE delivery_log_p2026_05 ALTER COLUMN id DROP IDENTITY IF EXISTS;
|
|
ALTER TABLE delivery_log ATTACH PARTITION delivery_log_p2026_05
|
|
FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');
|
|
END IF;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM pg_inherits
|
|
WHERE inhparent = 'delivery_log'::regclass
|
|
AND inhrelid = 'delivery_log_p2026_06'::regclass
|
|
) THEN
|
|
ALTER TABLE delivery_log_p2026_06 ALTER COLUMN id DROP IDENTITY IF EXISTS;
|
|
ALTER TABLE delivery_log ATTACH PARTITION delivery_log_p2026_06
|
|
FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');
|
|
END IF;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM pg_inherits
|
|
WHERE inhparent = 'delivery_log'::regclass
|
|
AND inhrelid = 'delivery_log_p_future'::regclass
|
|
) THEN
|
|
ALTER TABLE delivery_log_p_future ALTER COLUMN id DROP IDENTITY IF EXISTS;
|
|
ALTER TABLE delivery_log ATTACH PARTITION delivery_log_p_future
|
|
FOR VALUES FROM ('2026-07-01') TO (MAXVALUE);
|
|
END IF;
|
|
END $$;
|
|
|
|
-- pgschema drops multi-column CHECK constraints. Restore the direct-action
|
|
-- shape CHECK from schema/schema.sql (and migration 0055) verbatim.
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM pg_constraint
|
|
WHERE conrelid = 'relay_admin_actions'::regclass
|
|
AND conname = 'relay_admin_actions_direct_shape'
|
|
) THEN
|
|
ALTER TABLE relay_admin_actions
|
|
ADD CONSTRAINT relay_admin_actions_direct_shape CHECK (
|
|
report_id IS NOT NULL
|
|
OR (action = 'ban' AND enforcement_target_pubkey IS NOT NULL
|
|
AND timeout_secs IS NULL AND timeout_until IS NULL)
|
|
OR (action = 'timeout' AND enforcement_target_pubkey IS NOT NULL
|
|
AND timeout_secs IS NOT NULL AND timeout_until IS NOT NULL)
|
|
OR (action = 'delete' AND enforcement_target_event_id IS NOT NULL
|
|
AND enforcement_target_pubkey IS NOT NULL
|
|
AND timeout_secs IS NULL AND timeout_until IS NULL)
|
|
);
|
|
END IF;
|
|
END $$;
|
|
|
|
-- pgschema reconciles DDL but does not apply seed DML or table storage
|
|
-- parameters from schema/schema.sql. Restore those parts of the desired-state
|
|
-- contract explicitly and fail the bootstrap if the live catalog disagrees.
|
|
ALTER TABLE replica_heartbeat SET (vacuum_truncate = false);
|
|
|
|
INSERT INTO replica_heartbeat (id) VALUES (1)
|
|
ON CONFLICT (id) DO NOTHING;
|
|
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (
|
|
SELECT 1
|
|
FROM pg_class AS relation
|
|
JOIN pg_namespace AS namespace ON namespace.oid = relation.relnamespace
|
|
WHERE namespace.nspname = current_schema()
|
|
AND relation.relname = 'replica_heartbeat'
|
|
AND COALESCE(
|
|
relation.reloptions @> ARRAY['vacuum_truncate=false']::text[],
|
|
false
|
|
)
|
|
) THEN
|
|
RAISE EXCEPTION 'replica_heartbeat must disable vacuum truncation after pgschema apply';
|
|
END IF;
|
|
|
|
IF (SELECT count(*) FROM replica_heartbeat WHERE id = 1) <> 1 THEN
|
|
RAISE EXCEPTION 'replica_heartbeat must contain its singleton row after pgschema apply';
|
|
END IF;
|
|
END $$;
|