Fsync (flush) all inserted WAL records

Hi,

I use async commits. At some moment, I would like to make sure that all inserted WAL records are fsync-ed. I can use XLogFlush function but I have some doubts which LSN to specify. There is a number of functions which return write or insert LSNs but they are not applicable.

I can't use GetXLogInsertRecPtr() because it returns a real insert LSN, not the end LSN of the last record. XLogFlush may fail with such LSN because the specified LSN may be "in the future" if the WAL record ends up to the page boundary (the real insert LSN is summed up with page header size).

I can't use GetXLogWriteRecPtr() because it seems to be bounded to page boundaries. Some inserted WAL records may not be fsync-ed. Some other functions seems not applicable as well.

The first idea is to use GetLastImportantRecPtr() but this function returns the start LSN of the last important WAL record. I would use XLogFlush(GetLastImportantRecPtr() + 1) but I'm not sure that this way is conventional.

Another idea is to create a new function like GetXLogInsertRecPtr() which calls XLogBytePosToEndRecPtr() instead of XLogBytePosToRecPtr() inside it.

Could you please advice which way to go?

Does pg_current_wal_flush_lsn() [1]https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-RECOVERY-CONTROL return what you need?

[1]: https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-RECOVERY-CONTROL

--
Best regards,
Aleksander Alekseev

Hi,

Could you please advice which way to go?

Does pg_current_wal_flush_lsn() [1] return what you need?

[1]: https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-RECOVERY-CONTROL

If not, take a look at its implementation and functions around,
GetInsertRecPtr() and others. I believe you will find all you need for
the task.

--
Best regards,
Aleksander Alekseev

Hi Aleksander,

On Wednesday, August 07, 2024 12:19 MSK, Aleksander Alekseev <aleksander@timescale.com> wrote:
 > Does pg_current_wal_flush_lsn() [1] return what you need?

[1]: https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-RECOVERY-CONTROL

If not, take a look at its implementation and functions around,
GetInsertRecPtr() and others. I believe you will find all you need for
the task.Thank you for the response. I need the LSN of the last inserted by not flushed WAL record. The function pg_current_wal_flush_lsn() doesn't help. It returns the current flush position. GetInsertRecPtr() doesn't help as well because it returns XLogCtl->LogwrtRqst.Write which is updated when the record crosses page boundary. I looked at the code and haven't found any suitable function except of GetLastImportantRecPtr() but it returns start LSN of the last inserted important record (but I need end lsn).

I would propose a new function to fulfill my requirements like this (see below) but I prefer not to create new functions unreasonably:

XLogRecPtr
GetXLogLastInsertEndRecPtr(void)
{
    XLogCtlInsert *Insert = &XLogCtl->Insert;
    uint64 current_bytepos;
    SpinLockAcquire(&Insert->insertpos_lck);
    current_bytepos = Insert->CurrBytePos;
    SpinLockRelease(&Insert->insertpos_lck);
    return XLogBytePosToEndRecPtr(current_bytepos);
}
​​​​​
This function differs from the existing GetXLogInsertRecPtr() by calling XLogBytePosToEndRecPtr instead of XLogBytePosToRecPtr. 

With best regards,
Vitaly

Hi Vitaly,

I would propose a new function to fulfill my requirements like this (see below) but I prefer not to create new functions unreasonably:

XLogRecPtr
GetXLogLastInsertEndRecPtr(void)
{
XLogCtlInsert *Insert = &XLogCtl->Insert;
uint64 current_bytepos;
SpinLockAcquire(&Insert->insertpos_lck);
current_bytepos = Insert->CurrBytePos;
SpinLockRelease(&Insert->insertpos_lck);
return XLogBytePosToEndRecPtr(current_bytepos);
}

This function differs from the existing GetXLogInsertRecPtr() by calling XLogBytePosToEndRecPtr instead of XLogBytePosToRecPtr.

Perhaps you could give more context on the use cases for this
function? The value of it is not quite clear. What people typically
need is making sure if a given LSN was fsync'ed and/or replicated
and/or applied on a replica. Your case(s) however is different and I
don't fully understand it.

In any case you will need to implement an SQL-wrapper in order to make
the function available to DBAs, cover it with tests and provide
documentation.

--
Best regards,
Aleksander Alekseev

On Wednesday, August 07, 2024 16:55 MSK, Aleksander Alekseev <aleksander@timescale.com> wrote:
 
Perhaps you could give more context on the use cases for this
function? The value of it is not quite clear. What people typically
need is making sure if a given LSN was fsync'ed and/or replicated
and/or applied on a replica. Your case(s) however is different and I
don't fully understand it.
I use asynchronous commit (without XLogFlush/fsync at commit). At some moment I would like to XLogFlush (fsync) all already asynchronously committed transactions (inserted but not flushed/fsynced yet WAL records). Assume, that there is no any active transactions at this moment, no any potential race conditions. My problem is to find a proper LSN which I can use as a parameter for XLogFlush. The problem is that I can't use GetXLogInsertRecPtr() because it may be "in the future" due to some reasons (added page header size). XLogFlush will fail in this case.
In any case you will need to implement an SQL-wrapper in order to make
the function available to DBAs, cover it with tests and provide
documentation.Well, I would like to use such function in C language code, in some solution, not as a function to be used by users. 

With best regards,
Vitaly
​​​
 Hi Vitaly,

I would propose a new function to fulfill my requirements like this (see below) but I prefer not to create new functions unreasonably:

XLogRecPtr
GetXLogLastInsertEndRecPtr(void)
{
XLogCtlInsert *Insert = &XLogCtl->Insert;
uint64 current_bytepos;
SpinLockAcquire(&Insert->insertpos_lck);
current_bytepos = Insert->CurrBytePos;
SpinLockRelease(&Insert->insertpos_lck);
return XLogBytePosToEndRecPtr(current_bytepos);
}

This function differs from the existing GetXLogInsertRecPtr() by calling XLogBytePosToEndRecPtr instead of XLogBytePosToRecPtr.

​​​​
In any case you will need to implement an SQL-wrapper in order to make
the function available to DBAs, cover it with tests and provide
documentation.

--
Best regards,
Aleksander Alekseev

Hi,

I use asynchronous commit (without XLogFlush/fsync at commit). At some moment I would like to XLogFlush (fsync) all already asynchronously committed transactions (inserted but not flushed/fsynced yet WAL records). Assume, that there is no any active transactions at this moment, no any potential race conditions. My problem is to find a proper LSN which I can use as a parameter for XLogFlush.

How is it different from `CHECKPOINT;` ?

Well, I would like to use such function in C language code, in some solution, not as a function to be used by users.

Assuming the function has value, as you claim, I see no reason not to
expose it similarly to pg_current_wal_*(). On top of that you will
have to test-cover it anyway. The easiest way to do it will be to have
an SQL-wrapper.

--
Best regards,
Aleksander Alekseev

On Wed, Aug 07, 2024 at 06:00:45PM +0300, Aleksander Alekseev wrote:

Assuming the function has value, as you claim, I see no reason not to
expose it similarly to pg_current_wal_*(). On top of that you will
have to test-cover it anyway. The easiest way to do it will be to have
an SQL-wrapper.

I cannot be absolutely without seeing a patch, but adding SQL
functions in this area is usually very useful for monitoring purposes
of external solutions.
--
Michael

Dear All,

I would propose a new function like GetXLogInsertRecPtr(), but with some modifications (please, see the attached patch). The result LSN can be passed to XLogFLush() safely. I believe, it will not raise an error in any case. XLogFlush(GetXLogLastInsertEndRecPtr()) will flush (fsync) all already inserted records at the moment. It is what I would like to get.

I'm not sure, we need a SQL function counterpart for this new C function, but it is not a big deal to implement.

With best regards,
Vitaly

On Monday, August 19, 2024 09:35 MSK, Michael Paquier <michael@paquier.xyz> wrote:
 On Wed, Aug 07, 2024 at 06:00:45PM +0300, Aleksander Alekseev wrote:

Assuming the function has value, as you claim, I see no reason not to
expose it similarly to pg_current_wal_*(). On top of that you will
have to test-cover it anyway. The easiest way to do it will be to have
an SQL-wrapper.

I cannot be absolutely without seeing a patch, but adding SQL
functions in this area is usually very useful for monitoring purposes
of external solutions.
--
Michael