Re: Question: Is it possible to get the new xlog position after query execution?

On Sat, Nov 6, 2021 at 7:51 AM Peter J. Holzer <hjp-pgsql(at)hjp(dot)at> 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(at)hjp(dot)at>
> 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(at)hjp(dot)at | -- Charles Stross, "Creative writing
> __/ | http://www.hjp.at/ | challenge!"
>