使用pg_advisory_xact_lock的批处理作业致Tableau仪表板加载异常求替代方案

针对批处理合并分区时阻塞Tableau查询的问题，以下是几种可行的替代方案，核心思路是缩短锁持有时间或降低锁粒度，减少对用户查询的影响：

1. 使用SHARE UPDATE EXCLUSIVE表级锁（兼容查询的轻量锁） #

PostgreSQL的SHARE UPDATE EXCLUSIVE锁专为这类DDL操作设计——它允许正常的SELECT查询（Tableau核心操作）并发执行，同时能阻止其他DDL操作、分区修改或批量写入，保证合并操作的原子性。

关键是把分区合并的核心操作（比如ATTACH PARTITION）放在短事务中执行，避免长时间持有锁：

BEGIN;
-- 仅锁定需要合并的目标分区表，粒度精准
LOCK TABLE customer.target_table IN SHARE UPDATE EXCLUSIVE MODE;
-- 执行分区挂载操作，这是元数据级操作，速度极快
ALTER TABLE customer.target_table ATTACH PARTITION sme.target_table 
FOR VALUES FROM ('2024-05-01') TO ('2024-05-02');
COMMIT;

事务提交后锁立即释放，Tableau查询不会被长时间阻塞。

2. 分区交换（ALTER TABLE ... EXCHANGE PARTITION） #

如果SME子模式下的表结构与Customer父模式的分区表完全匹配，用分区交换替代挂载是最优解——这个操作仅修改PostgreSQL的系统目录，几乎瞬间完成，锁持有时间可以忽略不计。

示例代码：

BEGIN;
-- 原子交换分区，全程锁持有时间仅毫秒级
ALTER TABLE customer.target_table EXCHANGE PARTITION p_daily_load 
WITH TABLE sme.target_table;
COMMIT;

交换完成后，原SME表的数据直接成为Customer分区表的一部分，Tableau查询能立即读到新数据，且全程不会被阻塞。

3. 细粒度的事务级 advisory 锁 #

如果必须保留advisory锁的使用方式，把原来的全局锁改成针对具体表的细粒度锁，避免因为一个全局锁阻塞所有表的查询。

可以用目标表的OID作为锁的唯一标识：

-- 仅锁定当前需要合并的表，不影响其他表的查询
SELECT pg_advisory_xact_lock(('customer.target_table')::regclass::oid);
-- 执行分区合并操作

这样只有访问该特定表的Tableau查询会受到锁的限制，其他表的查询完全不受影响。

4. 基于视图的平滑切换 #

如果Tableau是通过视图访问数据，可通过修改视图定义实现无感知的数据切换：

1. 先在SME模式下完成数据加载和预处理
2. 在短事务中修改视图，将SME表的数据加入视图（或替换为合并后的分区表）
3. 事务提交后，Tableau查询自动读取新数据

示例代码：

BEGIN;
-- 锁定视图防止并发修改
SELECT 1 FROM pg_class WHERE relname = 'customer_dashboard_view' AND relnamespace = 'customer'::regnamespace FOR UPDATE;
-- 更新视图定义，包含新加载的SME数据
CREATE OR REPLACE VIEW customer.customer_dashboard_view AS
SELECT * FROM customer.target_table 
UNION ALL 
SELECT * FROM sme.target_table;
COMMIT;

这种方式完全避免了对底层表的锁阻塞，用户查询无感知。

内容的提问来源于stack exchange，提问作者Rajasekar