ALTER TABLE tbl ADD COLUMN id_desc bigint; CREATE OR REPLACE FUNCTION zzz_tbl_autofill_desc() RETURNS trigger AS $body$ BEGIN IF TG_OP = 'INSERT' THEN IF NEW.id_desc IS NULL THEN NEW.id_desc := heerid_to_asc(NEW.id); END IF; ELSIF TG_OP = 'UPDATE' THEN IF NEW.id IS DISTINCT FROM OLD.id THEN NEW.id_desc := heerid_to_asc(NEW.id); ELSIF NEW.id_desc IS NULL THEN NEW.id_desc := heerid_to_asc(NEW.id); END IF; END IF; RETURN NEW; END; $body$ LANGUAGE plpgsql; DROP TRIGGER IF EXISTS zzz_tbl_autofill_desc ON tbl; CREATE TRIGGER zzz_tbl_autofill_desc BEFORE INSERT OR UPDATE ON tbl FOR EACH ROW EXECUTE FUNCTION zzz_tbl_autofill_desc(); DO $$ DECLARE rows_done int; BEGIN LOOP SET LOCAL lock_timeout = '30s'; WITH batch AS ( SELECT ctid FROM tbl WHERE id_desc IS NULL AND id IS NOT NULL LIMIT 10000 FOR UPDATE SKIP LOCKED ) UPDATE tbl t SET id_desc = heerid_to_asc(t.id) FROM batch WHERE t.ctid = batch.ctid; GET DIAGNOSTICS rows_done = ROW_COUNT; COMMIT; EXIT WHEN rows_done = 0; END LOOP; LOOP SET LOCAL lock_timeout = '30s'; WITH batch AS ( SELECT ctid FROM tbl WHERE id_desc IS NULL AND id IS NOT NULL LIMIT 10000 FOR UPDATE ) UPDATE tbl t SET id_desc = heerid_to_asc(t.id) FROM batch WHERE t.ctid = batch.ctid; GET DIAGNOSTICS rows_done = ROW_COUNT; COMMIT; EXIT WHEN rows_done = 0; END LOOP; END; $$ SELECT count(*) FROM tbl WHERE id_desc IS NULL CREATE UNIQUE INDEX CONCURRENTLY idx_tbl_id_desc ON tbl (id_desc) ALTER TABLE tbl ADD CONSTRAINT tbl_id_desc_nn CHECK (id_desc IS NOT NULL) NOT VALID; ALTER TABLE tbl VALIDATE CONSTRAINT tbl_id_desc_nn; ALTER TABLE tbl ALTER COLUMN id_desc SET NOT NULL; ALTER TABLE tbl DROP CONSTRAINT tbl_id_desc_nn; ALTER TABLE tbl DROP CONSTRAINT tbl_pkey; ALTER TABLE tbl ADD CONSTRAINT tbl_pkey PRIMARY KEY USING INDEX idx_tbl_id_desc; ALTER TABLE tbl ALTER COLUMN id_desc SET DEFAULT heerid_next(); ALTER TABLE tbl ALTER COLUMN id DROP DEFAULT; ALTER TABLE tbl DROP COLUMN id; DROP TRIGGER zzz_tbl_autofill_desc ON tbl; DROP FUNCTION zzz_tbl_autofill_desc() CASCADE; ALTER TABLE tbl RENAME COLUMN id_desc TO id;
