Thread: Introduce MIN/MAX aggregate functions to pg_lsn
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
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
Attachment
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
Attachment
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:
Regards,
-- Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
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>
+ <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);
+}
+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
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
--
> + <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
>
> +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
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
Attachment
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
Attachment
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
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
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
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
Attachment
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