On Sat, Nov 6, 2021 at 7:51 AM Peter J. Holzer <hjp-pgsql@xxxxxx> wrote:
On 2021-11-01 00:36:16 -0400, Oleg Serov wrote:
> On Sun, Oct 31, 2021 at 4:29 PM Peter J. Holzer <hjp-pgsql@xxxxxx> wrote:
>
> On 2021-10-29 13:22:56 -0400, Oleg Serov wrote:
> > We are using a master/slave replication system where we perform
> > writes on master and use replication to offload reads.
> >
> > However, sometimes we have a replication lag of a few seconds
> > and as a result, after the update, the change is not yet
> > available on the replica.
> >
> > Is there a way to get XLOG position to which specific update
> > query will be written? That way we can check if our replica
> > caught up with changes and it is safe to read it from. Can it be
> > done using SQL functions? Can I get that information from query
> > protocol?
>
> I think I would prefer a more direct approach:
>
> If you know what you've written, can't you just check whether the
> replica has the new value(s)?
>
> The simplest answer: One thread on a single process on a server knows about it.
> Now another thread on another process/other server does not know about it.
So why would that other thread know the relevant XLOG position?
Yes, I guess I assumed this is a common pattern. We can propagate this information across a pub/sub service to all client connections with specific user ID. It makes much more sense to propagate a number rather than actual changes that were made. We want to keep a single source of truth.
> If not, an alternative could be a table which contains a simple counter
> or timestamp:
>
> begin;
> (lots of updates ...)
> commit;
> begin;
> update counter set c = c + 1 returning c; -- save this as c_current
> commit;
>
> Select c from counter on the replica in a loop until c >= c_current.
>
> Why invent something totally new when XLOG position is already used for
> replication by postgres? What are the benefits of it?
Because you had to ask. That shows that it isn't obvious. So your
application relies on some non-obvious (and possibly version-dependent)
implementation details of the database to ensure ordering. Using
something that makes sense from the application perspective (a timestamp
or a counter are just examples - your application may already have some
information which can use be used for that purpose) makes it more
obvious for the application programmer. (I'm generally a big fan of
end-to-end checks and testing what you are really interested in. If want
X but argue that X is true if Y is true and Y is true if Z is true, and
then go on to test for Z, that usually makes code hard to understand. It
is sometimes useful or even necessary (e.g. if X cannot be tested
directly), but it should IMHO be restricted to those cases.)
Postgres relies on this xlog position when it determines how far off is the replica (see pg_last_wal_replay_lsn). Using something that is guaranteed to be the source of truth (how far off is the replica) is better than using some indirect measurement (e.g. some value that we written to the DB).
How would you accomplish this otherwise?
hp
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@xxxxxx | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
