41 lines
2.2 KiB
PL/PgSQL
41 lines
2.2 KiB
PL/PgSQL
BEGIN;
|
|
CREATE TABLE public.server_poll_retirement_claims (
|
|
receipt_id uuid PRIMARY KEY REFERENCES public.server_poll_storage_admissions(receipt_id) ON DELETE CASCADE,
|
|
old_archive_proof text NOT NULL CHECK(old_archive_proof ~ '^[0-9a-f]{64}$'),
|
|
claim_token uuid NOT NULL,
|
|
claim_xid xid8 NOT NULL DEFAULT pg_current_xact_id()
|
|
);
|
|
CREATE FUNCTION public.server_poll_retirement_claim_immutable() RETURNS trigger
|
|
LANGUAGE plpgsql SET search_path=pg_catalog,public AS $$
|
|
BEGIN
|
|
IF TG_OP='INSERT' AND NEW.claim_xid=pg_current_xact_id() AND EXISTS(
|
|
SELECT 1 FROM public.server_poll_archives r WHERE r.receipt_id=NEW.receipt_id
|
|
AND r.version=1 AND r.archive_proof=NEW.old_archive_proof
|
|
) THEN RETURN NEW; END IF;
|
|
IF TG_OP='DELETE' AND pg_trigger_depth()>1 AND NOT EXISTS(
|
|
SELECT 1 FROM public.server_poll_storage_admissions a WHERE a.receipt_id=OLD.receipt_id
|
|
) THEN RETURN OLD; END IF;
|
|
RAISE EXCEPTION 'poll retirement claim cannot be replaced or reopened';
|
|
END $$;
|
|
CREATE TRIGGER server_poll_retirement_claim_immutable
|
|
BEFORE INSERT OR UPDATE OR DELETE ON public.server_poll_retirement_claims
|
|
FOR EACH ROW EXECUTE FUNCTION public.server_poll_retirement_claim_immutable();
|
|
CREATE TRIGGER server_poll_retirement_claim_no_truncate
|
|
BEFORE TRUNCATE ON public.server_poll_retirement_claims
|
|
FOR EACH STATEMENT EXECUTE FUNCTION public.server_poll_retirement_claim_immutable();
|
|
CREATE FUNCTION public.server_poll_retirement_requires_claim() RETURNS trigger
|
|
LANGUAGE plpgsql SET search_path=pg_catalog,public AS $$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM public.server_poll_retirement_claims c
|
|
WHERE c.receipt_id=OLD.receipt_id AND c.old_archive_proof=OLD.archive_proof
|
|
AND c.claim_token::text=current_setting('mm.poll_retire_claim',true)
|
|
AND c.claim_xid<>pg_current_xact_id())
|
|
THEN RAISE EXCEPTION 'poll retirement requires prior exact claim'; END IF;
|
|
RETURN NEW;
|
|
END $$;
|
|
CREATE TRIGGER server_poll_retirement_requires_claim BEFORE UPDATE ON public.server_poll_archives
|
|
FOR EACH ROW WHEN(OLD.version=1 AND NEW.version=2)
|
|
EXECUTE FUNCTION public.server_poll_retirement_requires_claim();
|
|
INSERT INTO public.schema_migrations(id) VALUES ('0047_server_poll_retirement_claims') ON CONFLICT(id) DO NOTHING;
|
|
COMMIT;
|