ALTER TABLE events ADD COLUMN id_desc bigint; CREATE OR REPLACE FUNCTION zzz_events_autofill_desc() RETURNS trigger AS $body$ BEGIN IF TG_OP = 'INSERT' THEN IF NEW.id_desc IS NULL THEN NEW.id_desc := heerid_to_desc(NEW.id); END IF; ELSIF TG_OP = 'UPDATE' THEN IF NEW.id IS DISTINCT FROM OLD.id THEN NEW.id_desc := heerid_to_desc(NEW.id); ELSIF NEW.id_desc IS NULL THEN NEW.id_desc := heerid_to_desc(NEW.id); END IF; END IF; RETURN NEW; END; $body$ LANGUAGE plpgsql; DROP TRIGGER IF EXISTS zzz_events_autofill_desc ON events; CREATE TRIGGER zzz_events_autofill_desc BEFORE INSERT OR UPDATE ON events FOR EACH ROW EXECUTE FUNCTION zzz_events_autofill_desc(); -- Partitioned parent: invoke the backfill primitive once per leaf -- partition. The runner enumerates leaves from pg_inherits at apply -- time and emits one statement per leaf in deterministic -- regclass::text order, replacing the <EACH_LEAF_TABLE> placeholder -- with the concrete partition name. Operators hand-running this -- file MUST expand the placeholder themselves before executing. CALL heeranjid_bulk_backfill('<EACH_LEAF_TABLE>', 'id', 'id_desc', 'heer', 10000); SELECT count(*) FROM events WHERE id_desc IS NULL CREATE UNIQUE INDEX events_ts_id_desc_idx ON ONLY events (ts, id_desc); -- Per leaf: CREATE UNIQUE INDEX CONCURRENTLY <leaf>_ts_id_desc_idx -- ON <leaf> (ts, id_desc); -- Then ALTER INDEX events_ts_id_desc_idx ATTACH PARTITION -- <leaf>_ts_id_desc_idx; -- The runner enumerates leaves from pg_inherits and emits these -- per-leaf statements at apply time. ALTER TABLE events ADD CONSTRAINT events_id_desc_nn CHECK (id_desc IS NOT NULL) NOT VALID; ALTER TABLE events VALIDATE CONSTRAINT events_id_desc_nn; ALTER TABLE events ALTER COLUMN id_desc SET NOT NULL; ALTER TABLE events DROP CONSTRAINT events_id_desc_nn; ALTER TABLE events DROP CONSTRAINT events_pkey; ALTER TABLE events ADD PRIMARY KEY (ts, id_desc); ALTER TABLE events ALTER COLUMN id_desc SET DEFAULT heerid_next_desc(); ALTER TABLE events ALTER COLUMN id DROP DEFAULT; ALTER TABLE events DROP COLUMN id; DROP TRIGGER zzz_events_autofill_desc ON events; DROP FUNCTION zzz_events_autofill_desc() CASCADE; ALTER TABLE events RENAME COLUMN id_desc TO id;
