--
-- 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');
-- Incorrect input
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');
                                       ^
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');
                                       ^
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');
                                       ^
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/');
                                       ^
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');
                                       ^
-- Also try it with non-error-throwing API
SELECT pg_input_is_valid('16AE7F7', 'pg_lsn');
 pg_input_is_valid 
-------------------
 f
(1 row)

SELECT * FROM pg_input_error_info('16AE7F7', 'pg_lsn');
                     message                     | detail | hint | sql_error_code 
-------------------------------------------------+--------+------+----------------
 invalid input syntax for type pg_lsn: "16AE7F7" |        |      | 22P02
(1 row)

-- Min/Max aggregation
SELECT MIN(f1), MAX(f1) FROM PG_LSN_TBL;
    min     |        max        
------------+-------------------
 0/00000000 | FFFFFFFF/FFFFFFFF
(1 row)

DROP TABLE PG_LSN_TBL;
-- Operators
SELECT '0/16AE7F8' = '0/16AE7F8'::pg_lsn;
 ?column? 
----------
 t
(1 row)

SELECT '0/16AE7F8'::pg_lsn != '0/16AE7F7';
 ?column? 
----------
 t
(1 row)

SELECT '0/16AE7F7' < '0/16AE7F8'::pg_lsn;
 ?column? 
----------
 t
(1 row)

SELECT '0/16AE7F8' > pg_lsn '0/16AE7F7';
 ?column? 
----------
 t
(1 row)

SELECT '0/16AE7F7'::pg_lsn - '0/16AE7F8'::pg_lsn;
 ?column? 
----------
       -1
(1 row)

SELECT '0/16AE7F8'::pg_lsn - '0/16AE7F7'::pg_lsn;
 ?column? 
----------
        1
(1 row)

SELECT '0/16AE7F7'::pg_lsn + 16::numeric;
  ?column?  
------------
 0/016AE807
(1 row)

SELECT 16::numeric + '0/16AE7F7'::pg_lsn;
  ?column?  
------------
 0/016AE807
(1 row)

SELECT '0/16AE7F7'::pg_lsn - 16::numeric;
  ?column?  
------------
 0/016AE7E7
(1 row)

SELECT 'FFFFFFFF/FFFFFFFE'::pg_lsn + 1::numeric;
     ?column?      
-------------------
 FFFFFFFF/FFFFFFFF
(1 row)

SELECT 'FFFFFFFF/FFFFFFFE'::pg_lsn + 2::numeric; -- out of range error
ERROR:  pg_lsn out of range
SELECT '0/1'::pg_lsn - 1::numeric;
  ?column?  
------------
 0/00000000
(1 row)

SELECT '0/1'::pg_lsn - 2::numeric; -- out of range error
ERROR:  pg_lsn out of range
SELECT '0/0'::pg_lsn + ('FFFFFFFF/FFFFFFFF'::pg_lsn - '0/0'::pg_lsn);
     ?column?      
-------------------
 FFFFFFFF/FFFFFFFF
(1 row)

SELECT 'FFFFFFFF/FFFFFFFF'::pg_lsn - ('FFFFFFFF/FFFFFFFF'::pg_lsn - '0/0'::pg_lsn);
  ?column?  
------------
 0/00000000
(1 row)

SELECT '0/16AE7F7'::pg_lsn + 'NaN'::numeric;
ERROR:  cannot add NaN to pg_lsn
SELECT '0/16AE7F7'::pg_lsn - 'NaN'::numeric;
ERROR:  cannot subtract NaN from 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
  WHERE i <= 10 AND j > 0 AND j <= 10
  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 j
                                 Filter: ((j > 0) AND (j <= 10))
                           ->  Function Scan on generate_series i
                                 Filter: (i <= 10)
(12 rows)

SELECT DISTINCT (i || '/' || j)::pg_lsn f
  FROM generate_series(1, 10) i,
       generate_series(1, 10) j,
       generate_series(1, 5) k
  WHERE i <= 10 AND j > 0 AND j <= 10
  ORDER BY f;
      f      
-------------
 1/00000001
 1/00000002
 1/00000003
 1/00000004
 1/00000005
 1/00000006
 1/00000007
 1/00000008
 1/00000009
 1/00000010
 2/00000001
 2/00000002
 2/00000003
 2/00000004
 2/00000005
 2/00000006
 2/00000007
 2/00000008
 2/00000009
 2/00000010
 3/00000001
 3/00000002
 3/00000003
 3/00000004
 3/00000005
 3/00000006
 3/00000007
 3/00000008
 3/00000009
 3/00000010
 4/00000001
 4/00000002
 4/00000003
 4/00000004
 4/00000005
 4/00000006
 4/00000007
 4/00000008
 4/00000009
 4/00000010
 5/00000001
 5/00000002
 5/00000003
 5/00000004
 5/00000005
 5/00000006
 5/00000007
 5/00000008
 5/00000009
 5/00000010
 6/00000001
 6/00000002
 6/00000003
 6/00000004
 6/00000005
 6/00000006
 6/00000007
 6/00000008
 6/00000009
 6/00000010
 7/00000001
 7/00000002
 7/00000003
 7/00000004
 7/00000005
 7/00000006
 7/00000007
 7/00000008
 7/00000009
 7/00000010
 8/00000001
 8/00000002
 8/00000003
 8/00000004
 8/00000005
 8/00000006
 8/00000007
 8/00000008
 8/00000009
 8/00000010
 9/00000001
 9/00000002
 9/00000003
 9/00000004
 9/00000005
 9/00000006
 9/00000007
 9/00000008
 9/00000009
 9/00000010
 10/00000001
 10/00000002
 10/00000003
 10/00000004
 10/00000005
 10/00000006
 10/00000007
 10/00000008
 10/00000009
 10/00000010
(100 rows)