cannot execute SELECT FOR NO KEY UPDATE in a read-only transaction

[kwongtn98]

Describe the bug
We have the following sql statement

SELECT mytable.id, mytable.other_id
FROM mytable
WHERE (mytable.status = %s
       OR (mytable.counter< (mytable.max_counter)
           AND mytable.next_attempt_at <= (STATEMENT_TIMESTAMP())
           AND mytable.status = %s))
ORDER BY mytable.next_attempt_at ASC NULLS FIRST, mytable.created ASC
LIMIT 10
FOR NO KEY
UPDATE OF mytable SKIP LOCKED

Our django application then throws

cannot execute SELECT FOR NO KEY UPDATE in a read-only transaction

From a high level view it seems like an error with the query parser.

[gcsabbagh]

Do you get a QueryParserError log?

There is a bug in the role selection logic, and it seems to default back to "auto" or "any" if query parser fails.

I opened an issue here: #884

Until it's fixed, in order to avoid this, your options are:

• Turn off query parser
• Or SET SERVER ROLE TO 'primary' in your transaction/session

If it's not a query parser error, then it's just that the sqlparser crate that pgcat uses just didnt parse your query correctly. You can submit improvements here: https://github.com/apache/datafusion-sqlparser-rs