pgpool handling of CTEs with UPDATE

Question

I'm running pgpool-II with load balancing enabled, such that it is sending read-only queries to both servers in my cluster.

However, there are some cases where I have CTE queries that contain UPDATE commands, but the underlying query is a SELECT, so pgpool is sending it to the slave and failing due to the read-only nature.

For example, this query finds matches for display, and marks them as viewed at the same time:

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

Any suggestions to get pgpool to recognize this as a write query, so should I separate out the write?

Answer 1

Explcitly open a read/write transaction.

BEGIN TRANSACTION READ WRITE;

to tell PgPool that the transaction should go to the master, or SET transaction_read_only = off, which is equivalent.

I thought PgPool supported comment hints to route statements to the master node, but didn't see references to that in a quick scan of the docs.

Your other option is to patch PgPool to make its query parser smarter about identifying writable CTEs like this.