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
Attachments:
add_min_max_aggregates_for_pg_lsn_type_v1.patchtext/x-patch; charset=US-ASCII; name=add_min_max_aggregates_for_pg_lsn_type_v1.patchDownload+54-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
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
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
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
Attachments:
add_min_max_aggregates_for_pg_lsn_type_v2.patchtext/x-patch; charset=US-ASCII; name=add_min_max_aggregates_for_pg_lsn_type_v2.patchDownload+48-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
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
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