proposal: get oldest LSN - function

On 2016-02-26 18:45:14 +0300, Kartyshov Ivan wrote:

Function pg_oldest_xlog_location gets us the oldest LSN (Log Sequence
Number) in xlog.

It is useful additional tool for DBA (we can get replicationSlotMinLSN, so
why not in master), it can show us, if xlog replication or wal-sender is
working properly or indicate if replication on startup can get up to date
with master, or after long turnoff must be recovered from archive.

How does it help with any of that?

- Andres

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 27.02.2016 03:07, Andres Freund wrote

How does it help with any of that?

Hi, thank you for fast answer.
Maybe i wasn't too accurate in terms, because I newbie, but:
We can get information about xlog, using big amout of support function
(pg_current_xlog_location(), pg_current_xlog_insert_location(),
pg_xlogfile_name_offset(), pg_xlogfile_name(),
pg_last_xlog_receive_location(), pg_last_xlog_replay_location(), ...
etc) they helps to get get useful information about xlog files and its
content. So, this patch extends this amount of functions.

This function is mostly for debugging purposes.

--
Ivan Kartyshov
Postgres Professional: www.postgrespro.com
Russian Postgres Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Sat, Feb 27, 2016 at 3:52 PM, Kartyshov Ivan
<i.kartyshov@postgrespro.ru> wrote:

On 27.02.2016 03:07, Andres Freund wrote

How does it help with any of that?

Maybe i wasn't too accurate in terms, because I newbie, but:
We can get information about xlog, using big amout of support function
(pg_current_xlog_location(), pg_current_xlog_insert_location(),
pg_xlogfile_name_offset(), pg_xlogfile_name(),
pg_last_xlog_receive_location(), pg_last_xlog_replay_location(), ... etc)
they helps to get get useful information about xlog files and its content.
So, this patch extends this amount of functions.
It is useful additional tool for DBA (we can get replicationSlotMinLSN, so
why not in master), it can show us, if xlog replication or wal-sender is
working properly or indicate if replication on startup can get up to date
with master, or after long turnoff must be recovered from archive.

What pg_ls_dir('pg_xlog') couldn't do here if you need to know the
last WAL segment present on master?
--
Michael

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Hello.
You forgot to declare a GetXLogOldestLSNPtr function in a header file (in
xlog.h I think).
Some compilers refuse to compile this code (MSVC for example).
Now I see warning from GCC:

xlog.c:10627:1: warning: no previous prototype for «GetXLogOldestLSNPtr» [-Wmissing-prototypes]
GetXLogOldestLSNPtr(void)

Thanks.
--
Yury Zhuravlev
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 27.02.2016 22:36, Yury Zhuravlev wrote:

Hello.
You forgot to declare a GetXLogOldestLSNPtr function in a header file
(in xlog.h I think).
Some compilers refuse to compile this code (MSVC for example). Now I
see warning from GCC:

xlog.c:10627:1: warning: no previous prototype for
«GetXLogOldestLSNPtr» [-Wmissing-prototypes]
GetXLogOldestLSNPtr(void)

Thanks.

Hi Yury,
thank for valuable remark.
Changes applied

--
Ivan Kartyshov
Postgres Professional: www.postgrespro.com
Russian Postgres Company

On 27.02.2016 16:09, Michael Paquier wrote:

On Sat, Feb 27, 2016 at 3:52 PM, Kartyshov Ivan
<i.kartyshov@postgrespro.ru> wrote:

Maybe i wasn't too accurate in terms, because I newbie, but:
We can get information about xlog, using big amout of support function
(pg_current_xlog_location(), pg_current_xlog_insert_location(),
pg_xlogfile_name_offset(), pg_xlogfile_name(),
pg_last_xlog_receive_location(), pg_last_xlog_replay_location(), ... etc)
they helps to get get useful information about xlog files and its content.
So, this patch extends this amount of functions.
It is useful additional tool for DBA (we can get replicationSlotMinLSN, so
why not in master), it can show us, if xlog replication or wal-sender is
working properly or indicate if replication on startup can get up to date
with master, or after long turnoff must be recovered from archive.

What pg_ls_dir('pg_xlog') couldn't do here if you need to know the
last WAL segment present on master?

Hi Michael,

It will not satisfy our purposes and our administrators for three reasons.
1) DBA set me task to get the oldest number that present in WAL, not last
2) Surely we can choose the oldest segment from list
"pg_ls_dir('pg_xlog')" of segments and calculate the first LSN by hand,
but it is not what we want to do manually.
3) Soon will be commitfest and our administrators wants to enhance their
tool case for debug with usable features.

Thank you for comment.

--
Ivan Kartyshov
Postgres Professional: www.postgrespro.com
Russian Postgres Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Sun, Feb 28, 2016 at 4:40 PM, Kartyshov Ivan wrote:

It will not satisfy our purposes and our administrators for three reasons.
1) DBA set me task to get the oldest number that present in WAL, not last

Yeah I got that.

2) Surely we can choose the oldest segment from list "pg_ls_dir('pg_xlog')"
of segments and calculate the first LSN by hand, but it is not what we want
to do manually.

That's where I am not following. In any case you are just one SQL
query away from the result. And actually your patch is incorrect I
think. If you care about the oldest record available you should look
for the first LSN position of the oldest segment, no? What you are
calculating now is the oldest virtual LSN position in local pg_xlog.
--
Michael

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 28.02.2016 11:36, Michael Paquier wrote:

On Sun, Feb 28, 2016 at 4:40 PM, Kartyshov Ivan wrote:

It will not satisfy our purposes and our administrators for three reasons.
1) DBA set me task to get the oldest number that present in WAL, not last

Yeah I got that.

2) Surely we can choose the oldest segment from list "pg_ls_dir('pg_xlog')"
of segments and calculate the first LSN by hand, but it is not what we want
to do manually.

That's where I am not following. In any case you are just one SQL
query away from the result. And actually your patch is incorrect I
think. If you care about the oldest record available you should look
for the first LSN position of the oldest segment, no? What you are
calculating now is the oldest virtual LSN position in local pg_xlog.

Sorry, for long not answering and thank you for time you spent reviewing
this patch, please clarify what you meen, when you say "virtual LSN
position".
In whole you are right, my main goal is to get the oldest LSN position
in local pg_xlog, and i agree with you that it isn`t too hard to get it
by SQL query. But it will be more usefull, to have easy access in
userspace.

--
Ivan Kartyshov
Postgres Professional: www.postgrespro.com
Russian Postgres Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 11.03.2016 16:33, Kartyshov Ivan wrote:

On 28.02.2016 11:36, Michael Paquier wrote:

On Sun, Feb 28, 2016 at 4:40 PM, Kartyshov Ivan wrote:

It will not satisfy our purposes and our administrators for three
reasons.
1) DBA set me task to get the oldest number that present in WAL, not
last

Yeah I got that.

2) Surely we can choose the oldest segment from list
"pg_ls_dir('pg_xlog')"
of segments and calculate the first LSN by hand, but it is not what
we want
to do manually.

That's where I am not following. In any case you are just one SQL
query away from the result. And actually your patch is incorrect I
think. If you care about the oldest record available you should look
for the first LSN position of the oldest segment, no? What you are
calculating now is the oldest virtual LSN position in local pg_xlog.

Sorry, for long not answering and thank you for time you spent
reviewing this patch, please clarify what you meen, when you say
"virtual LSN position".
In whole you are right, my main goal is to get the oldest LSN position
in local pg_xlog, and i agree with you that it isn`t too hard to get
it by SQL query. But it will be more usefull, to have easy access in
userspace.

You wrote "If you care about the oldest record available you should look
for the first LSN position of the oldest segment, no?"
Yes I do it exactly this way.

--

--
Ivan Kartyshov
Postgres Professional: www.postgrespro.com
Russian Postgres Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Fri, Mar 11, 2016 at 2:35 PM, Kartyshov Ivan
<i.kartyshov@postgrespro.ru> wrote:

You wrote "If you care about the oldest record available you should look for
the first LSN position of the oldest segment, no?"
Yes I do it exactly this way.

Your patch does that:
+    XLogRecPtr    result;
+
+    XLogSegNoOffsetToRecPtr(XLogGetLastRemovedSegno()+1, 1, result);
+    return result;
So basically it returns the first possible LSN position of the last
segment present in pg_xlog. That's not true, this LSN position is not
the LSN of the oldest record, you are missing the header at the
beginning of the segment.

+    if (RecoveryInProgress())
+        ereport(ERROR,
+                (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+                 errmsg("recovery is in progress"),
+                 errhint("WAL control functions cannot be executed
during recovery.")));
I also don't get why this restriction is necessary. Segments get
recycled and removed as well at recovery.

In short, I finally understood why I was intuitively in meh-state with
this patch. If we want to have a SQL representation of what is in
shared memory for XLogCtlData, I think that this patch is too much
limited. Things like lastSegSwitchTime would be useful as well, at
least in my view some of them are interesting for debugging purposes.
If you have the last segment name removed returned as text at SQL
level, you could then compile the LSN position you'd wish to have for
your stuff.
--
Michael

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers