postgresql - pgpool 使用 UPDATE 处理 CTE

Question

我在启用负载平衡的情况下运行 pgpool-II，以便它向我集群中的两个服务器发送只读查询。

但是，在某些情况下，我有包含 UPDATE 命令的 CTE 查询，但底层查询是一个 SELECT，因此 pgpool 将它发送到从服务器并由于只读性质而失败。

例如，此查询找到要显示的匹配项，并将它们同时标记为已查看:

WITH matches AS (
    WITH user_liked AS (
          SELECT liked_user_id FROM likes WHERE user_id='x' AND active IS TRUE
    )
    SELECT user_id, liked_user_id, liked_user_viewed, created_ts, matched_ts AS pointer
          FROM likes 
          WHERE 
          user_id IN(SELECT * FROM user_liked) 
          AND 
          active IS TRUE
          AND 
          liked_user_id='x' 
          ORDER BY matched_ts DESC
     ),
     update_liked_user_viewed AS (
          UPDATE likes
          SET liked_user_viewed=TRUE
          WHERE liked_user_id='x' AND user_id IN(SELECT user_id FROM matches)
     )
     SELECT * FROM matches

有什么建议可以让 pgpool 将其识别为写入查询，所以我应该将写入分开吗？

Answer 1

显式打开一个读/写事务。

BEGIN TRANSACTION READ WRITE;

告诉PgPool交易应该交给master，或者SET transaction_read_only = off，这是等价的。

我认为 PgPool 支持注释提示以将语句路由到主节点，但在快速浏览文档时没有看到对此的引用。

您的另一个选择是修补 PgPool，使其查询解析器更智能地识别像这样的可写 CTE。