1--
2-- PG_LSN
3--
4CREATE TABLE PG_LSN_TBL (f1 pg_lsn);
5-- Largest and smallest input
6INSERT INTO PG_LSN_TBL VALUES ('0/0');
7INSERT INTO PG_LSN_TBL VALUES ('FFFFFFFF/FFFFFFFF');
8-- Incorrect input
9INSERT INTO PG_LSN_TBL VALUES ('G/0');
10ERROR:  invalid input syntax for type pg_lsn: "G/0"
11LINE 1: INSERT INTO PG_LSN_TBL VALUES ('G/0');
12                                       ^
13INSERT INTO PG_LSN_TBL VALUES ('-1/0');
14ERROR:  invalid input syntax for type pg_lsn: "-1/0"
15LINE 1: INSERT INTO PG_LSN_TBL VALUES ('-1/0');
16                                       ^
17INSERT INTO PG_LSN_TBL VALUES (' 0/12345678');
18ERROR:  invalid input syntax for type pg_lsn: " 0/12345678"
19LINE 1: INSERT INTO PG_LSN_TBL VALUES (' 0/12345678');
20                                       ^
21INSERT INTO PG_LSN_TBL VALUES ('ABCD/');
22ERROR:  invalid input syntax for type pg_lsn: "ABCD/"
23LINE 1: INSERT INTO PG_LSN_TBL VALUES ('ABCD/');
24                                       ^
25INSERT INTO PG_LSN_TBL VALUES ('/ABCD');
26ERROR:  invalid input syntax for type pg_lsn: "/ABCD"
27LINE 1: INSERT INTO PG_LSN_TBL VALUES ('/ABCD');
28                                       ^
29DROP TABLE PG_LSN_TBL;
30-- Operators
31SELECT '0/16AE7F8' = '0/16AE7F8'::pg_lsn;
32 ?column?
33----------
34 t
35(1 row)
36
37SELECT '0/16AE7F8'::pg_lsn != '0/16AE7F7';
38 ?column?
39----------
40 t
41(1 row)
42
43SELECT '0/16AE7F7' < '0/16AE7F8'::pg_lsn;
44 ?column?
45----------
46 t
47(1 row)
48
49SELECT '0/16AE7F8' > pg_lsn '0/16AE7F7';
50 ?column?
51----------
52 t
53(1 row)
54
55SELECT '0/16AE7F7'::pg_lsn - '0/16AE7F8'::pg_lsn;
56 ?column?
57----------
58       -1
59(1 row)
60
61SELECT '0/16AE7F8'::pg_lsn - '0/16AE7F7'::pg_lsn;
62 ?column?
63----------
64        1
65(1 row)
66
67-- Check btree and hash opclasses
68EXPLAIN (COSTS OFF)
69SELECT DISTINCT (i || '/' || j)::pg_lsn f
70  FROM generate_series(1, 10) i,
71       generate_series(1, 10) j,
72       generate_series(1, 5) k
73  WHERE i <= 10 AND j > 0 AND j <= 10
74  ORDER BY f;
75                                QUERY PLAN
76--------------------------------------------------------------------------
77 Sort
78   Sort Key: (((((i.i)::text || '/'::text) || (j.j)::text))::pg_lsn)
79   ->  HashAggregate
80         Group Key: ((((i.i)::text || '/'::text) || (j.j)::text))::pg_lsn
81         ->  Nested Loop
82               ->  Function Scan on generate_series k
83               ->  Materialize
84                     ->  Nested Loop
85                           ->  Function Scan on generate_series j
86                                 Filter: ((j > 0) AND (j <= 10))
87                           ->  Function Scan on generate_series i
88                                 Filter: (i <= 10)
89(12 rows)
90
91SELECT DISTINCT (i || '/' || j)::pg_lsn f
92  FROM generate_series(1, 10) i,
93       generate_series(1, 10) j,
94       generate_series(1, 5) k
95  WHERE i <= 10 AND j > 0 AND j <= 10
96  ORDER BY f;
97   f
98-------
99 1/1
100 1/2
101 1/3
102 1/4
103 1/5
104 1/6
105 1/7
106 1/8
107 1/9
108 1/10
109 2/1
110 2/2
111 2/3
112 2/4
113 2/5
114 2/6
115 2/7
116 2/8
117 2/9
118 2/10
119 3/1
120 3/2
121 3/3
122 3/4
123 3/5
124 3/6
125 3/7
126 3/8
127 3/9
128 3/10
129 4/1
130 4/2
131 4/3
132 4/4
133 4/5
134 4/6
135 4/7
136 4/8
137 4/9
138 4/10
139 5/1
140 5/2
141 5/3
142 5/4
143 5/5
144 5/6
145 5/7
146 5/8
147 5/9
148 5/10
149 6/1
150 6/2
151 6/3
152 6/4
153 6/5
154 6/6
155 6/7
156 6/8
157 6/9
158 6/10
159 7/1
160 7/2
161 7/3
162 7/4
163 7/5
164 7/6
165 7/7
166 7/8
167 7/9
168 7/10
169 8/1
170 8/2
171 8/3
172 8/4
173 8/5
174 8/6
175 8/7
176 8/8
177 8/9
178 8/10
179 9/1
180 9/2
181 9/3
182 9/4
183 9/5
184 9/6
185 9/7
186 9/8
187 9/9
188 9/10
189 10/1
190 10/2
191 10/3
192 10/4
193 10/5
194 10/6
195 10/7
196 10/8
197 10/9
198 10/10
199(100 rows)
200
201