On Fri, May 9, 2014 at 9:30 PM, Tom Lane <t...@sss.pgh.pa.us> wrote:
>
> =?UTF-8?Q?Fabr=C3=ADzio_de_Royes_Mello?= <fabriziome...@gmail.com> writes:
> > On Fri, May 9, 2014 at 8:42 PM, Tom Lane <t...@sss.pgh.pa.us> wrote:
> >> I think it's really too late for this for 9.4. At this point it's
> >> less than 48 hours until beta1 wraps, and we do not have the bandwidth
> >> to do anything but worry about stabilizing the features we've already
> >> got.
>
> > But it's a very small change with many benefits, and Michael acted very
> > proactive to make this happens.
>
> [ shrug... ] "proactive" would have been doing this a month ago.
> If we're going to ship a release, we have to stop taking new features
> at some point, and we are really past that point for 9.4.
>
> And, to be blunt, this is not important enough to hold up the release
> for, nor to take any stability risks for. It should go into the next
> commitfest cycle where it can get a non-rushed review.
>
I agree with you that is too late to add *new features*.
But I agree with Andres when he said this is a regression introcuced in the
pg_lsn patch.
So we'll release a version that break a simple query like that:
fabrizio=# SELECT DISTINCT (g.i||'/0')::pg_lsn f FROM generate_series(1,
100) g(i), generate_series(1, 5);
ERROR: could not identify an equality operator for type pg_lsn
LINE 1: SELECT DISTINCT (g.i||'/0')::pg_lsn f FROM generate_series(1...
^
I attached the last version of this fix with the Andres and Fujii
suggestions.
Regards,
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Timbira: http://www.timbira.com.br
>> Blog sobre TI: http://fabriziomello.blogspot.com
>> Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello
diff --git a/src/backend/utils/adt/pg_lsn.c b/src/backend/utils/adt/pg_lsn.c
index d1448ae..c021a1e 100644
--- a/src/backend/utils/adt/pg_lsn.c
+++ b/src/backend/utils/adt/pg_lsn.c
@@ -14,6 +14,7 @@
#include "postgres.h"
#include "funcapi.h"
+#include "access/hash.h"
#include "libpq/pqformat.h"
#include "utils/builtins.h"
#include "utils/pg_lsn.h"
@@ -153,6 +154,27 @@ pg_lsn_ge(PG_FUNCTION_ARGS)
PG_RETURN_BOOL(lsn1 >= lsn2);
}
+/* handler for btree index operator */
+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 support */
+Datum
+pg_lsn_hash(PG_FUNCTION_ARGS)
+{
+ return DirectFunctionCall1(hashint8, PG_GETARG_LSN(0));
+}
/*----------------------------------------------------------
* Arithmetic operators on PostgreSQL LSNs.
diff --git a/src/include/catalog/pg_amop.h b/src/include/catalog/pg_amop.h
index 8efd3be..35fd17e 100644
--- a/src/include/catalog/pg_amop.h
+++ b/src/include/catalog/pg_amop.h
@@ -513,6 +513,16 @@ 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 ( 3260 3220 3220 1 s 3224 403 0 ));
+DATA(insert ( 3260 3220 3220 2 s 3226 403 0 ));
+DATA(insert ( 3260 3220 3220 3 s 3222 403 0 ));
+DATA(insert ( 3260 3220 3220 4 s 3227 403 0 ));
+DATA(insert ( 3260 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 ( 3261 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 198b126..369adb9 100644
--- 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 ( 3260 3220 3220 1 3263 ));
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 ( 3261 3220 3220 1 3262 ));
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 ecf7063..2298a83 100644
--- 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 3260 3220 t 0 ));
+DATA(insert ( 405 pg_lsn_ops PGNSP PGUID 3261 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 f280af4..87ee4eb 100644
--- 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 9e8f4ac..84f66ac 100644
--- 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 = 3260 ( 403 pg_lsn_ops PGNSP PGUID ));
+DATA(insert OID = 3261 ( 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 e601ccd..2fbc8f1 100644
--- 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 = 3262 ( 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");
+DATA(insert OID = 3263 ( 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");
/* 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 981fcd6..7dd932d 100644
--- 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 504768c..8421e15 100644
--- a/src/test/regress/expected/pg_lsn.out
+++ b/src/test/regress/expected/pg_lsn.out
@@ -1,32 +1,31 @@
--
-- PG_LSN
--
-CREATE TABLE PG_LSN_TBL (f1 pg_lsn);
+CREATE TABLE pg_lsn_tbl (f1 pg_lsn);
-- Largest and smallest input
-INSERT INTO PG_LSN_TBL VALUES ('0/0');
-INSERT INTO PG_LSN_TBL VALUES ('FFFFFFFF/FFFFFFFF');
+INSERT INTO pg_lsn_tbl VALUES ('0/0');
+INSERT INTO pg_lsn_tbl VALUES ('FFFFFFFF/FFFFFFFF');
-- Incorrect input
-INSERT INTO PG_LSN_TBL VALUES ('G/0');
+INSERT INTO pg_lsn_tbl VALUES ('G/0');
ERROR: invalid input syntax for type pg_lsn: "G/0"
-LINE 1: INSERT INTO PG_LSN_TBL VALUES ('G/0');
+LINE 1: INSERT INTO pg_lsn_tbl VALUES ('G/0');
^
-INSERT INTO PG_LSN_TBL VALUES ('-1/0');
+INSERT INTO pg_lsn_tbl VALUES ('-1/0');
ERROR: invalid input syntax for type pg_lsn: "-1/0"
-LINE 1: INSERT INTO PG_LSN_TBL VALUES ('-1/0');
+LINE 1: INSERT INTO pg_lsn_tbl VALUES ('-1/0');
^
-INSERT INTO PG_LSN_TBL VALUES (' 0/12345678');
+INSERT INTO pg_lsn_tbl VALUES (' 0/12345678');
ERROR: invalid input syntax for type pg_lsn: " 0/12345678"
-LINE 1: INSERT INTO PG_LSN_TBL VALUES (' 0/12345678');
+LINE 1: INSERT INTO pg_lsn_tbl VALUES (' 0/12345678');
^
-INSERT INTO PG_LSN_TBL VALUES ('ABCD/');
+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/');
+LINE 1: INSERT INTO pg_lsn_tbl VALUES ('ABCD/');
^
-INSERT INTO PG_LSN_TBL VALUES ('/ABCD');
+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');
+LINE 1: INSERT INTO pg_lsn_tbl VALUES ('/ABCD');
^
-DROP TABLE PG_LSN_TBL;
-- Operators
SELECT '0/16AE7F8' = '0/16AE7F8'::pg_lsn;
?column?
@@ -64,3 +63,75 @@ SELECT '0/16AE7F8'::pg_lsn - '0/16AE7F7'::pg_lsn;
1
(1 row)
+-- Operator Class
+TRUNCATE pg_lsn_tbl;
+INSERT INTO pg_lsn_tbl SELECT DISTINCT (g.i||'/0')::pg_lsn f FROM generate_series(1, 5) g(i), generate_series(1, 5);
+SELECT * FROM pg_lsn_tbl ORDER BY 1;
+ f1
+-----
+ 1/0
+ 2/0
+ 3/0
+ 4/0
+ 5/0
+(5 rows)
+
+CREATE INDEX pg_lsn_tbl_1 ON pg_lsn_tbl USING btree (f1);
+CREATE INDEX pg_lsn_tbl_2 ON pg_lsn_tbl USING hash (f1);
+SELECT indexdef FROM pg_indexes WHERE tablename = 'pg_lsn_tbl' ORDER BY 1;
+ indexdef
+----------------------------------------------------------
+ CREATE INDEX pg_lsn_tbl_1 ON pg_lsn_tbl USING btree (f1)
+ CREATE INDEX pg_lsn_tbl_2 ON pg_lsn_tbl USING hash (f1)
+(2 rows)
+
+DROP TABLE pg_lsn_tbl;
+EXPLAIN (COSTS OFF) SELECT DISTINCT (g.i||'/0')::pg_lsn f FROM generate_series(1, 100) g(i), generate_series(1, 5);
+ QUERY PLAN
+----------------------------------------------------
+ HashAggregate
+ Group Key: (((g.i)::text || '/0'::text))::pg_lsn
+ -> Nested Loop
+ -> Function Scan on generate_series g
+ -> Function Scan on generate_series
+(5 rows)
+
+EXPLAIN (COSTS OFF) SELECT (g.i||'/0')::pg_lsn f FROM generate_series(1, 100) g(i) ORDER BY f;
+ QUERY PLAN
+---------------------------------------------------
+ Sort
+ Sort Key: ((((i)::text || '/0'::text))::pg_lsn)
+ -> Function Scan on generate_series g
+(3 rows)
+
+EXPLAIN (COSTS OFF) SELECT (g.i||'/0')::pg_lsn, count(*) FROM generate_series(1, 100) g(i), generate_series(1, 5) GROUP BY 1 ORDER BY 1;
+ QUERY PLAN
+----------------------------------------------------------
+ Sort
+ Sort Key: ((((g.i)::text || '/0'::text))::pg_lsn)
+ -> HashAggregate
+ Group Key: (((g.i)::text || '/0'::text))::pg_lsn
+ -> Nested Loop
+ -> Function Scan on generate_series g
+ -> Function Scan on generate_series
+(7 rows)
+
+EXPLAIN (COSTS OFF) SELECT * FROM (SELECT (g.i||'/0')::pg_lsn lsn FROM generate_series(1, 10) g(i) ORDER BY g.i) a JOIN (SELECT (g.i||'/0')::pg_lsn lsn FROM generate_series(1, 10) g(i) ORDER BY g.i DESC) b ON (a.lsn = b.lsn );
+ QUERY PLAN
+--------------------------------------------------------------
+ Merge Join
+ Merge Cond: (a.lsn = b.lsn)
+ -> Sort
+ Sort Key: a.lsn
+ -> Subquery Scan on a
+ -> Sort
+ Sort Key: g.i
+ -> Function Scan on generate_series g
+ -> Sort
+ Sort Key: b.lsn
+ -> Subquery Scan on b
+ -> Sort
+ Sort Key: g_1.i
+ -> Function Scan on generate_series g_1
+(14 rows)
+
diff --git a/src/test/regress/sql/pg_lsn.sql b/src/test/regress/sql/pg_lsn.sql
index 1634d37..8ff302e 100644
--- a/src/test/regress/sql/pg_lsn.sql
+++ b/src/test/regress/sql/pg_lsn.sql
@@ -2,19 +2,18 @@
-- PG_LSN
--
-CREATE TABLE PG_LSN_TBL (f1 pg_lsn);
+CREATE TABLE pg_lsn_tbl (f1 pg_lsn);
-- Largest and smallest input
-INSERT INTO PG_LSN_TBL VALUES ('0/0');
-INSERT INTO PG_LSN_TBL VALUES ('FFFFFFFF/FFFFFFFF');
+INSERT INTO pg_lsn_tbl VALUES ('0/0');
+INSERT INTO pg_lsn_tbl VALUES ('FFFFFFFF/FFFFFFFF');
-- Incorrect input
-INSERT INTO PG_LSN_TBL VALUES ('G/0');
-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');
-DROP TABLE PG_LSN_TBL;
+INSERT INTO pg_lsn_tbl VALUES ('G/0');
+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');
-- Operators
SELECT '0/16AE7F8' = '0/16AE7F8'::pg_lsn;
@@ -23,3 +22,16 @@ 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;
+
+-- Operator Class
+TRUNCATE pg_lsn_tbl;
+INSERT INTO pg_lsn_tbl SELECT DISTINCT (g.i||'/0')::pg_lsn f FROM generate_series(1, 5) g(i), generate_series(1, 5);
+SELECT * FROM pg_lsn_tbl ORDER BY 1;
+CREATE INDEX pg_lsn_tbl_1 ON pg_lsn_tbl USING btree (f1);
+CREATE INDEX pg_lsn_tbl_2 ON pg_lsn_tbl USING hash (f1);
+SELECT indexdef FROM pg_indexes WHERE tablename = 'pg_lsn_tbl' ORDER BY 1;
+DROP TABLE pg_lsn_tbl;
+EXPLAIN (COSTS OFF) SELECT DISTINCT (g.i||'/0')::pg_lsn f FROM generate_series(1, 100) g(i), generate_series(1, 5);
+EXPLAIN (COSTS OFF) SELECT (g.i||'/0')::pg_lsn f FROM generate_series(1, 100) g(i) ORDER BY f;
+EXPLAIN (COSTS OFF) SELECT (g.i||'/0')::pg_lsn, count(*) FROM generate_series(1, 100) g(i), generate_series(1, 5) GROUP BY 1 ORDER BY 1;
+EXPLAIN (COSTS OFF) SELECT * FROM (SELECT (g.i||'/0')::pg_lsn lsn FROM generate_series(1, 10) g(i) ORDER BY g.i) a JOIN (SELECT (g.i||'/0')::pg_lsn lsn FROM generate_series(1, 10) g(i) ORDER BY g.i DESC) b ON (a.lsn = b.lsn );
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
