ALTER TABLE tags ADD COLUMN id_desc bigint; CREATE OR REPLACE FUNCTION zzz_tags_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_tags_autofill_desc ON tags; CREATE TRIGGER zzz_tags_autofill_desc BEFORE INSERT OR UPDATE ON tags FOR EACH ROW EXECUTE FUNCTION zzz_tags_autofill_desc(); ALTER TABLE book_tags ADD COLUMN tag_id_desc bigint; CREATE OR REPLACE FUNCTION zzz_book_tags_autofill_desc() RETURNS trigger AS $body$ BEGIN IF TG_OP = 'INSERT' THEN IF NEW.tag_id_desc IS NULL THEN NEW.tag_id_desc := heerid_to_desc(NEW.tag_id); END IF; ELSIF TG_OP = 'UPDATE' THEN IF NEW.tag_id IS DISTINCT FROM OLD.tag_id THEN NEW.tag_id_desc := heerid_to_desc(NEW.tag_id); ELSIF NEW.tag_id_desc IS NULL THEN NEW.tag_id_desc := heerid_to_desc(NEW.tag_id); END IF; END IF; RETURN NEW; END; $body$ LANGUAGE plpgsql; DROP TRIGGER IF EXISTS zzz_book_tags_autofill_desc ON book_tags; CREATE TRIGGER zzz_book_tags_autofill_desc BEFORE INSERT OR UPDATE ON book_tags FOR EACH ROW EXECUTE FUNCTION zzz_book_tags_autofill_desc(); CALL heeranjid_bulk_backfill('tags', 'id', 'id_desc', 'heer', 10000) CALL heeranjid_bulk_backfill('book_tags', 'tag_id', 'tag_id_desc', 'heer', 10000) SELECT count(*) FROM tags WHERE id_desc IS NULL SELECT count(*) FROM book_tags WHERE (tag_id IS NULL) IS DISTINCT FROM (tag_id_desc IS NULL) OR (tag_id IS NOT NULL AND tag_id_desc <> heerid_to_desc(tag_id)) CREATE UNIQUE INDEX CONCURRENTLY idx_tags_id_desc ON tags (id_desc) CREATE INDEX CONCURRENTLY idx_book_tags_tag_id_desc ON book_tags (tag_id_desc) ALTER TABLE book_tags ADD CONSTRAINT book_tags_tag_id_desc_fkey FOREIGN KEY (tag_id_desc) REFERENCES tags(id_desc) NOT VALID ALTER TABLE book_tags VALIDATE CONSTRAINT book_tags_tag_id_desc_fkey ALTER TABLE tags ADD CONSTRAINT tags_id_desc_nn CHECK (id_desc IS NOT NULL) NOT VALID; ALTER TABLE tags VALIDATE CONSTRAINT tags_id_desc_nn; ALTER TABLE tags ALTER COLUMN id_desc SET NOT NULL; ALTER TABLE tags DROP CONSTRAINT tags_id_desc_nn; ALTER TABLE book_tags DROP CONSTRAINT book_tags_tag_id_fkey; ALTER TABLE tags DROP CONSTRAINT tags_pkey; ALTER TABLE tags ADD CONSTRAINT tags_pkey PRIMARY KEY USING INDEX idx_tags_id_desc; ALTER TABLE tags ALTER COLUMN id_desc SET DEFAULT heerid_next_desc(); ALTER TABLE tags ALTER COLUMN id DROP DEFAULT; ALTER TABLE tags DROP COLUMN id; DROP TRIGGER zzz_tags_autofill_desc ON tags; DROP FUNCTION zzz_tags_autofill_desc() CASCADE; ALTER TABLE tags RENAME COLUMN id_desc TO id; ALTER TABLE book_tags DROP COLUMN tag_id; DROP TRIGGER zzz_book_tags_autofill_desc ON book_tags; DROP FUNCTION zzz_book_tags_autofill_desc() CASCADE; ALTER TABLE book_tags DROP CONSTRAINT book_tags_tag_id_desc_fkey; ALTER TABLE book_tags RENAME COLUMN tag_id_desc TO tag_id; ALTER TABLE book_tags ADD CONSTRAINT book_tags_tag_id_fkey FOREIGN KEY (tag_id) REFERENCES tags(id); -- Join-table layout: OptionA (parent=tags, join_table=book_tags)
