Thread: Function and view to retrieve WAL receiver status

Function and view to retrieve WAL receiver status

From
Michael Paquier
Date:
Hi all,

Currently there is no equivalent of pg_stat_get_wal_senders for the
WAL receiver on a node, and it seems that it would be useful to have
an SQL representation of what is in shared memory should a WAL
receiver be active without going through the ps display for example.
So, any opinion about having in core a function called
pg_stat_get_wal_receiver that returns a single tuple that translates
the data WalRcvData?
We could bundle on top of it a system view, say called
pg_stat_wal_receiver, with this layer:
View "public.pg_stat_wal_receiver"
Column|Type|Modifiers
pid|integer|
status|text|
receive_start_lsn|pg_lsn|
receive_start_tli|integer|
received_up_to_lsn|pg_lsn|
received_tli|integer|
latest_chunk_start_lsn|pg_lsn|
last_msg_send_time|timestamp with time zone|
last_msg_receipt_time|timestamp with time zone|
latest_end_lsn|pg_lsn|
latest_end_time|timestamp with time zone|
slot_name|text|

If the node has no WAL receiver active, a tuple with NULL values is
returned instead.
Thoughts?
-- 
Michael



Re: Function and view to retrieve WAL receiver status

From
Gurjeet Singh
Date:
<p dir="ltr"><br /> On Dec 13, 2015 9:56 PM, "Michael Paquier" <<a
href="mailto:michael.paquier@gmail.com">michael.paquier@gmail.com</a>>wrote:<p dir="ltr">><br /> > If the node
hasno WAL receiver active, a tuple with NULL values is<br /> > returned instead.<p dir="ltr">IMO, in the absence of
aWAL receiver the SRF (and the view) should not return any rows.<br /> 

Re: Function and view to retrieve WAL receiver status

From
Michael Paquier
Date:
On Mon, Dec 14, 2015 at 3:09 PM, Gurjeet Singh <singh.gurjeet@gmail.com> wrote:
> On Dec 13, 2015 9:56 PM, "Michael Paquier" <michael.paquier@gmail.com>
> wrote:
>> If the node has no WAL receiver active, a tuple with NULL values is
>> returned instead.
>
> IMO, in the absence of a WAL receiver the SRF (and the view) should not
> return any rows.

The whole point is to not use a SRF in this case: there is always at
most one WAL receiver.
-- 
Michael



Re: Function and view to retrieve WAL receiver status

From
Gurjeet Singh
Date:

On Sun, Dec 13, 2015 at 10:15 PM, Michael Paquier <michael.paquier@gmail.com> wrote:
On Mon, Dec 14, 2015 at 3:09 PM, Gurjeet Singh <singh.gurjeet@gmail.com> wrote:
> On Dec 13, 2015 9:56 PM, "Michael Paquier" <michael.paquier@gmail.com>
> wrote:
>> If the node has no WAL receiver active, a tuple with NULL values is
>> returned instead.
>
> IMO, in the absence of a WAL receiver the SRF (and the view) should not
> return any rows.

The whole point is to not use a SRF in this case: there is always at
most one WAL receiver.

The function, maybe. But emitting an all-nulls row from a view seems counter-intuitive, at least when looking at it in context of relational database.

--

Re: Function and view to retrieve WAL receiver status

From
Michael Paquier
Date:
On Tue, Dec 15, 2015 at 5:27 AM, Gurjeet Singh wrote:
> The function, maybe. But emitting an all-nulls row from a view seems
> counter-intuitive, at least when looking at it in context of relational
> database.

OK, noted. Any other opinions?
-- 
Michael



Re: Function and view to retrieve WAL receiver status

From
Robert Haas
Date:
On Mon, Dec 14, 2015 at 7:23 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:
> On Tue, Dec 15, 2015 at 5:27 AM, Gurjeet Singh wrote:
>> The function, maybe. But emitting an all-nulls row from a view seems
>> counter-intuitive, at least when looking at it in context of relational
>> database.
>
> OK, noted. Any other opinions?

I wouldn't bother with the view.  If we're going to do it, I'd say
just provide the function and let people SELECT * from it if they want
to.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company



Re: Function and view to retrieve WAL receiver status

From
Michael Paquier
Date:
On Fri, Dec 18, 2015 at 8:39 AM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Mon, Dec 14, 2015 at 7:23 PM, Michael Paquier
> <michael.paquier@gmail.com> wrote:
>> On Tue, Dec 15, 2015 at 5:27 AM, Gurjeet Singh wrote:
>>> The function, maybe. But emitting an all-nulls row from a view seems
>>> counter-intuitive, at least when looking at it in context of relational
>>> database.
>>
>> OK, noted. Any other opinions?
>
> I wouldn't bother with the view.  If we're going to do it, I'd say
> just provide the function and let people SELECT * from it if they want
> to.

OK, I took some time to write a patch for that as attached, added in
the next CF here:
https://commitfest.postgresql.org/8/447/
I am fine switching to an SRF depending on other opinions of people
here, it just seems like an overkill knowing the uniqueness of the WAL
sender in a server.

I have finished with a function and a system view, this came up more
in line with the existing things like pg_stat_archiver, and this makes
as well the documentation clearer, at least that was my feeling when
hacking that.
Regards,
--
Michael

Attachment

Re: Function and view to retrieve WAL receiver status

From
Haribabu Kommi
Date:
On Sat, Dec 19, 2015 at 12:54 AM, Michael Paquier
<michael.paquier@gmail.com> wrote:
> On Fri, Dec 18, 2015 at 8:39 AM, Robert Haas <robertmhaas@gmail.com> wrote:
>> On Mon, Dec 14, 2015 at 7:23 PM, Michael Paquier
>> <michael.paquier@gmail.com> wrote:
>>> On Tue, Dec 15, 2015 at 5:27 AM, Gurjeet Singh wrote:
>>>> The function, maybe. But emitting an all-nulls row from a view seems
>>>> counter-intuitive, at least when looking at it in context of relational
>>>> database.
>>>
>>> OK, noted. Any other opinions?
>>
>> I wouldn't bother with the view.  If we're going to do it, I'd say
>> just provide the function and let people SELECT * from it if they want
>> to.
>
> OK, I took some time to write a patch for that as attached, added in
> the next CF here:
> https://commitfest.postgresql.org/8/447/
> I am fine switching to an SRF depending on other opinions of people
> here, it just seems like an overkill knowing the uniqueness of the WAL
> sender in a server.
>
> I have finished with a function and a system view, this came up more
> in line with the existing things like pg_stat_archiver, and this makes
> as well the documentation clearer, at least that was my feeling when
> hacking that.

I also feel showing NULL values may not be good, when there is
no walreceiver. Instead of SRF function to avoid showing NULL vallues
how about adding "WHERE s.pid IS NOT NULL" to the system view.
pid value cannot be NULL, until unless there is no walreceiver.


Regards,
Hari Babu
Fujitsu Australia



Re: Function and view to retrieve WAL receiver status

From
Michael Paquier
Date:
On Tue, Jan 5, 2016 at 7:49 PM, Haribabu Kommi <kommi.haribabu@gmail.com> wrote:
> On Sat, Dec 19, 2015 at 12:54 AM, Michael Paquier
> <michael.paquier@gmail.com> wrote:
>> On Fri, Dec 18, 2015 at 8:39 AM, Robert Haas <robertmhaas@gmail.com> wrote:
>>> On Mon, Dec 14, 2015 at 7:23 PM, Michael Paquier
>>> <michael.paquier@gmail.com> wrote:
>>>> On Tue, Dec 15, 2015 at 5:27 AM, Gurjeet Singh wrote:
>>>>> The function, maybe. But emitting an all-nulls row from a view seems
>>>>> counter-intuitive, at least when looking at it in context of relational
>>>>> database.
>>>>
>>>> OK, noted. Any other opinions?
>>>
>>> I wouldn't bother with the view.  If we're going to do it, I'd say
>>> just provide the function and let people SELECT * from it if they want
>>> to.
>>
>> OK, I took some time to write a patch for that as attached, added in
>> the next CF here:
>> https://commitfest.postgresql.org/8/447/
>> I am fine switching to an SRF depending on other opinions of people
>> here, it just seems like an overkill knowing the uniqueness of the WAL
>> sender in a server.
>>
>> I have finished with a function and a system view, this came up more
>> in line with the existing things like pg_stat_archiver, and this makes
>> as well the documentation clearer, at least that was my feeling when
>> hacking that.
>
> I also feel showing NULL values may not be good, when there is
> no walreceiver. Instead of SRF function to avoid showing NULL vallues
> how about adding "WHERE s.pid IS NOT NULL" to the system view.
> pid value cannot be NULL, until unless there is no walreceiver.

Yeah, I would not mind switching it to that. A couple of other stat
catalog views do it as well.
-- 
Michael



Re: Function and view to retrieve WAL receiver status

From
Haribabu Kommi
Date:
On Tue, Jan 5, 2016 at 10:24 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:
> On Tue, Jan 5, 2016 at 7:49 PM, Haribabu Kommi <kommi.haribabu@gmail.com> wrote:
>> On Sat, Dec 19, 2015 at 12:54 AM, Michael Paquier
>> <michael.paquier@gmail.com> wrote:
>>> On Fri, Dec 18, 2015 at 8:39 AM, Robert Haas <robertmhaas@gmail.com> wrote:
>>>> On Mon, Dec 14, 2015 at 7:23 PM, Michael Paquier
>>>> <michael.paquier@gmail.com> wrote:
>>>>> On Tue, Dec 15, 2015 at 5:27 AM, Gurjeet Singh wrote:
>>>>>> The function, maybe. But emitting an all-nulls row from a view seems
>>>>>> counter-intuitive, at least when looking at it in context of relational
>>>>>> database.
>>>>>
>>>>> OK, noted. Any other opinions?
>>>>
>>>> I wouldn't bother with the view.  If we're going to do it, I'd say
>>>> just provide the function and let people SELECT * from it if they want
>>>> to.
>>>
>>> OK, I took some time to write a patch for that as attached, added in
>>> the next CF here:
>>> https://commitfest.postgresql.org/8/447/
>>> I am fine switching to an SRF depending on other opinions of people
>>> here, it just seems like an overkill knowing the uniqueness of the WAL
>>> sender in a server.
>>>
>>> I have finished with a function and a system view, this came up more
>>> in line with the existing things like pg_stat_archiver, and this makes
>>> as well the documentation clearer, at least that was my feeling when
>>> hacking that.
>>
>> I also feel showing NULL values may not be good, when there is
>> no walreceiver. Instead of SRF function to avoid showing NULL vallues
>> how about adding "WHERE s.pid IS NOT NULL" to the system view.
>> pid value cannot be NULL, until unless there is no walreceiver.
>
> Yeah, I would not mind switching it to that. A couple of other stat
> catalog views do it as well.

Following are my observations on the latest patch.

+ If no WAL receiver is present on the server queried,
+   a single tuple filled with <literal>NULL</> values is returned instead.
+  </para>

The above documentation change is not required if we change the system
view.

+            s.received_up_to_lsn,

The column name can be changed as "received_lsn" similar to "received_tli".
up_to may not be required.

+ XLogRecPtr received_up_lsn;
+ TimeLineID received_up_tli;

same as like above comment.

+ /* lock? */

I find out that walrcv data is updated only under mutex. it is better
to take that
mutex to provide a consistent snapshot data to user.


Regards,
Hari Babu
Fujitsu Australia



Re: Function and view to retrieve WAL receiver status

From
Michael Paquier
Date:
On Wed, Jan 6, 2016 at 8:14 AM, Haribabu Kommi <kommi.haribabu@gmail.com> wrote:
> Following are my observations on the latest patch.

Thanks for your review.

> + If no WAL receiver is present on the server queried,
> +   a single tuple filled with <literal>NULL</> values is returned instead.
> +  </para>
>
> The above documentation change is not required if we change the system
> view.

Affirmative.

> +            s.received_up_to_lsn,
>
> The column name can be changed as "received_lsn" similar to "received_tli".
> up_to may not be required.
>
> + XLogRecPtr received_up_lsn;
> + TimeLineID received_up_tli;
>
> same as like above comment.

Indeed, let's make the variable names more simple and consistent by
removing this _up_ portion everywhere.

> + /* lock? */
>
> I find out that walrcv data is updated only under mutex. it is better
> to take that mutex to provide a consistent snapshot data to user.

The lock is taken, the comment is just incorrect:
+    /* lock? */
+    SpinLockAcquire(&walrcv->mutex);
[...]
+    SpinLockRelease(&walrcv->mutex);

I also found out that the description of those fields was not clear
enough actually: received_tli and received _lsn are related to what
has been received *and* flushed to disk, with an initial value being
their start equivalent. This deserves a clear description with all
those things addressed.

Attached is an updated patch.
--
Michael

Attachment

Re: Function and view to retrieve WAL receiver status

From
Michael Paquier
Date:
On Wed, Jan 6, 2016 at 3:04 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:
> Attached is an updated patch.

Forgot to update rules.out...
--
Michael

Attachment

Re: Function and view to retrieve WAL receiver status

From
Haribabu Kommi
Date:
On Wed, Jan 6, 2016 at 8:00 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:
> On Wed, Jan 6, 2016 at 3:04 PM, Michael Paquier
> <michael.paquier@gmail.com> wrote:
>> Attached is an updated patch.
>
> Forgot to update rules.out...

Thanks for the update. Patch looks good to me.
I marked it as ready for committer.

Regards,
Hari Babu
Fujitsu Australia



Re: Function and view to retrieve WAL receiver status

From
Michael Paquier
Date:
On Thu, Jan 7, 2016 at 1:57 PM, Haribabu Kommi <kommi.haribabu@gmail.com> wrote:
> On Wed, Jan 6, 2016 at 8:00 PM, Michael Paquier
> <michael.paquier@gmail.com> wrote:
>> On Wed, Jan 6, 2016 at 3:04 PM, Michael Paquier
>> <michael.paquier@gmail.com> wrote:
>>> Attached is an updated patch.
>>
>> Forgot to update rules.out...
>
> Thanks for the update. Patch looks good to me.
> I marked it as ready for committer.

Thanks!
-- 
Michael



Re: Function and view to retrieve WAL receiver status

From
Alvaro Herrera
Date:
Michael Paquier wrote:
> On Thu, Jan 7, 2016 at 1:57 PM, Haribabu Kommi <kommi.haribabu@gmail.com> wrote:
> > On Wed, Jan 6, 2016 at 8:00 PM, Michael Paquier
> > <michael.paquier@gmail.com> wrote:
> >> On Wed, Jan 6, 2016 at 3:04 PM, Michael Paquier
> >> <michael.paquier@gmail.com> wrote:
> >>> Attached is an updated patch.
> >>
> >> Forgot to update rules.out...
> >
> > Thanks for the update. Patch looks good to me.
> > I marked it as ready for committer.
> 
> Thanks!

Messed around with it, couldn't find any fault, pushed.

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services



Re: Function and view to retrieve WAL receiver status

From
Michael Paquier
Date:
On Fri, Jan 8, 2016 at 4:38 AM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
> Michael Paquier wrote:
>> On Thu, Jan 7, 2016 at 1:57 PM, Haribabu Kommi <kommi.haribabu@gmail.com> wrote:
>> > On Wed, Jan 6, 2016 at 8:00 PM, Michael Paquier
>> > <michael.paquier@gmail.com> wrote:
>> >> On Wed, Jan 6, 2016 at 3:04 PM, Michael Paquier
>> >> <michael.paquier@gmail.com> wrote:
>> >>> Attached is an updated patch.
>> >>
>> >> Forgot to update rules.out...
>> >
>> > Thanks for the update. Patch looks good to me.
>> > I marked it as ready for committer.
>>
>> Thanks!
>
> Messed around with it, couldn't find any fault, pushed.

Thanks!
-- 
Michael