Skip to content

Commit 313f87a

Browse files
michaelpqmichaelpq
committed
Add min() and max() aggregates for pg_lsn
This is useful for monitoring, when it comes for example to calculations of WAL retention with replication slots and delays with a set of standbys. Bump catalog version. Author: Fabrízio de Royes Mello Reviewed-by: Surafel Temesgen Discussion: https://postgr.es/m/CAFcNs+oc8ZoHhowA4rR1GGCgG8QNgK_TOwPRVYQo5rYy8_PXzA@mail.gmail.com
1 parent 8a810a1 commit 313f87a

File tree

7 files changed

+50
-3
lines changed

7 files changed

+50
-3
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -14804,7 +14804,7 @@ NULL baz</literallayout>(3 rows)</entry>
1480414804
</indexterm>
1480514805
<function>max(<replaceable class="parameter">expression</replaceable>)</function>
1480614806
</entry>
14807-
<entry>any numeric, string, date/time, network, or enum type,
14807+
<entry>any numeric, string, date/time, network, pg_lsn, or enum type,
1480814808
or arrays of these types</entry>
1480914809
<entry>same as argument type</entry>
1481014810
<entry>Yes</entry>
@@ -14822,7 +14822,7 @@ NULL baz</literallayout>(3 rows)</entry>
1482214822
</indexterm>
1482314823
<function>min(<replaceable class="parameter">expression</replaceable>)</function>
1482414824
</entry>
14825-
<entry>any numeric, string, date/time, network, or enum type,
14825+
<entry>any numeric, string, date/time, network, pg_lsn, or enum type,
1482614826
or arrays of these types</entry>
1482714827
<entry>same as argument type</entry>
1482814828
<entry>Yes</entry>

‎src/backend/utils/adt/pg_lsn.c

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -171,6 +171,24 @@ pg_lsn_ge(PG_FUNCTION_ARGS)
171171
PG_RETURN_BOOL(lsn1 >= lsn2);
172172
}
173173

174+
Datum
175+
pg_lsn_larger(PG_FUNCTION_ARGS)
176+
{
177+
XLogRecPtr lsn1 = PG_GETARG_LSN(0);
178+
XLogRecPtr lsn2 = PG_GETARG_LSN(1);
179+
180+
PG_RETURN_LSN((lsn1 > lsn2) ? lsn1 : lsn2);
181+
}
182+
183+
Datum
184+
pg_lsn_smaller(PG_FUNCTION_ARGS)
185+
{
186+
XLogRecPtr lsn1 = PG_GETARG_LSN(0);
187+
XLogRecPtr lsn2 = PG_GETARG_LSN(1);
188+
189+
PG_RETURN_LSN((lsn1 < lsn2) ? lsn1 : lsn2);
190+
}
191+
174192
/* btree index opclass support */
175193
Datum
176194
pg_lsn_cmp(PG_FUNCTION_ARGS)

‎src/include/catalog/catversion.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/* yyyymmddN */
56-
#define CATALOG_VERSION_NO 201907052
56+
#define CATALOG_VERSION_NO 201907053
5757

5858
#endif

‎src/include/catalog/pg_aggregate.dat

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -146,6 +146,9 @@
146146
{ aggfnoid => 'max(inet)', aggtransfn => 'network_larger',
147147
aggcombinefn => 'network_larger', aggsortop => '>(inet,inet)',
148148
aggtranstype => 'inet' },
149+
{ aggfnoid => 'max(pg_lsn)', aggtransfn => 'pg_lsn_larger',
150+
aggcombinefn => 'pg_lsn_larger', aggsortop => '>(pg_lsn,pg_lsn)',
151+
aggtranstype => 'pg_lsn' },
149152

150153
# min
151154
{ aggfnoid => 'min(int8)', aggtransfn => 'int8smaller',
@@ -208,6 +211,9 @@
208211
{ aggfnoid => 'min(inet)', aggtransfn => 'network_smaller',
209212
aggcombinefn => 'network_smaller', aggsortop => '<(inet,inet)',
210213
aggtranstype => 'inet' },
214+
{ aggfnoid => 'min(pg_lsn)', aggtransfn => 'pg_lsn_smaller',
215+
aggcombinefn => 'pg_lsn_smaller', aggsortop => '<(pg_lsn,pg_lsn)',
216+
aggtranstype => 'pg_lsn' },
211217

212218
# count
213219
{ aggfnoid => 'count(any)', aggtransfn => 'int8inc_any',

‎src/include/catalog/pg_proc.dat

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -6219,6 +6219,9 @@
62196219
{ oid => '3564', descr => 'maximum value of all inet input values',
62206220
proname => 'max', prokind => 'a', proisstrict => 'f', prorettype => 'inet',
62216221
proargtypes => 'inet', prosrc => 'aggregate_dummy' },
6222+
{ oid => '4189', descr => 'maximum value of all pg_lsn input values',
6223+
proname => 'max', prokind => 'a', proisstrict => 'f', prorettype => 'pg_lsn',
6224+
proargtypes => 'pg_lsn', prosrc => 'aggregate_dummy' },
62226225

62236226
{ oid => '2131', descr => 'minimum value of all bigint input values',
62246227
proname => 'min', prokind => 'a', proisstrict => 'f', prorettype => 'int8',
@@ -6283,6 +6286,9 @@
62836286
{ oid => '3565', descr => 'minimum value of all inet input values',
62846287
proname => 'min', prokind => 'a', proisstrict => 'f', prorettype => 'inet',
62856288
proargtypes => 'inet', prosrc => 'aggregate_dummy' },
6289+
{ oid => '4190', descr => 'minimum value of all pg_lsn input values',
6290+
proname => 'min', prokind => 'a', proisstrict => 'f', prorettype => 'pg_lsn',
6291+
proargtypes => 'pg_lsn', prosrc => 'aggregate_dummy' },
62866292

62876293
# count has two forms: count(any) and count(*)
62886294
{ oid => '2147',
@@ -8385,6 +8391,12 @@
83858391
{ oid => '3413', descr => 'hash',
83868392
proname => 'pg_lsn_hash_extended', prorettype => 'int8',
83878393
proargtypes => 'pg_lsn int8', prosrc => 'pg_lsn_hash_extended' },
8394+
{ oid => '4187', descr => 'larger of two',
8395+
proname => 'pg_lsn_larger', prorettype => 'pg_lsn',
8396+
proargtypes => 'pg_lsn pg_lsn', prosrc => 'pg_lsn_larger' },
8397+
{ oid => '4188', descr => 'smaller of two',
8398+
proname => 'pg_lsn_smaller', prorettype => 'pg_lsn',
8399+
proargtypes => 'pg_lsn pg_lsn', prosrc => 'pg_lsn_smaller' },
83888400

83898401
# enum related procs
83908402
{ oid => '3504', descr => 'I/O',

‎src/test/regress/expected/pg_lsn.out

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -26,6 +26,13 @@ INSERT INTO PG_LSN_TBL VALUES ('/ABCD');
2626
ERROR: invalid input syntax for type pg_lsn: "/ABCD"
2727
LINE 1: INSERT INTO PG_LSN_TBL VALUES ('/ABCD');
2828
^
29+
-- Min/Max aggregation
30+
SELECT MIN(f1), MAX(f1) FROM PG_LSN_TBL;
31+
min | max
32+
-----+-------------------
33+
0/0 | FFFFFFFF/FFFFFFFF
34+
(1 row)
35+
2936
DROP TABLE PG_LSN_TBL;
3037
-- Operators
3138
SELECT '0/16AE7F8' = '0/16AE7F8'::pg_lsn;

‎src/test/regress/sql/pg_lsn.sql

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -14,6 +14,10 @@ INSERT INTO PG_LSN_TBL VALUES ('-1/0');
1414
INSERT INTO PG_LSN_TBL VALUES (' 0/12345678');
1515
INSERT INTO PG_LSN_TBL VALUES ('ABCD/');
1616
INSERT INTO PG_LSN_TBL VALUES ('/ABCD');
17+
18+
-- Min/Max aggregation
19+
SELECT MIN(f1), MAX(f1) FROM PG_LSN_TBL;
20+
1721
DROP TABLE PG_LSN_TBL;
1822

1923
-- Operators

0 commit comments

Comments
 (0)