Re: Proposal: "Causal reads" mode for load balancing reads without stale data

On 16 November 2015 at 11:01, Craig Ringer <craig(at)2ndquadrant(dot)com> wrote:

> On 16 November 2015 at 18:44, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>
>>
>> The pooler knows which statements are reads and writes
>>
>
> I think that's an iffy assumption.
>

It's not an assumption, its a requirement. If it can't do this in some
manner then you can't use a load balancing pooler.

Randomly submitting things works as well, since it leads to a write error
when you try to write data on a read only server, so you do then learn
whether it is a read or a write. Once you know its a write, you submit to
master. But you still need to be careful of other effects, so that isn't
recommended.

It's one we tend to make because otherwise read/write pooling won't work,
> but in PostgreSQL there's really no way to know when calling a function.
>
> What does
>
> SELECT get_user_stats()
>
> do? The pooler has _no_ _idea_ unless manually configured with knowledge
> about particular user defined functions.
>
> In the absence of such knowledge it can:
>
> - send the work to a replica and report the ERROR to the user if it fails
> due to an attempted write;
> - send the work to a replica, capture an ERROR due to attempted write, and
> retry on the master;
> - send everything it's not sure about to the master
>
> If a pooler had insight into the catalogs and if we had readonly /
> readwrite attributes on functions, it could be smarter.
>

pgpool supports white/black function listing for exactly this reason.

--
Simon Riggs http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/>
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services