Re: synchronous_commit = apply

On 1 September 2015 at 20:25, Thomas Munro
<thomas(dot)munro(at)enterprisedb(dot)com> wrote:
> Hi
>
> Do you think it's reasonable to want to COMMIT a particular transaction on a
> master node, and then immediately run a read-only query on a hot standby
> node that is guaranteed to see that transaction?
>

well, that is important to make load balancing completely safe (not
returning old data when is important to get the latest).
Having said that, i have never seen a case where the apply lag
postgres has really matters or where the cause of the apply lag (I/O)
doesn't get worst if we try to apply immediatly.

Other solutions use a cache on top to apply in-memory at the cost of
getting inconsistent in a failure.

> A friend of mine who works with a different RDBMS technology that can do
> that asked me how to achieve this with Postgres, and I suggested waiting for
> the standby's pg_last_xlog_replay_location() to be >= the master's
> pg_current_xlog_location() after COMMIT, which might involve some looping
> and sleeping.
>
> As a quick weekend learning exercise/hack I recently went looking into how
> we could support $SUBJECT. I discovered we already report the apply
> progress back to the master, and the synchronous waiting facility seemed to
> be all ready to support this. In fact it seemed a little too easy so
> something tells me it must be wrong! But anyway, please see the attached
> toy POC patch which does that.
>

i haven't seen the patch, but probably is as easy as you see it...
IIRC, Simon proposed a patch for this a few years ago and this was
actually contempleted from the beggining in the design of SR.

I guess there were good reasons the patch didn't get applied, i found
this thread and in this one Simon suggest is not the first time he
submitted that option so it should be other threads too:
http://www.postgresql.org/message-id/AANLkTinxoYmWoWBsJxmnpJHJh_YAN9vFmnmhNJDMev4M@mail.gmail.com

--
Jaime Casanova www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación