Horton

Thread

Commits

  1. Add min() and max() aggregates for pg_lsn

  1. Introduce MIN/MAX aggregate functions to pg_lsn

    Fabrízio de Royes Mello <fabriziomello@gmail.com> — 2019-03-22T19:49:57Z

    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

  2. Re: Introduce MIN/MAX aggregate functions to pg_lsn

    Michael Paquier <michael@paquier.xyz> — 2019-03-23T01:27:23Z

    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

  3. Re: Introduce MIN/MAX aggregate functions to pg_lsn

    Fabrízio de Royes Mello <fabriziomello@gmail.com> — 2019-03-23T11:39:19Z

    On Fri, Mar 22, 2019 at 10:27 PM Michael Paquier <michael@paquier.xyz>
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:
https://commitfest.postgresql.org/23/2070/

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

  4. Re: Introduce MIN/MAX aggregate functions to pg_lsn

    Surafel Temsgen <surafel3000@gmail.com> — 2019-07-02T10:22:32Z

    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

  5. Re: Introduce MIN/MAX aggregate functions to pg_lsn

    Fabrízio de Royes Mello <fabriziomello@gmail.com> — 2019-07-02T14:31:49Z

    On Tue, Jul 2, 2019 at 7:22 AM Surafel Temesgen <surafel3000@gmail.com>
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

  6. Re: Introduce MIN/MAX aggregate functions to pg_lsn

    Michael Paquier <michael@paquier.xyz> — 2019-07-04T08:17:17Z

    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

  7. Re: Introduce MIN/MAX aggregate functions to pg_lsn

    Robert Haas <robertmhaas@gmail.com> — 2019-07-04T13:57:29Z

    On Thu, Jul 4, 2019 at 4:17 AM Michael Paquier <michael@paquier.xyz> 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

  8. Re: Introduce MIN/MAX aggregate functions to pg_lsn

    Fabrízio de Royes Mello <fabriziomello@gmail.com> — 2019-07-04T16:48:06Z

    On Thu, Jul 4, 2019 at 5:17 AM Michael Paquier <michael@paquier.xyz> 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

  9. Re: Introduce MIN/MAX aggregate functions to pg_lsn

    Fabrízio de Royes Mello <fabriziomello@gmail.com> — 2019-07-04T16:48:24Z

    On Thu, Jul 4, 2019 at 10:57 AM Robert Haas <robertmhaas@gmail.com> wrote:
>
> On Thu, Jul 4, 2019 at 4:17 AM Michael Paquier <michael@paquier.xyz>
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

  10. Re: Introduce MIN/MAX aggregate functions to pg_lsn

    Michael Paquier <michael@paquier.xyz> — 2019-07-05T03:21:53Z

    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 <robertmhaas@gmail.com> 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

  11. Re: Introduce MIN/MAX aggregate functions to pg_lsn

    Fabrízio de Royes Mello <fabriziomello@gmail.com> — 2019-07-05T12:52:15Z

    On Fri, Jul 5, 2019 at 12:22 AM Michael Paquier <michael@paquier.xyz> 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 <robertmhaas@gmail.com>
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