Обсуждение: Question: Is it possible to get the new xlog position after query execution?

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)?

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.

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

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@hjp.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?


>     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.)

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

> On Nov 7, 2021, at 19:42, Oleg Serov <oleg@slapdash.com> wrote:
> How would you accomplish this otherwise?

Synchronous commit.  It sounds like you are attempting to build the same kind of guarantees that synchronous commit
provides,only in the application by polling LSNs.  It might be best just to use synchronous commit, so that you know
thatonce the transaction is committed, the secondary has it. 

> On Nov 8, 2021, at 10:03, Oleg Serov <oleg@slapdash.com> wrote:
> That does not seem to be feasible for our application. Using synchronous commit affects performance and really makes
replicationnot really useful... What we want to achieve is to have a consistent DB state across all connections for
masterand replica per user. If other users see something outdated, is OK.   

Synchronous commit can be turned on and off per-user:

    ALTER ROLE <role> SET synchronous_commit = 'on';

That way, the users that require it have it, but other users do not.

> On Nov 8, 2021, at 11:47, Oleg Serov <oleg@slapdash.com> wrote:
> I'm sorry, when I mean users, I mean application end-user (e.g. john@doe.com), not the postgres role.

You can also set it as a session-level variable.  :-)

    SET synchronous_commit = 'on';

You could flip it on for the application-level users that require it, only when it is required.