Horton

Thread

  1. Fsync (flush) all inserted WAL records

    Vitaly Davydov <v.davydov@postgrespro.ru> — 2024-08-06T06:41:12Z

    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

  2. Re: Fsync (flush) all inserted WAL records

    Aleksander Alekseev <aleksander@timescale.com> — 2024-08-07T09:15:50Z

    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] return what you need?

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

-- 
Best regards,
Aleksander Alekseev

  3. Re: Fsync (flush) all inserted WAL records

    Aleksander Alekseev <aleksander@timescale.com> — 2024-08-07T09:19:28Z

    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

  4. Re: Fsync (flush) all inserted WAL records

    Vitaly Davydov <v.davydov@postgrespro.ru> — 2024-08-07T13:42:01Z

    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

  5. Re: Fsync (flush) all inserted WAL records

    Aleksander Alekseev <aleksander@timescale.com> — 2024-08-07T13:55:36Z

    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

  6. Re: Fsync (flush) all inserted WAL records

    Vitaly Davydov <v.davydov@postgrespro.ru> — 2024-08-07T14:37:39Z

    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

 

 

  7. Re: Fsync (flush) all inserted WAL records

    Aleksander Alekseev <aleksander@timescale.com> — 2024-08-07T15:00:45Z

    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

  8. Re: Fsync (flush) all inserted WAL records

    Michael Paquier <michael@paquier.xyz> — 2024-08-19T06:35:45Z

    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

  9. Re: Fsync (flush) all inserted WAL records

    Vitaly Davydov <v.davydov@postgrespro.ru> — 2024-08-20T15:18:45Z

    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