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
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 1a01473..490f3a8 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -14663,7 +14663,7 @@ NULL baz</literallayout>(3 rows)</entry>
</indexterm>
<function>max(<replaceable class="parameter">expression</replaceable>)</function>
</entry>
- <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>Yes</entry>
@@ -14681,7 +14681,7 @@ NULL baz</literallayout>(3 rows)</entry>
</indexterm>
<function>min(<replaceable class="parameter">expression</replaceable>)</function>
</entry>
- <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>Yes</entry>
diff --git a/src/backend/utils/adt/pg_lsn.c b/src/backend/utils/adt/pg_lsn.c
index 7242d3c..ab393bc 100644
--- a/src/backend/utils/adt/pg_lsn.c
+++ b/src/backend/utils/adt/pg_lsn.c
@@ -155,6 +155,30 @@ pg_lsn_ge(PG_FUNCTION_ARGS)
PG_RETURN_BOOL(lsn1 >= lsn2);
}
+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);
+}
+
+Datum
+pg_lsn_smaller(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);
+}
+
/* btree index opclass support */
Datum
pg_lsn_cmp(PG_FUNCTION_ARGS)
diff --git a/src/include/catalog/pg_aggregate.dat b/src/include/catalog/pg_aggregate.dat
index 044695a..242d843 100644
--- a/src/include/catalog/pg_aggregate.dat
+++ b/src/include/catalog/pg_aggregate.dat
@@ -146,6 +146,9 @@
{ aggfnoid => 'max(inet)', aggtransfn => 'network_larger',
aggcombinefn => 'network_larger', aggsortop => '>(inet,inet)',
aggtranstype => 'inet' },
+{ aggfnoid => 'max(pg_lsn)', aggtransfn => 'pg_lsn_larger',
+ aggcombinefn => 'pg_lsn_larger', aggsortop => '>(pg_lsn,pg_lsn)',
+ aggtranstype => 'pg_lsn' },
# min
{ aggfnoid => 'min(int8)', aggtransfn => 'int8smaller',
@@ -208,6 +211,9 @@
{ aggfnoid => 'min(inet)', aggtransfn => 'network_smaller',
aggcombinefn => 'network_smaller', aggsortop => '<(inet,inet)',
aggtranstype => 'inet' },
+{ aggfnoid => 'min(pg_lsn)', aggtransfn => 'pg_lsn_smaller',
+ aggcombinefn => 'pg_lsn_smaller', aggsortop => '<(pg_lsn,pg_lsn)',
+ aggtranstype => 'pg_lsn' },
# count
{ aggfnoid => 'count(any)', aggtransfn => 'int8inc_any',
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index acf1131..cfc9b86 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6189,6 +6189,9 @@
{ oid => '3564', descr => 'maximum value of all inet input values',
proname => 'max', prokind => 'a', proisstrict => 'f', prorettype => 'inet',
proargtypes => 'inet', prosrc => 'aggregate_dummy' },
+{ oid => '8125', descr => 'maximum value of all pg_lsn input values',
+ proname => 'max', prokind => 'a', proisstrict => 'f', prorettype => 'pg_lsn',
+ proargtypes => 'pg_lsn', prosrc => 'aggregate_dummy' },
{ oid => '2131', descr => 'minimum value of all bigint input values',
proname => 'min', prokind => 'a', proisstrict => 'f', prorettype => 'int8',
@@ -6253,6 +6256,9 @@
{ oid => '3565', descr => 'minimum value of all inet input values',
proname => 'min', prokind => 'a', proisstrict => 'f', prorettype => 'inet',
proargtypes => 'inet', prosrc => 'aggregate_dummy' },
+{ oid => '8126', descr => 'minimum value of all pg_lsn input values',
+ proname => 'min', prokind => 'a', proisstrict => 'f', prorettype => 'pg_lsn',
+ proargtypes => 'pg_lsn', prosrc => 'aggregate_dummy' },
# count has two forms: count(any) and count(*)
{ oid => '2147',
@@ -8355,6 +8361,12 @@
{ oid => '3413', descr => 'hash',
proname => 'pg_lsn_hash_extended', prorettype => 'int8',
proargtypes => 'pg_lsn int8', prosrc => 'pg_lsn_hash_extended' },
+{ oid => '8123', descr => 'larger of two',
+ proname => 'pg_lsn_larger', prorettype => 'pg_lsn',
+ proargtypes => 'pg_lsn pg_lsn', prosrc => 'pg_lsn_larger' },
+{ oid => '8124', descr => 'smaller of two',
+ proname => 'pg_lsn_smaller', prorettype => 'pg_lsn',
+ proargtypes => 'pg_lsn pg_lsn', prosrc => 'pg_lsn_smaller' },
# enum related procs
{ oid => '3504', descr => 'I/O',
diff --git a/src/test/regress/expected/pg_lsn.out b/src/test/regress/expected/pg_lsn.out
index 2854cfd..64d41df 100644
--- a/src/test/regress/expected/pg_lsn.out
+++ b/src/test/regress/expected/pg_lsn.out
@@ -26,6 +26,13 @@ INSERT INTO PG_LSN_TBL VALUES ('/ABCD');
ERROR: invalid input syntax for type pg_lsn: "/ABCD"
LINE 1: INSERT INTO PG_LSN_TBL VALUES ('/ABCD');
^
+-- Min/Max aggregation
+SELECT MIN(f1), MAX(f1) FROM PG_LSN_TBL;
+ min | max
+-----+-------------------
+ 0/0 | FFFFFFFF/FFFFFFFF
+(1 row)
+
DROP TABLE PG_LSN_TBL;
-- Operators
SELECT '0/16AE7F8' = '0/16AE7F8'::pg_lsn;
diff --git a/src/test/regress/sql/pg_lsn.sql b/src/test/regress/sql/pg_lsn.sql
index 746f720..2047166 100644
--- a/src/test/regress/sql/pg_lsn.sql
+++ b/src/test/regress/sql/pg_lsn.sql
@@ -14,6 +14,9 @@ INSERT INTO PG_LSN_TBL VALUES ('-1/0');
INSERT INTO PG_LSN_TBL VALUES (' 0/12345678');
INSERT INTO PG_LSN_TBL VALUES ('ABCD/');
INSERT INTO PG_LSN_TBL VALUES ('/ABCD');
+
+-- Min/Max aggregation
+SELECT MIN(f1), MAX(f1) FROM PG_LSN_TBL;
DROP TABLE PG_LSN_TBL;
-- Operators
