Thread: Fsync (flush) all inserted WAL records

Hi Hackers,

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?

With best regards,
Vitaly

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
XLogFlushfunction but I have some doubts which LSN to specify. There is a number of functions which return write or
insertLSNs 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
mayfail with such LSN because the specified LSN may be "in the future" if the WAL record ends up to the page boundary
(thereal 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
befsync-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] 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
functionsunreasonably:
 
>
> 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
alreadyasynchronously committed transactions (inserted but not flushed/fsynced yet WAL records). Assume, that there is
noany active transactions at this moment, no any potential race conditions. My problem is to find a proper LSN which I
canuse 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