Subject: Re: [HACKERS] make async slave to wait for lsn to be replayed
To: Alexey Kondratov <a.kondratov@postgrespro.ru>,
 Tom Lane <tgl@sss.pgh.pa.us>
Cc: Kyotaro Horiguchi <horikyota.ntt@gmail.com>, a.akenteva@postgrespro.ru,
 a.korotkov@postgrespro.ru, i.kartyshov@postgrespro.ru,
 amit.kapila16@gmail.com, pgsql-hackers@lists.postgresql.org
References: <20200408.100945.375702947246835193.horikyota.ntt@gmail.com>
 <d3ff2e363af60b345f82396992595a03@postgrespro.ru>
 <13716.1586378146@sss.pgh.pa.us>
 <20200409.161107.580344062273684114.horikyota.ntt@gmail.com>
 <880834ee-4b18-6d5d-9dae-6520c5eb2bef@oss.nttdata.com>
 <27171.1586439221@sss.pgh.pa.us>
 <a8bff0350a27e0a87a6eaf0905d6737f@postgrespro.ru>
From: Fujii Masao <masao.fujii@oss.nttdata.com>
Message-ID: <759206c1-265f-97da-d135-fcecbced646b@oss.nttdata.com>
Date: Fri, 10 Apr 2020 11:25:02 +0900
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.14; rv:68.0)
 Gecko/20100101 Thunderbird/68.6.0
MIME-Version: 1.0
In-Reply-To: <a8bff0350a27e0a87a6eaf0905d6737f@postgrespro.ru>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 7bit
Precedence: bulk


On 2020/04/10 3:16, Alexey Kondratov wrote:
> On 2020-04-09 16:33, Tom Lane wrote:
>> Fujii Masao <masao.fujii@oss.nttdata.com> writes:
>>> On 2020/04/09 16:11, Kyotaro Horiguchi wrote:
>>>> At Wed, 08 Apr 2020 16:35:46 -0400, Tom Lane <tgl@sss.pgh.pa.us> wrote in
>>>>> Why is this getting grafted onto BEGIN/START TRANSACTION in the
>>>>> first place?
>>
>>>> The rationale for not being a fmgr function is stated in the following
>>>> comments. [...]
>>
>>> This issue happens because the function is executed after BEGIN? If yes,
>>> what about executing the function (i.e., as separate transaction) before BEGIN?
>>> If so, the snapshot taken in the function doesn't affect the subsequent
>>> transaction whatever its isolation level is.
>>
>> I wonder whether making it a procedure, rather than a plain function,
>> would help any.
>>
> 
> Just another idea in case if one will still decide to go with a separate statement + BEGIN integration instead of a function. We could use parenthesized options list here. This is already implemented for VACUUM, REINDEX, etc. There was an idea to allow CONCURRENTLY in REINDEX there [1] and recently this was proposed again for new options [2], since it is much more extensible from the grammar perspective.
> 
> That way, the whole feature may look like:
> 
> WAIT (LSN '16/B374D848', TIMEOUT 100);
> 
> and/or
> 
> BEGIN
> WAIT (LSN '16/B374D848', WHATEVER_OPTION_YOU_WANT);
> ...
> COMMIT;
> 
> It requires only one reserved keyword 'WAIT'. The advantage of this approach is that it can be extended to support xid, timestamp, csn or anything else, that may be invented in the future, without affecting the grammar.
> 
> What do you think?
> 
> Personally, I find this syntax to be more convenient and human-readable compared with function call:
> 
> SELECT pg_wait_for_lsn('16/B374D848');
> BEGIN;

I can imagine that some users want to specify the LSN to wait for,
from the result of another query, for example,
SELECT pg_wait_for_lsn(lsn) FROM xxx. If this is valid use case,
isn't the function better?

Regards,

-- 
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION