Add btree and hash opclasses for pg_lsn.

This is needed to allow ORDER BY, DISTINCT, etc to work as expected for
pg_lsn values.

We had previously decided to put this off for 9.5, but in view of commit
eeca4cd35e284c72b2ea1b4494e64e7738896e81 there's no reason to avoid a
catversion bump for 9.4beta2, and this does make a pretty significant
usability difference for pg_lsn.

Michael Paquier, with fixes from Andres Freund and Tom Lane

diff --git a/src/backend/utils/adt/pg_lsn.c b/src/backend/utils/adt/pg_lsn.c
index d1448aee7bd9d5ef14bd6b2ae8071c7d2a0b3f66..aa0f690412fe0f7515c000f0978dcde54935ad7f 100644 (file)
--- a/src/backend/utils/adt/pg_lsn.c
+++ b/src/backend/utils/adt/pg_lsn.c
@@ -1,9 +1,9 @@
 /*-------------------------------------------------------------------------
  *
  * pg_lsn.c
- *   Internal PostgreSQL LSN operations
+ *   Operations for the pg_lsn datatype.
  *
- * Portions Copyright (c) 1996-2011, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1996-2014, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
  * IDENTIFICATION
@@ -13,6 +13,7 @@
  */
 #include "postgres.h"
 
+#include "access/hash.h"
 #include "funcapi.h"
 #include "libpq/pqformat.h"
 #include "utils/builtins.h"
@@ -153,6 +154,29 @@ pg_lsn_ge(PG_FUNCTION_ARGS)
    PG_RETURN_BOOL(lsn1 >= lsn2);
 }
 
+/* btree index opclass support */
+Datum
+pg_lsn_cmp(PG_FUNCTION_ARGS)
+{
+   XLogRecPtr  a = PG_GETARG_LSN(0);
+   XLogRecPtr  b = PG_GETARG_LSN(1);
+
+   if (a > b)
+       PG_RETURN_INT32(1);
+   else if (a == b)
+       PG_RETURN_INT32(0);
+   else
+       PG_RETURN_INT32(-1);
+}
+
+/* hash index opclass support */
+Datum
+pg_lsn_hash(PG_FUNCTION_ARGS)
+{
+   /* We can use hashint8 directly */
+   return hashint8(fcinfo);
+}
+
 
 /*----------------------------------------------------------
  * Arithmetic operators on PostgreSQL LSNs.

diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index c46a92b82f73eb8a3194de23079048a805cbbf2f..c79d20bb3d7df97dda87c3a28c3dbdc1ca75ac00 100644 (file)
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -53,6 +53,6 @@
  */
 
 /*                         yyyymmddN */
-#define CATALOG_VERSION_NO 201405111
+#define CATALOG_VERSION_NO 201406041
 
 #endif

diff --git a/src/include/catalog/pg_amop.h b/src/include/catalog/pg_amop.h
index 264059f17e01f9fb75076564762c5f9efc3751bc..3ef5a49cc9bd67245690f822d82bcc8a8f92d667 100644 (file)
--- a/src/include/catalog/pg_amop.h
+++ b/src/include/catalog/pg_amop.h
@@ -512,6 +512,16 @@ DATA(insert (  2968  2950 2950 3 s 2972    403 0 ));
 DATA(insert (  2968  2950 2950 4 s 2977    403 0 ));
 DATA(insert (  2968  2950 2950 5 s 2975    403 0 ));
 
+/*
+ * btree pg_lsn_ops
+ */
+
+DATA(insert (  3253  3220 3220 1 s 3224    403 0 ));
+DATA(insert (  3253  3220 3220 2 s 3226    403 0 ));
+DATA(insert (  3253  3220 3220 3 s 3222    403 0 ));
+DATA(insert (  3253  3220 3220 4 s 3227    403 0 ));
+DATA(insert (  3253  3220 3220 5 s 3225    403 0 ));
+
 /*
  * hash index _ops
  */
@@ -581,6 +591,8 @@ DATA(insert (   2231   1042 1042 1 s 1054 405 0 ));
 DATA(insert (  2235   1033 1033 1 s  974 405 0 ));
 /* uuid_ops */
 DATA(insert (  2969   2950 2950 1 s 2972 405 0 ));
+/* pg_lsn_ops */
+DATA(insert (  3254   3220 3220 1 s 3222 405 0 ));
 /* numeric_ops */
 DATA(insert (  1998   1700 1700 1 s 1752 405 0 ));
 /* array_ops */

diff --git a/src/include/catalog/pg_amproc.h b/src/include/catalog/pg_amproc.h
index 198b126964f8f0d33912e3bd2bfa34d31d4da6e1..10a47df6be6cdb3c44f129f3c1a68adc9bdde7e0 100644 (file)
--- a/src/include/catalog/pg_amproc.h
+++ b/src/include/catalog/pg_amproc.h
@@ -134,6 +134,7 @@ DATA(insert (   2789   27 27 1 2794 ));
 DATA(insert (  2968   2950 2950 1 2960 ));
 DATA(insert (  2994   2249 2249 1 2987 ));
 DATA(insert (  3194   2249 2249 1 3187 ));
+DATA(insert (  3253   3220 3220 1 3251 ));
 DATA(insert (  3522   3500 3500 1 3514 ));
 DATA(insert (  3626   3614 3614 1 3622 ));
 DATA(insert (  3683   3615 3615 1 3668 ));
@@ -174,6 +175,7 @@ DATA(insert (   2229   25 25 1 400 ));
 DATA(insert (  2231   1042 1042 1 1080 ));
 DATA(insert (  2235   1033 1033 1 329 ));
 DATA(insert (  2969   2950 2950 1 2963 ));
+DATA(insert (  3254   3220 3220 1 3252 ));
 DATA(insert (  3523   3500 3500 1 3515 ));
 DATA(insert (  3903   3831 3831 1 3902 ));
 DATA(insert (  4034   3802 3802 1 4045 ));

diff --git a/src/include/catalog/pg_opclass.h b/src/include/catalog/pg_opclass.h
index 369888665fe9041833b0e4bc9e86b02dc0b5bc09..dc523416c9251c5badf3379a79b3cb80d92f69ad 100644 (file)
--- a/src/include/catalog/pg_opclass.h
+++ b/src/include/catalog/pg_opclass.h
@@ -215,6 +215,8 @@ DATA(insert (   2742    _reltime_ops        PGNSP PGUID 2745  1024 t 703 ));
 DATA(insert (  2742    _tinterval_ops      PGNSP PGUID 2745  1025 t 704 ));
 DATA(insert (  403     uuid_ops            PGNSP PGUID 2968  2950 t 0 ));
 DATA(insert (  405     uuid_ops            PGNSP PGUID 2969  2950 t 0 ));
+DATA(insert (  403     pg_lsn_ops          PGNSP PGUID 3253  3220 t 0 ));
+DATA(insert (  405     pg_lsn_ops          PGNSP PGUID 3254  3220 t 0 ));
 DATA(insert (  403     enum_ops            PGNSP PGUID 3522  3500 t 0 ));
 DATA(insert (  405     enum_ops            PGNSP PGUID 3523  3500 t 0 ));
 DATA(insert (  403     tsvector_ops        PGNSP PGUID 3626  3614 t 0 ));

diff --git a/src/include/catalog/pg_operator.h b/src/include/catalog/pg_operator.h
index f280af441c27723dd177b446d0469eb51e4955e8..87ee4eb85201b6298a99422f0b04c156451ab12f 100644 (file)
--- a/src/include/catalog/pg_operator.h
+++ b/src/include/catalog/pg_operator.h
@@ -1595,7 +1595,7 @@ DATA(insert OID = 2977 (  ">="       PGNSP PGUID b f f 2950 2950 16 2976 2974 uuid_
 DESCR("greater than or equal");
 
 /* pg_lsn operators */
-DATA(insert OID = 3222 (  "="     PGNSP PGUID b f f 3220 3220 16 3222 3223 pg_lsn_eq eqsel eqjoinsel ));
+DATA(insert OID = 3222 (  "="     PGNSP PGUID b t t 3220 3220 16 3222 3223 pg_lsn_eq eqsel eqjoinsel ));
 DESCR("equal");
 DATA(insert OID = 3223 (  "<>"    PGNSP PGUID b f f 3220 3220 16 3223 3222 pg_lsn_ne neqsel neqjoinsel ));
 DESCR("not equal");

diff --git a/src/include/catalog/pg_opfamily.h b/src/include/catalog/pg_opfamily.h
index c83ac8c1a44be14fa7eaece1a8c22a675edfbf14..26297ced0da8f821e894b746abdaa6dc06f72f20 100644 (file)
--- a/src/include/catalog/pg_opfamily.h
+++ b/src/include/catalog/pg_opfamily.h
@@ -134,6 +134,8 @@ DATA(insert OID = 1029 (    783     point_ops       PGNSP PGUID ));
 DATA(insert OID = 2745 (   2742    array_ops       PGNSP PGUID ));
 DATA(insert OID = 2968 (   403     uuid_ops        PGNSP PGUID ));
 DATA(insert OID = 2969 (   405     uuid_ops        PGNSP PGUID ));
+DATA(insert OID = 3253 (   403     pg_lsn_ops      PGNSP PGUID ));
+DATA(insert OID = 3254 (   405     pg_lsn_ops      PGNSP PGUID ));
 DATA(insert OID = 3522 (   403     enum_ops        PGNSP PGUID ));
 DATA(insert OID = 3523 (   405     enum_ops        PGNSP PGUID ));
 DATA(insert OID = 3626 (   403     tsvector_ops    PGNSP PGUID ));

diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 72170af0e840bf5067456552bffb1b3de5484ca5..58c9d2b1d3f7e0db5b2d556a43572fbcb31ae745 100644 (file)
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -4293,6 +4293,10 @@ DATA(insert OID = 3238 (  pg_lsn_recv    PGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 3
 DESCR("I/O");
 DATA(insert OID = 3239 (  pg_lsn_send  PGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 17 "3220" _null_ _null_ _null_ _null_ pg_lsn_send _null_ _null_ _null_ ));
 DESCR("I/O");
+DATA(insert OID = 3251 (  pg_lsn_cmp   PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 23 "3220 3220" _null_ _null_ _null_ _null_ pg_lsn_cmp _null_ _null_ _null_ ));
+DESCR("less-equal-greater");
+DATA(insert OID = 3252 (  pg_lsn_hash  PGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 23 "3220" _null_ _null_ _null_ _null_ pg_lsn_hash _null_ _null_ _null_ ));
+DESCR("hash");
 
 /* enum related procs */
 DATA(insert OID = 3504 (  anyenum_in   PGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 3500 "2275" _null_ _null_ _null_ _null_ anyenum_in _null_ _null_ _null_ ));

diff --git a/src/include/utils/pg_lsn.h b/src/include/utils/pg_lsn.h
index 981fcd6fa8c30bc584bca850025b6ccc40bbc561..7dd932d01b20dd4f64daaf048a7d9899e37668a7 100644 (file)
--- a/src/include/utils/pg_lsn.h
+++ b/src/include/utils/pg_lsn.h
@@ -29,6 +29,8 @@ extern Datum pg_lsn_lt(PG_FUNCTION_ARGS);
 extern Datum pg_lsn_gt(PG_FUNCTION_ARGS);
 extern Datum pg_lsn_le(PG_FUNCTION_ARGS);
 extern Datum pg_lsn_ge(PG_FUNCTION_ARGS);
+extern Datum pg_lsn_cmp(PG_FUNCTION_ARGS);
+extern Datum pg_lsn_hash(PG_FUNCTION_ARGS);
 
 extern Datum pg_lsn_mi(PG_FUNCTION_ARGS);
 

diff --git a/src/test/regress/expected/pg_lsn.out b/src/test/regress/expected/pg_lsn.out
index 504768c203875a2da607b1b435bf2bc96b37a7cf..493eb9ea84083f9a042c93bb98ef79f53b563ff7 100644 (file)
--- a/src/test/regress/expected/pg_lsn.out
+++ b/src/test/regress/expected/pg_lsn.out
@@ -64,3 +64,133 @@ SELECT '0/16AE7F8'::pg_lsn - '0/16AE7F7'::pg_lsn;
         1
 (1 row)
 
+-- Check btree and hash opclasses
+EXPLAIN (COSTS OFF)
+SELECT DISTINCT (i || '/' || j)::pg_lsn f
+  FROM generate_series(1, 10) i,
+       generate_series(1, 10) j,
+       generate_series(1, 5) k
+  ORDER BY f;
+                                QUERY PLAN                                
+--------------------------------------------------------------------------
+ Sort
+   Sort Key: (((((i.i)::text || '/'::text) || (j.j)::text))::pg_lsn)
+   ->  HashAggregate
+         Group Key: ((((i.i)::text || '/'::text) || (j.j)::text))::pg_lsn
+         ->  Nested Loop
+               ->  Function Scan on generate_series k
+               ->  Materialize
+                     ->  Nested Loop
+                           ->  Function Scan on generate_series i
+                           ->  Function Scan on generate_series j
+(10 rows)
+
+SELECT DISTINCT (i || '/' || j)::pg_lsn f
+  FROM generate_series(1, 10) i,
+       generate_series(1, 10) j,
+       generate_series(1, 5) k
+  ORDER BY f;
+   f   
+-------
+ 1/1
+ 1/2
+ 1/3
+ 1/4
+ 1/5
+ 1/6
+ 1/7
+ 1/8
+ 1/9
+ 1/10
+ 2/1
+ 2/2
+ 2/3
+ 2/4
+ 2/5
+ 2/6
+ 2/7
+ 2/8
+ 2/9
+ 2/10
+ 3/1
+ 3/2
+ 3/3
+ 3/4
+ 3/5
+ 3/6
+ 3/7
+ 3/8
+ 3/9
+ 3/10
+ 4/1
+ 4/2
+ 4/3
+ 4/4
+ 4/5
+ 4/6
+ 4/7
+ 4/8
+ 4/9
+ 4/10
+ 5/1
+ 5/2
+ 5/3
+ 5/4
+ 5/5
+ 5/6
+ 5/7
+ 5/8
+ 5/9
+ 5/10
+ 6/1
+ 6/2
+ 6/3
+ 6/4
+ 6/5
+ 6/6
+ 6/7
+ 6/8
+ 6/9
+ 6/10
+ 7/1
+ 7/2
+ 7/3
+ 7/4
+ 7/5
+ 7/6
+ 7/7
+ 7/8
+ 7/9
+ 7/10
+ 8/1
+ 8/2
+ 8/3
+ 8/4
+ 8/5
+ 8/6
+ 8/7
+ 8/8
+ 8/9
+ 8/10
+ 9/1
+ 9/2
+ 9/3
+ 9/4
+ 9/5
+ 9/6
+ 9/7
+ 9/8
+ 9/9
+ 9/10
+ 10/1
+ 10/2
+ 10/3
+ 10/4
+ 10/5
+ 10/6
+ 10/7
+ 10/8
+ 10/9
+ 10/10
+(100 rows)
+

diff --git a/src/test/regress/sql/pg_lsn.sql b/src/test/regress/sql/pg_lsn.sql
index 1634d37f7010357124ce3d4ae7fde65e26668a7b..a3da0f15796b4acbad3ba8579e4af7933544a10d 100644 (file)
--- a/src/test/regress/sql/pg_lsn.sql
+++ b/src/test/regress/sql/pg_lsn.sql
@@ -23,3 +23,17 @@ 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;
+
+-- Check btree and hash opclasses
+EXPLAIN (COSTS OFF)
+SELECT DISTINCT (i || '/' || j)::pg_lsn f
+  FROM generate_series(1, 10) i,
+       generate_series(1, 10) j,
+       generate_series(1, 5) k
+  ORDER BY f;
+
+SELECT DISTINCT (i || '/' || j)::pg_lsn f
+  FROM generate_series(1, 10) i,
+       generate_series(1, 10) j,
+       generate_series(1, 5) k
+  ORDER BY f;