[pgpool-general: 274] Re: ERROR: cannot execute SELECT FOR UPDATE in a read-only transaction

[Toshihiro Kitagawa]

On Thu, 08 Mar 2012 16:09:26 +0100
Martin Kotys <martin at kotys.info> wrote:

> Hey,
> 
> We've installed pgPoolII on our servers and would like to switch over 
> our application to pgpool. When we did it, we got the following error:
> 
> ERROR: DBD::Pg::db selectrow_array failed: ERROR:  cannot execute SELECT 
> FOR UPDATE in a read-only transaction
> CONTEXT:  SQL statement "SELECT id::varchar || '-' || actorderid::varchar
>                                    FROM shop WHERE id = $1 FOR UPDATE"
> PL/pgSQL function "nextorderid" line 5 at SQL statement [for Statement 
> "SELECT nextorderid(2)"]
> 
> Nextorderid is a stored procedure which returns an order ID. Strange 
> thing is that it sometimes work and sometimes not.
> 
> I assume that the problem is that if the query is sent to master server, 
> all is OK, but if the query is sent to slave it fails.
> The same will probably happen with select nextval(), begin end, and so on.
> 
> Have you experienced similar issue by any chance? Don't you know how to 
> solve that issue?

I think that it is solved by adding nextorderid to black_function_list.
Sequence functions have been already added to it.

-- 
Toshihiro Kitagawa
SRA OSS, Inc. Japan