Postgrest error when connected to read replica database

[paulmin55]

Hello,

I'm trying to run the lastest Postgrest v0.3.1.1 on Debian GNU/Linux 8 to use a Postgres 9.4.5 read replica db for read only queries but I'm getting this message when curling postgrest at:

Steps to reproduce:

   $curl http://localhost:3000 
{"hint":null,"details":null,"code":"0A000","message":"cannot set transaction read-write mode during recovery"}.

I have no trouble using postgrest against the master db running Postgres 9.4.5, however I was hoping I can use Postgrest with the read replica db to perform read-only operations.

I've chatted with @ruslantalpa on glitter and he suspects it might be an issue with how postgrest issues the 'begin transaction' statement and requested that I put the postgres query logs as shown below. I appreciate any advise on this. Thank you!

Postgres read replica db query log when running curl http://localhost:3000:

[28104]:LOG: execute 6: BEGIN ISOLATION LEVEL READ COMMITTED READ WRITE
2016-04-04 15:21:11 UTC:xxx.xxx.xxx.xxx(42946):read_only@database:[28104]:ERROR: cannot set transaction read-write mode during recovery

Successful postgres query logs when running curl http://localhost:3000: on master db:

2016-04-04 15:04:31 UTC:xxx.xxx.xxx.xxx(36131):read_only@database:[21057]:DETAIL: parameters: $1 = 'public'
2016-04-04 15:04:31 UTC:xxx.xxx.xxx.xxx(36131):read_only@database:[21057]:LOG: execute 8: commit
2016-04-04 15:04:31 UTC:xxx.xxx.xxx.xxx(36131):read_only@database:[21057]:LOG: execute 6: BEGIN ISOLATION LEVEL READ COMMITTED READ WRITE
2016-04-04 15:04:31 UTC:xxx.xxx.xxx.xxx(36131):read_only@database:[21057]:LOG: statement: set local role ‘read_only’;
2016-04-04 15:04:31 UTC:xxx.xxx.xxx.xxx(36131):read_only@database:[21057]:LOG: execute 7: 
select
n.nspname as table_schema,
relname as table_name,
c.relkind = 'r' or (c.relkind IN ('v', 'f')) and (pg_relation_is_updatable(c.oid::regclass, false) & 8) = 8
or (exists (
select 1
from pg_trigger
where pg_trigger.tgrelid = c.oid and (pg_trigger.tgtype::integer & 69) = 69)
) as insertable
from
pg_class c
join pg_namespace n on n.oid = c.relnamespace
where
c.relkind in ('v', 'r', 'm')
and n.nspname = $1
and (
pg_has_role(c.relowner, 'USAGE'::text)
or has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text)
or has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES'::text)
)
order by relname 
2016-04-04 15:04:31 UTC:xxx.xxx.xxx.xxx(36131):read_only@database:[21057]:DETAIL: parameters: $1 = 'public'
2016-04-04 15:04:31 UTC:xxx.xxx.xxx.xxx(36131):read_only@database:[21057]:LOG: execute 8: commit

[ruslantalpa]

@paulmin55 are you comfortable doing a build from source?

[ruslantalpa]

If you can do the following test and confirm it works then i can push a PR for you that will allow the functionality you need.
Please do this:
Change this line https://github.com/begriffs/postgrest/blob/master/src/PostgREST/App.hs#L77 to

(HT.run handleReq HT.ReadCommitted HT.Read)

then follow the link in my previous comment to build from source and run the resulting binary agains your read replica (any insert updates with this binary will fail on the master).

[paulmin55]

@ruslantalpa I made the change to https://github.com/begriffs/postgrest/blob/master/src/PostgREST/App.hs#L77 with

(HT.run handleReq HT.ReadCommitted HT.Read)

and built from source using your link above. I can confirm that Postgrest is able to read from the read-replica database with this change OK. Thanks for your work on this, I look forward to this functionality change!

[ruslantalpa]

This can be closed, it was fixed