Introduce MIN/MAX aggregate functions to pg_lsn

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

Introduce MIN/MAX aggregate functions to pg_lsn

fabriziomello
Hi all,

Before we introduce pg_lsn datatype the LSN was expressed as a TEXT type, so a simple query using MIN/MAX functions works as expected. Query like:

SELECT min(restart_lsn) FROM pg_replication_slots;
SELECT min(sent_lsn) FROM pg_stat_replication ;

So attached patch aims to introduce MIN/MAX aggregate functions to pg_lsn datatype.

Regards,

--
   Fabrízio de Royes Mello         Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

add_min_max_aggregates_for_pg_lsn_type_v1.patch (7K) Download Attachment
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Blog sobre TI: http://fabriziomello.blogspot.com
>> Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello
Reply | Threaded
Open this post in threaded view
|

Re: Introduce MIN/MAX aggregate functions to pg_lsn

Michael Paquier-2
On Fri, Mar 22, 2019 at 04:49:57PM -0300, Fabrízio de Royes Mello wrote:
> So attached patch aims to introduce MIN/MAX aggregate functions to pg_lsn

Fine by me.  This looks helpful for monitoring.

Please make sure to register it to the next commit fest:
https://commitfest.postgresql.org/23/
It is too late for Postgres 12 unfortunately.
--
Michael

signature.asc (849 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Introduce MIN/MAX aggregate functions to pg_lsn

fabriziomello

On Fri, Mar 22, 2019 at 10:27 PM Michael Paquier <[hidden email]> wrote:
>
> On Fri, Mar 22, 2019 at 04:49:57PM -0300, Fabrízio de Royes Mello wrote:
> > So attached patch aims to introduce MIN/MAX aggregate functions to pg_lsn
>
> Fine by me.  This looks helpful for monitoring.
>
> Please make sure to register it to the next commit fest:
> https://commitfest.postgresql.org/23/
> It is too late for Postgres 12 unfortunately.

Sure, added:

Regards,
--
   Fabrízio de Royes Mello         Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Blog sobre TI: http://fabriziomello.blogspot.com
>> Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello
Reply | Threaded
Open this post in threaded view
|

Re: Introduce MIN/MAX aggregate functions to pg_lsn

Surafel Temesgen
In reply to this post by fabriziomello
Hi,
Here are same review comment
-      <entry>any numeric, string, date/time, network, or enum type,
+      <entry>any numeric, string, date/time, network, lsn, or enum type,
              or arrays of these types</entry>
       <entry>same as argument type</entry>
In the documentation it refereed as pg_lsn type rather than lsn alone
+Datum
+pg_lsn_larger(PG_FUNCTION_ARGS)
+{
+ XLogRecPtr lsn1 = PG_GETARG_LSN(0);
+ XLogRecPtr lsn2 = PG_GETARG_LSN(1);
+ XLogRecPtr result;
+
+ result = ((lsn1 > lsn2) ? lsn1 : lsn2);
+
+ PG_RETURN_LSN(result);
+}

rather than using additional variable its more readable and effective to return the argument
itself like we do in date data type and other place
regards
Surafel

Reply | Threaded
Open this post in threaded view
|

Re: Introduce MIN/MAX aggregate functions to pg_lsn

fabriziomello


On Tue, Jul 2, 2019 at 7:22 AM Surafel Temesgen <[hidden email]> wrote:
>
> Hi,
> Here are same review comment

Thanks for your review.

> -      <entry>any numeric, string, date/time, network, or enum type,
> +      <entry>any numeric, string, date/time, network, lsn, or enum type,
>               or arrays of these types</entry>
>        <entry>same as argument type</entry>
> In the documentation it refereed as pg_lsn type rather than lsn alone

Fixed.

> +Datum

> +pg_lsn_larger(PG_FUNCTION_ARGS)
> +{
> + XLogRecPtr lsn1 = PG_GETARG_LSN(0);
> + XLogRecPtr lsn2 = PG_GETARG_LSN(1);
> + XLogRecPtr result;
> +
> + result = ((lsn1 > lsn2) ? lsn1 : lsn2);
> +
> + PG_RETURN_LSN(result);
> +}
>
> rather than using additional variable its more readable and effective to return the argument
> itself like we do in date data type and other place
>
Fixed.

New version attached.

Regards,

--
   Fabrízio de Royes Mello         Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

add_min_max_aggregates_for_pg_lsn_type_v2.patch (7K) Download Attachment
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Blog sobre TI: http://fabriziomello.blogspot.com
>> Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello
Reply | Threaded
Open this post in threaded view
|

Re: Introduce MIN/MAX aggregate functions to pg_lsn

Michael Paquier-2
On Tue, Jul 02, 2019 at 11:31:49AM -0300, Fabrízio de Royes Mello wrote:
> New version attached.

This looks in pretty good shape to me, and no objections from me to
get those functions as the min() flavor is useful for monitoring WAL
retention for complex deployments.

Do you have a particular use-case in mind for max() one?  I can think
of at least one case: monitoring the flush LSNs of a set of standbys
to find out how much has been replayed at most.
--
Michael

signature.asc (849 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Introduce MIN/MAX aggregate functions to pg_lsn

Robert Haas
On Thu, Jul 4, 2019 at 4:17 AM Michael Paquier <[hidden email]> wrote:
> Do you have a particular use-case in mind for max() one?  I can think
> of at least one case: monitoring the flush LSNs of a set of standbys
> to find out how much has been replayed at most.

It would be pretty silly to have one and not the other, regardless of
whether we can think of an immediate use case.

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


Reply | Threaded
Open this post in threaded view
|

Re: Introduce MIN/MAX aggregate functions to pg_lsn

fabriziomello
In reply to this post by Michael Paquier-2


On Thu, Jul 4, 2019 at 5:17 AM Michael Paquier <[hidden email]> wrote:

>
> On Tue, Jul 02, 2019 at 11:31:49AM -0300, Fabrízio de Royes Mello wrote:
> > New version attached.
>
> This looks in pretty good shape to me, and no objections from me to
> get those functions as the min() flavor is useful for monitoring WAL
> retention for complex deployments.
>
> Do you have a particular use-case in mind for max() one?  I can think
> of at least one case: monitoring the flush LSNs of a set of standbys
> to find out how much has been replayed at most.
>

I use min/max to measure the amount of generated WAL (diff) during some periods based on wal position stored in some monitoring system. 

Regards,

--
   Fabrízio de Royes Mello         Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Blog sobre TI: http://fabriziomello.blogspot.com
>> Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello
Reply | Threaded
Open this post in threaded view
|

Re: Introduce MIN/MAX aggregate functions to pg_lsn

fabriziomello
In reply to this post by Robert Haas

On Thu, Jul 4, 2019 at 10:57 AM Robert Haas <[hidden email]> wrote:
>
> On Thu, Jul 4, 2019 at 4:17 AM Michael Paquier <[hidden email]> wrote:
> > Do you have a particular use-case in mind for max() one?  I can think
> > of at least one case: monitoring the flush LSNs of a set of standbys
> > to find out how much has been replayed at most.
>
> It would be pretty silly to have one and not the other, regardless of
> whether we can think of an immediate use case.
>

+1


--
   Fabrízio de Royes Mello         Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Blog sobre TI: http://fabriziomello.blogspot.com
>> Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello
Reply | Threaded
Open this post in threaded view
|

Re: Introduce MIN/MAX aggregate functions to pg_lsn

Michael Paquier-2
On Thu, Jul 04, 2019 at 01:48:24PM -0300, Fabrízio de Royes Mello wrote:
> On Thu, Jul 4, 2019 at 10:57 AM Robert Haas <[hidden email]> wrote:
>> It would be pretty silly to have one and not the other, regardless of
>> whether we can think of an immediate use case.
>
> +1

OK, applied with a catalog version bump.  This is cool to have.
--
Michael

signature.asc (849 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Introduce MIN/MAX aggregate functions to pg_lsn

fabriziomello


On Fri, Jul 5, 2019 at 12:22 AM Michael Paquier <[hidden email]> wrote:

>
> On Thu, Jul 04, 2019 at 01:48:24PM -0300, Fabrízio de Royes Mello wrote:
> > On Thu, Jul 4, 2019 at 10:57 AM Robert Haas <[hidden email]> wrote:
> >> It would be pretty silly to have one and not the other, regardless of
> >> whether we can think of an immediate use case.
> >
> > +1
>
> OK, applied with a catalog version bump.  This is cool to have.

Awesome... thanks.

Att,

--
   Fabrízio de Royes Mello         Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Blog sobre TI: http://fabriziomello.blogspot.com
>> Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello