Feedback-ID: i131946ab:Fastmail
Message-ID: <8b5b172f-0ae7-d644-8358-e2851dded43b@enterprisedb.com>
Date: Thu, 2 Mar 2023 11:33:01 +0100
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:102.0)
 Gecko/20100101 Thunderbird/102.8.0
Subject: Re: [HACKERS] make async slave to wait for lsn to be replayed
Content-Language: en-US
To: Kartyshov Ivan <i.kartyshov@postgrespro.ru>,
 pgsql-hackers@lists.postgresql.org
References: <eb12f9b03851bb2583adab5df9579b4b@postgrespro.ru>
From: Peter Eisentraut <peter.eisentraut@enterprisedb.com>
In-Reply-To: <eb12f9b03851bb2583adab5df9579b4b@postgrespro.ru>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/8b5b172f-0ae7-d644-8358-e2851dded43b%40enterprisedb.com>
Precedence: bulk

On 28.02.23 11:10, Kartyshov Ivan wrote:
> 3) Procedure style: Tom Lane and Kyotaro (wait_proc_v1.patch)
> https://www.postgresql.org/message-id/27171.1586439221%40sss.pgh.pa.us
> https://www.postgresql.org/message-id/20210121.173009.235021120161403875.horikyota.ntt%40gmail.com
> ==========
> advantages: no new words in grammar,like it made in 
> pg_last_wal_replay_lsn, no snapshots need
> disadvantages: a little harder to remember names
> SELECT pg_waitlsn(‘LSN’, timeout);
> SELECT pg_waitlsn_infinite(‘LSN’);
> SELECT pg_waitlsn_no_wait(‘LSN’);

Of the presented options, I prefer this one.  (Maybe with a "_" between 
"wait" and "lsn".)

But I wonder how a client is going to get the LSN.  How would all of 
this be used by a client?  I can think of a scenarios where you have an 
application that issues a bunch of SQL commands and you have some kind 
of pooler in the middle that redirects those commands to different 
hosts, and what you really want is to have it transparently behave as if 
it's just a single host.  Do we want to inject a bunch of "SELECT 
pg_get_lsn()", "SELECT pg_wait_lsn()" calls into that?

I'm tempted to think this could be a protocol-layer facility.  Every 
query automatically returns the current LSN, and every query can also 
send along an LSN to wait for, and the client library would just keep 
track of the LSN for (what it thinks of as) the connection.  So you get 
some automatic serialization without having to modify your client code.

That said, exposing this functionality using functions could be a valid 
step in that direction, so that you can at least build out the actual 
internals of the functionality and test it out.