ALTER TABLE parent ADD COLUMN id_desc bigint; CREATE OR REPLACE FUNCTION zzz_parent_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_parent_autofill_desc ON parent; CREATE TRIGGER zzz_parent_autofill_desc BEFORE INSERT OR UPDATE ON parent FOR EACH ROW EXECUTE FUNCTION zzz_parent_autofill_desc(); ALTER TABLE c ADD COLUMN p_id_desc bigint; CREATE OR REPLACE FUNCTION zzz_c_autofill_desc() RETURNS trigger AS $body$ BEGIN IF TG_OP = 'INSERT' THEN IF NEW.p_id_desc IS NULL THEN NEW.p_id_desc := heerid_to_desc(NEW.p_id); END IF; ELSIF TG_OP = 'UPDATE' THEN IF NEW.p_id IS DISTINCT FROM OLD.p_id THEN NEW.p_id_desc := heerid_to_desc(NEW.p_id); ELSIF NEW.p_id_desc IS NULL THEN NEW.p_id_desc := heerid_to_desc(NEW.p_id); END IF; END IF; RETURN NEW; END; $body$ LANGUAGE plpgsql; DROP TRIGGER IF EXISTS zzz_c_autofill_desc ON c; CREATE TRIGGER zzz_c_autofill_desc BEFORE INSERT OR UPDATE ON c FOR EACH ROW EXECUTE FUNCTION zzz_c_autofill_desc(); CALL heeranjid_bulk_backfill('parent', 'id', 'id_desc', 'heer', 10000) CALL heeranjid_bulk_backfill('c', 'p_id', 'p_id_desc', 'heer', 10000) SELECT count(*) FROM parent WHERE id_desc IS NULL SELECT count(*) FROM c WHERE p_id_desc IS NULL CREATE UNIQUE INDEX CONCURRENTLY idx_parent_id_desc ON parent (id_desc) CREATE INDEX CONCURRENTLY idx_c_p_id_desc ON c (p_id_desc) ALTER TABLE c ADD CONSTRAINT c_p_id_desc_fkey FOREIGN KEY (p_id_desc) REFERENCES parent(id_desc) NOT VALID ALTER TABLE c VALIDATE CONSTRAINT c_p_id_desc_fkey ALTER TABLE parent ADD CONSTRAINT parent_id_desc_nn CHECK (id_desc IS NOT NULL) NOT VALID; ALTER TABLE parent VALIDATE CONSTRAINT parent_id_desc_nn; ALTER TABLE parent ALTER COLUMN id_desc SET NOT NULL; ALTER TABLE parent DROP CONSTRAINT parent_id_desc_nn; ALTER TABLE c ADD CONSTRAINT c_p_id_desc_nn CHECK (p_id_desc IS NOT NULL) NOT VALID; ALTER TABLE c VALIDATE CONSTRAINT c_p_id_desc_nn; ALTER TABLE c ALTER COLUMN p_id_desc SET NOT NULL; ALTER TABLE c DROP CONSTRAINT c_p_id_desc_nn; ALTER TABLE c DROP CONSTRAINT c_p_id_fkey; ALTER TABLE parent DROP CONSTRAINT parent_pkey; ALTER TABLE parent ADD CONSTRAINT parent_pkey PRIMARY KEY USING INDEX idx_parent_id_desc; ALTER TABLE parent ALTER COLUMN id_desc SET DEFAULT heerid_next_desc(); ALTER TABLE parent ALTER COLUMN id DROP DEFAULT; ALTER TABLE parent DROP COLUMN id; DROP TRIGGER zzz_parent_autofill_desc ON parent; DROP FUNCTION zzz_parent_autofill_desc() CASCADE; ALTER TABLE parent RENAME COLUMN id_desc TO id; ALTER TABLE c DROP COLUMN p_id; DROP TRIGGER zzz_c_autofill_desc ON c; DROP FUNCTION zzz_c_autofill_desc() CASCADE; ALTER TABLE c DROP CONSTRAINT c_p_id_desc_fkey; ALTER TABLE c RENAME COLUMN p_id_desc TO p_id; ALTER TABLE c ADD CONSTRAINT c_p_id_fkey FOREIGN KEY (p_id) REFERENCES parent(id) ON DELETE RESTRICT;
