ALTER TABLE a ADD COLUMN id_desc bigint; CREATE OR REPLACE FUNCTION zzz_a_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_a_autofill_desc ON a; CREATE TRIGGER zzz_a_autofill_desc BEFORE INSERT OR UPDATE ON a FOR EACH ROW EXECUTE FUNCTION zzz_a_autofill_desc(); ALTER TABLE b ADD COLUMN a_id_desc bigint; CREATE OR REPLACE FUNCTION zzz_b_autofill_desc() RETURNS trigger AS $body$ BEGIN IF TG_OP = 'INSERT' THEN IF NEW.a_id_desc IS NULL THEN NEW.a_id_desc := heerid_to_desc(NEW.a_id); END IF; ELSIF TG_OP = 'UPDATE' THEN IF NEW.a_id IS DISTINCT FROM OLD.a_id THEN NEW.a_id_desc := heerid_to_desc(NEW.a_id); ELSIF NEW.a_id_desc IS NULL THEN NEW.a_id_desc := heerid_to_desc(NEW.a_id); END IF; END IF; RETURN NEW; END; $body$ LANGUAGE plpgsql; DROP TRIGGER IF EXISTS zzz_b_autofill_desc ON b; CREATE TRIGGER zzz_b_autofill_desc BEFORE INSERT OR UPDATE ON b FOR EACH ROW EXECUTE FUNCTION zzz_b_autofill_desc(); CALL heeranjid_bulk_backfill('a', 'id', 'id_desc', 'heer', 10000) CALL heeranjid_bulk_backfill('b', 'a_id', 'a_id_desc', 'heer', 10000) SELECT count(*) FROM a WHERE id_desc IS NULL SELECT count(*) FROM b WHERE (a_id IS NULL) IS DISTINCT FROM (a_id_desc IS NULL) OR (a_id IS NOT NULL AND a_id_desc <> heerid_to_desc(a_id)) CREATE UNIQUE INDEX CONCURRENTLY idx_a_id_desc ON a (id_desc) CREATE INDEX CONCURRENTLY idx_b_a_id_desc ON b (a_id_desc) ALTER TABLE b ADD CONSTRAINT b_a_id_desc_fkey FOREIGN KEY (a_id_desc) REFERENCES a(id_desc) DEFERRABLE INITIALLY DEFERRED NOT VALID ALTER TABLE b VALIDATE CONSTRAINT b_a_id_desc_fkey ALTER TABLE a ADD CONSTRAINT a_id_desc_nn CHECK (id_desc IS NOT NULL) NOT VALID; ALTER TABLE a VALIDATE CONSTRAINT a_id_desc_nn; ALTER TABLE a ALTER COLUMN id_desc SET NOT NULL; ALTER TABLE a DROP CONSTRAINT a_id_desc_nn; SET CONSTRAINTS ALL DEFERRED; ALTER TABLE b DROP CONSTRAINT b_a_id_fkey; ALTER TABLE a DROP CONSTRAINT a_pkey; ALTER TABLE a ADD CONSTRAINT a_pkey PRIMARY KEY USING INDEX idx_a_id_desc; ALTER TABLE a ALTER COLUMN id_desc SET DEFAULT heerid_next_desc(); ALTER TABLE a ALTER COLUMN id DROP DEFAULT; ALTER TABLE a DROP COLUMN id; DROP TRIGGER zzz_a_autofill_desc ON a; DROP FUNCTION zzz_a_autofill_desc() CASCADE; ALTER TABLE a RENAME COLUMN id_desc TO id; ALTER TABLE b DROP COLUMN a_id; DROP TRIGGER zzz_b_autofill_desc ON b; DROP FUNCTION zzz_b_autofill_desc() CASCADE; ALTER TABLE b DROP CONSTRAINT b_a_id_desc_fkey; ALTER TABLE b RENAME COLUMN a_id_desc TO a_id; ALTER TABLE b ADD CONSTRAINT b_a_id_fkey FOREIGN KEY (a_id) REFERENCES a(id) ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED;
