ALTER TABLE nodes ADD COLUMN id_desc bigint; ALTER TABLE nodes ADD COLUMN parent_id_desc bigint; CREATE OR REPLACE FUNCTION zzz_nodes_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; IF NEW.parent_id_desc IS NULL THEN NEW.parent_id_desc := heerid_to_desc(NEW.parent_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; IF NEW.parent_id IS DISTINCT FROM OLD.parent_id THEN NEW.parent_id_desc := heerid_to_desc(NEW.parent_id); ELSIF NEW.parent_id_desc IS NULL THEN NEW.parent_id_desc := heerid_to_desc(NEW.parent_id); END IF; END IF; RETURN NEW; END; $body$ LANGUAGE plpgsql; DROP TRIGGER IF EXISTS zzz_nodes_autofill_desc ON nodes; CREATE TRIGGER zzz_nodes_autofill_desc BEFORE INSERT OR UPDATE ON nodes FOR EACH ROW EXECUTE FUNCTION zzz_nodes_autofill_desc(); CALL heeranjid_bulk_backfill('nodes', 'id', 'id_desc', 'heer', 10000) CALL heeranjid_bulk_backfill('nodes', 'parent_id', 'parent_id_desc', 'heer', 10000) SELECT count(*) FROM nodes WHERE id_desc IS NULL SELECT count(*) FROM nodes WHERE (parent_id IS NULL) IS DISTINCT FROM (parent_id_desc IS NULL) OR (parent_id IS NOT NULL AND parent_id_desc <> heerid_to_desc(parent_id)) CREATE UNIQUE INDEX CONCURRENTLY idx_nodes_id_desc ON nodes (id_desc) CREATE INDEX CONCURRENTLY idx_nodes_parent_id_desc ON nodes (parent_id_desc) ALTER TABLE nodes ADD CONSTRAINT nodes_parent_id_desc_fkey FOREIGN KEY (parent_id_desc) REFERENCES nodes(id_desc) NOT VALID ALTER TABLE nodes VALIDATE CONSTRAINT nodes_parent_id_desc_fkey ALTER TABLE nodes ADD CONSTRAINT nodes_id_desc_nn CHECK (id_desc IS NOT NULL) NOT VALID; ALTER TABLE nodes VALIDATE CONSTRAINT nodes_id_desc_nn; ALTER TABLE nodes ALTER COLUMN id_desc SET NOT NULL; ALTER TABLE nodes DROP CONSTRAINT nodes_id_desc_nn; ALTER TABLE nodes DROP CONSTRAINT nodes_parent_id_fkey; ALTER TABLE nodes DROP CONSTRAINT nodes_pkey; ALTER TABLE nodes ADD CONSTRAINT nodes_pkey PRIMARY KEY USING INDEX idx_nodes_id_desc; ALTER TABLE nodes ALTER COLUMN id_desc SET DEFAULT heerid_next_desc(); ALTER TABLE nodes ALTER COLUMN id DROP DEFAULT; ALTER TABLE nodes DROP COLUMN id; ALTER TABLE nodes DROP COLUMN parent_id; DROP TRIGGER zzz_nodes_autofill_desc ON nodes; DROP FUNCTION zzz_nodes_autofill_desc() CASCADE; ALTER TABLE nodes RENAME COLUMN id_desc TO id; ALTER TABLE nodes DROP CONSTRAINT nodes_parent_id_desc_fkey; ALTER TABLE nodes RENAME COLUMN parent_id_desc TO parent_id; ALTER TABLE nodes ADD CONSTRAINT nodes_parent_id_fkey FOREIGN KEY (parent_id) REFERENCES nodes(id);
