Hi,
I'd like to propose to introduce the +(pg_lsn, int8) and -(pg_lsn, int8)
operators. The + operator allows us to add the number of bytes into pg_lsn,
resulting new pg_lsn. The - operator allows us to substract the number
of bytes from pg_lsn, resulting new pg_lsn. Thought?
I sometimes need these features for debuging purpose.
Attached is the patch implementing those operators.
Of course, this is the dev item for v14.
Regards,
--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION
diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
index c2e42f31c0..c5469e4678 100644
--- a/doc/src/sgml/datatype.sgml
+++ b/doc/src/sgml/datatype.sgml
@@ -4776,7 +4776,10 @@ SELECT * FROM pg_attribute
standard comparison operators, like <literal>=</literal> and
<literal>></literal>. Two LSNs can be subtracted using the
<literal>-</literal> operator; the result is the number of bytes separating
- those write-ahead log locations.
+ those write-ahead log locations. Also the number of bytes can be added
+ into and substracted from LSN using the <literal>+</literal> and
+ <literal>-</literal> operators, respectively.
+
</para>
</sect1>
diff --git a/src/backend/utils/adt/pg_lsn.c b/src/backend/utils/adt/pg_lsn.c
index d9754a7778..e4bdad3ef7 100644
--- a/src/backend/utils/adt/pg_lsn.c
+++ b/src/backend/utils/adt/pg_lsn.c
@@ -248,3 +248,49 @@ pg_lsn_mi(PG_FUNCTION_ARGS)
return result;
}
+
+/*
+ * Add the number of bytes to pg_lsn, giving a new pg_lsn.
+ * Must handle both positive and negative numbers of bytes.
+ */
+Datum
+pg_lsn_pli(PG_FUNCTION_ARGS)
+{
+ XLogRecPtr lsn = PG_GETARG_LSN(0);
+ int64 nbytes = PG_GETARG_INT64(1);
+ XLogRecPtr result;
+
+ result = lsn + nbytes;
+
+ /* Check for pg_lsn overflow */
+ if ((nbytes >= 0 && result < lsn) ||
+ (nbytes < 0 && result > lsn))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("pg_lsn out of range")));
+
+ PG_RETURN_LSN(result);
+}
+
+/*
+ * Substract the number of bytes from pg_lsn, giving a new pg_lsn.
+ * Must handle both positive and negative numbers of bytes.
+ */
+Datum
+pg_lsn_mii(PG_FUNCTION_ARGS)
+{
+ XLogRecPtr lsn = PG_GETARG_LSN(0);
+ int64 nbytes = PG_GETARG_INT64(1);
+ XLogRecPtr result;
+
+ result = lsn - nbytes;
+
+ /* Check for pg_lsn overflow */
+ if ((nbytes >= 0 && result > lsn) ||
+ (nbytes < 0 && result < lsn))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("pg_lsn out of range")));
+
+ PG_RETURN_LSN(result);
+}
diff --git a/src/include/catalog/pg_operator.dat
b/src/include/catalog/pg_operator.dat
index 00ada7e48f..dc3dcc30c5 100644
--- a/src/include/catalog/pg_operator.dat
+++ b/src/include/catalog/pg_operator.dat
@@ -2909,6 +2909,12 @@
{ oid => '3228', descr => 'minus',
oprname => '-', oprleft => 'pg_lsn', oprright => 'pg_lsn',
oprresult => 'numeric', oprcode => 'pg_lsn_mi' },
+{ oid => '4179', descr => 'add',
+ oprname => '+', oprleft => 'pg_lsn', oprright => 'int8',
+ oprresult => 'pg_lsn', oprcode => 'pg_lsn_pli' },
+{ oid => '4180', descr => 'subtract',
+ oprname => '-', oprleft => 'pg_lsn', oprright => 'int8',
+ oprresult => 'pg_lsn', oprcode => 'pg_lsn_mii' },
# enum operators
{ oid => '3516', descr => 'equal',
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 4bce3ad8de..198feddc91 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -8578,6 +8578,12 @@
{ oid => '4188', descr => 'smaller of two',
proname => 'pg_lsn_smaller', prorettype => 'pg_lsn',
proargtypes => 'pg_lsn pg_lsn', prosrc => 'pg_lsn_smaller' },
+{ oid => '4198',
+ proname => 'pg_lsn_pli', prorettype => 'pg_lsn',
+ proargtypes => 'pg_lsn int8', prosrc => 'pg_lsn_pli' },
+{ oid => '4199',
+ proname => 'pg_lsn_mii', prorettype => 'pg_lsn',
+ proargtypes => 'pg_lsn int8', prosrc => 'pg_lsn_mii' },
# 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 64d41dfdad..175b5dc9a7 100644
--- a/src/test/regress/expected/pg_lsn.out
+++ b/src/test/regress/expected/pg_lsn.out
@@ -71,6 +71,34 @@ SELECT '0/16AE7F8'::pg_lsn - '0/16AE7F7'::pg_lsn;
1
(1 row)
+SELECT '0/16AE7F7'::pg_lsn + 16::bigint;
+ ?column?
+-----------
+ 0/16AE807
+(1 row)
+
+SELECT '0/16AE7F7'::pg_lsn - 16::bigint;
+ ?column?
+-----------
+ 0/16AE7E7
+(1 row)
+
+SELECT 'FFFFFFFF/FFFFFFFE'::pg_lsn + 1::bigint;
+ ?column?
+-------------------
+ FFFFFFFF/FFFFFFFF
+(1 row)
+
+SELECT 'FFFFFFFF/FFFFFFFE'::pg_lsn + 2::bigint; -- out of range error
+ERROR: pg_lsn out of range
+SELECT '0/1'::pg_lsn - 1::bigint;
+ ?column?
+----------
+ 0/0
+(1 row)
+
+SELECT '0/1'::pg_lsn - 2::bigint; -- out of range error
+ERROR: pg_lsn out of range
-- Check btree and hash opclasses
EXPLAIN (COSTS OFF)
SELECT DISTINCT (i || '/' || j)::pg_lsn f
diff --git a/src/test/regress/sql/pg_lsn.sql b/src/test/regress/sql/pg_lsn.sql
index 2c143c82ff..d64ac852c4 100644
--- a/src/test/regress/sql/pg_lsn.sql
+++ b/src/test/regress/sql/pg_lsn.sql
@@ -27,6 +27,12 @@ SELECT '0/16AE7F7' < '0/16AE7F8'::pg_lsn;
SELECT '0/16AE7F8' > pg_lsn '0/16AE7F7';
SELECT '0/16AE7F7'::pg_lsn - '0/16AE7F8'::pg_lsn;
SELECT '0/16AE7F8'::pg_lsn - '0/16AE7F7'::pg_lsn;
+SELECT '0/16AE7F7'::pg_lsn + 16::bigint;
+SELECT '0/16AE7F7'::pg_lsn - 16::bigint;
+SELECT 'FFFFFFFF/FFFFFFFE'::pg_lsn + 1::bigint;
+SELECT 'FFFFFFFF/FFFFFFFE'::pg_lsn + 2::bigint; -- out of range error
+SELECT '0/1'::pg_lsn - 1::bigint;
+SELECT '0/1'::pg_lsn - 2::bigint; -- out of range error
-- Check btree and hash opclasses
EXPLAIN (COSTS OFF)
