pg_fact_loader

pig build pkg pg_fact_loader         # 构建 DEB 包

pig repo add pgdg -u          # 添加 PGDG 仓库并更新缓存

CREATE EXTENSION pg_fact_loader;

CREATE EXTENSION pg_fact_loader;

CREATE EXTENSION pglogical;
CREATE EXTENSION pglogical_ticker;
CREATE EXTENSION pg_fact_loader;

-- 注册事实表
INSERT INTO fact_loader.fact_tables (fact_table_relid, fact_table_agg_proid, ...)
VALUES ('public.customers_fact'::regclass, 'customers_fact_merge'::regproc, ...);

-- 注册队列（审计）表
INSERT INTO fact_loader.queue_tables (queue_table_relid, queue_of_base_table_relid, ...)
VALUES ('audit.customers_audit'::regclass, 'public.customers'::regclass, ...);

-- 将队列表与事实表通过合并函数关联
INSERT INTO fact_loader.queue_table_deps
    (fact_table_id, queue_table_id, insert_merge_proid, update_merge_proid, delete_merge_proid)
VALUES (1, 1, 'customers_fact_merge'::regproc, 'customers_fact_merge'::regproc, 'customers_fact_merge'::regproc);

-- 定义如何从队列条目中获取键
INSERT INTO fact_loader.key_retrieval_sequences
    (queue_table_dep_id, return_columns, is_fact_key)
VALUES (1, '{customer_id}', true);

-- 处理待处理的变更
SELECT fact_loader.worker();

-- 定期调度此操作（例如通过 pg_cron 每几秒运行一次）

-- 对每条现有记录运行合并函数
SELECT customers_fact_merge(customer_id) FROM customers;

SELECT fact_loader.add_batch_id_fields();