fdbe4b90c1e6800f4e57b6f2a891d57701b4b6f2
1 -- PostRR - src/postrr.sql
2 -- Copyright (C) 2012 Sebastian 'tokkee' Harl <sh@tokkee.org>
3 -- All rights reserved.
4 --
5 -- Redistribution and use in source and binary forms, with or without
6 -- modification, are permitted provided that the following conditions
7 -- are met:
8 -- 1. Redistributions of source code must retain the above copyright
9 -- notice, this list of conditions and the following disclaimer.
10 -- 2. Redistributions in binary form must reproduce the above copyright
11 -- notice, this list of conditions and the following disclaimer in the
12 -- documentation and/or other materials provided with the distribution.
13 --
14 -- THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
15 -- ``AS IS'' AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED
16 -- TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
17 -- PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDERS OR
18 -- CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
19 -- EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
20 -- PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS;
21 -- OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY,
22 -- WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR
23 -- OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF
24 -- ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
26 --
27 -- PostRR - PostgreSQL Round-Robin Extension
28 --
30 -- complain if script is sourced in psql, rather than via CREATE EXTENSION
31 \echo Use "CREATE EXTENSION postrr" to load this file. \quit
33 CREATE SCHEMA postrr;
35 CREATE SEQUENCE postrr.tsid INCREMENT BY 1
36 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 NO CYCLE;
38 CREATE TABLE postrr.rrtimeslices (
39 tsid integer NOT NULL PRIMARY KEY
40 DEFAULT nextval('postrr.tsid'::regclass)
41 CHECK (0 < tsid),
42 tslen integer NOT NULL,
43 tsnum integer NOT NULL
44 );
46 SELECT pg_catalog.pg_extension_config_dump('postrr.rrtimeslices', '');
48 CREATE TABLE postrr.rrarchives (
49 rraname text NOT NULL,
50 tbl name NOT NULL,
51 tscol name NOT NULL,
52 vcol name NOT NULL,
53 UNIQUE (rraname, tbl, tscol, vcol)
54 );
56 SELECT pg_catalog.pg_extension_config_dump('postrr.rrarchives', '');
58 CREATE OR REPLACE FUNCTION PostRR_Version()
59 RETURNS cstring
60 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'postrr_version'
61 LANGUAGE C IMMUTABLE;
63 CREATE TYPE RRTimeslice;
65 CREATE OR REPLACE FUNCTION RRTimeslice_validate(integer)
66 RETURNS cstring
67 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_validate'
68 LANGUAGE C IMMUTABLE STRICT;
70 -- this will abort the transaction in case the expected internal length does
71 -- not match the actual length
72 SELECT RRTimeslice_validate(16);
74 CREATE OR REPLACE FUNCTION RRTimeslice_in(cstring, oid, integer)
75 RETURNS RRTimeslice
76 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_in'
77 LANGUAGE C IMMUTABLE STRICT;
79 CREATE OR REPLACE FUNCTION RRTimeslice_out(RRTimeslice)
80 RETURNS cstring
81 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_out'
82 LANGUAGE C IMMUTABLE STRICT;
84 CREATE OR REPLACE FUNCTION RRTimeslice_typmodin(cstring[])
85 RETURNS integer
86 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_typmodin'
87 LANGUAGE C IMMUTABLE STRICT;
89 CREATE OR REPLACE FUNCTION RRTimeslice_typmodout(integer)
90 RETURNS cstring
91 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_typmodout'
92 LANGUAGE C IMMUTABLE STRICT;
94 CREATE TYPE RRTimeslice (
95 INTERNALLENGTH = 16,
96 INPUT = RRTimeslice_in,
97 OUTPUT = RRTimeslice_out,
98 TYPMOD_IN = RRTimeslice_typmodin,
99 TYPMOD_OUT = RRTimeslice_typmodout,
100 ALIGNMENT = double,
101 STORAGE = plain
102 );
104 CREATE OR REPLACE FUNCTION RRTimeslice(rrtimeslice, integer, boolean)
105 RETURNS rrtimeslice
106 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_to_rrtimeslice'
107 LANGUAGE C IMMUTABLE STRICT;
109 CREATE CAST (rrtimeslice AS rrtimeslice)
110 WITH FUNCTION RRTimeslice(rrtimeslice, integer, boolean)
111 AS IMPLICIT;
113 CREATE OR REPLACE FUNCTION RRTimeslice(timestamptz, integer, boolean)
114 RETURNS rrtimeslice
115 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'timestamptz_to_rrtimeslice'
116 LANGUAGE C IMMUTABLE STRICT;
118 CREATE CAST (timestamptz AS rrtimeslice)
119 WITH FUNCTION RRTimeslice(timestamptz, integer, boolean)
120 AS IMPLICIT;
122 CREATE OR REPLACE FUNCTION Tstamptz(rrtimeslice)
123 RETURNS timestamptz
124 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_to_timestamptz'
125 LANGUAGE C IMMUTABLE STRICT;
127 CREATE CAST (rrtimeslice AS timestamptz)
128 WITH FUNCTION Tstamptz(rrtimeslice);
129 -- EXPLICIT
131 CREATE OR REPLACE FUNCTION rrtimeslice_cmp(rrtimeslice, rrtimeslice)
132 RETURNS integer
133 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_cmp'
134 LANGUAGE C IMMUTABLE STRICT;
136 CREATE OR REPLACE FUNCTION rrtimeslice_seq_eq(rrtimeslice, rrtimeslice)
137 RETURNS boolean
138 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_seq_eq'
139 LANGUAGE C IMMUTABLE STRICT;
141 CREATE OR REPLACE FUNCTION rrtimeslice_seq_ne(rrtimeslice, rrtimeslice)
142 RETURNS boolean
143 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_seq_ne'
144 LANGUAGE C IMMUTABLE STRICT;
146 CREATE OR REPLACE FUNCTION rrtimeslice_seq_lt(rrtimeslice, rrtimeslice)
147 RETURNS boolean
148 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_seq_lt'
149 LANGUAGE C IMMUTABLE STRICT;
151 CREATE OR REPLACE FUNCTION rrtimeslice_seq_le(rrtimeslice, rrtimeslice)
152 RETURNS boolean
153 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_seq_le'
154 LANGUAGE C IMMUTABLE STRICT;
156 CREATE OR REPLACE FUNCTION rrtimeslice_seq_gt(rrtimeslice, rrtimeslice)
157 RETURNS boolean
158 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_seq_gt'
159 LANGUAGE C IMMUTABLE STRICT;
161 CREATE OR REPLACE FUNCTION rrtimeslice_seq_ge(rrtimeslice, rrtimeslice)
162 RETURNS boolean
163 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_seq_ge'
164 LANGUAGE C IMMUTABLE STRICT;
166 CREATE OR REPLACE FUNCTION rrtimeslice_seq_cmp(rrtimeslice, rrtimeslice)
167 RETURNS integer
168 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_seq_cmp'
169 LANGUAGE C IMMUTABLE STRICT;
171 CREATE OR REPLACE FUNCTION rrtimeslice_seq_hash(rrtimeslice)
172 RETURNS integer
173 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_seq_hash'
174 LANGUAGE C IMMUTABLE STRICT;
176 CREATE OPERATOR = (
177 LEFTARG = RRTimeslice,
178 RIGHTARG = RRTimeslice,
179 PROCEDURE = rrtimeslice_seq_eq,
180 COMMUTATOR = =,
181 NEGATOR = <>,
182 RESTRICT = eqsel
183 );
185 CREATE OPERATOR <> (
186 LEFTARG = RRTimeslice,
187 RIGHTARG = RRTimeslice,
188 PROCEDURE = rrtimeslice_seq_ne,
189 COMMUTATOR = <>,
190 NEGATOR = =,
191 RESTRICT = neqsel
192 );
194 CREATE OPERATOR < (
195 LEFTARG = RRTimeslice,
196 RIGHTARG = RRTimeslice,
197 PROCEDURE = rrtimeslice_seq_lt,
198 COMMUTATOR = >,
199 NEGATOR = <=,
200 RESTRICT = scalarltsel
201 );
203 CREATE OPERATOR <= (
204 LEFTARG = RRTimeslice,
205 RIGHTARG = RRTimeslice,
206 PROCEDURE = rrtimeslice_seq_le,
207 COMMUTATOR = >=,
208 NEGATOR = <,
209 RESTRICT = scalarltsel
210 );
212 CREATE OPERATOR > (
213 LEFTARG = RRTimeslice,
214 RIGHTARG = RRTimeslice,
215 PROCEDURE = rrtimeslice_seq_gt,
216 COMMUTATOR = <,
217 NEGATOR = >=,
218 RESTRICT = scalargtsel
219 );
221 CREATE OPERATOR >= (
222 LEFTARG = RRTimeslice,
223 RIGHTARG = RRTimeslice,
224 PROCEDURE = rrtimeslice_seq_ge,
225 COMMUTATOR = <=,
226 NEGATOR = >,
227 RESTRICT = scalargtsel
228 );
230 CREATE OPERATOR CLASS rrtimeslice_ops
231 DEFAULT FOR TYPE RRTimeslice USING btree AS
232 OPERATOR 1 < ,
233 OPERATOR 2 <= ,
234 OPERATOR 3 = ,
235 OPERATOR 4 >= ,
236 OPERATOR 5 > ,
237 FUNCTION 1 rrtimeslice_seq_cmp(rrtimeslice, rrtimeslice);
239 CREATE OPERATOR CLASS rrtimeslice_hash_ops
240 FOR TYPE RRTimeslice USING hash AS
241 OPERATOR 1 = ,
242 FUNCTION 1 rrtimeslice_seq_hash(rrtimeslice);
244 CREATE TYPE CData;
246 CREATE OR REPLACE FUNCTION CData_validate(integer)
247 RETURNS cstring
248 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'cdata_validate'
249 LANGUAGE C IMMUTABLE STRICT;
251 -- this will abort the transaction in case the expected internal length does
252 -- not match the actual length
253 SELECT CData_validate(24);
255 CREATE OR REPLACE FUNCTION CData_in(cstring, oid, integer)
256 RETURNS CData
257 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'cdata_in'
258 LANGUAGE C IMMUTABLE STRICT;
260 CREATE OR REPLACE FUNCTION CData_out(CData)
261 RETURNS cstring
262 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'cdata_out'
263 LANGUAGE C IMMUTABLE STRICT;
265 CREATE OR REPLACE FUNCTION CData_typmodin(cstring[])
266 RETURNS integer
267 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'cdata_typmodin'
268 LANGUAGE C IMMUTABLE STRICT;
270 CREATE OR REPLACE FUNCTION CData_typmodout(integer)
271 RETURNS cstring
272 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'cdata_typmodout'
273 LANGUAGE C IMMUTABLE STRICT;
275 CREATE TYPE CData (
276 INTERNALLENGTH = 24,
277 INPUT = CData_in,
278 OUTPUT = CData_out,
279 TYPMOD_IN = CData_typmodin,
280 TYPMOD_OUT = CData_typmodout,
281 ALIGNMENT = double,
282 STORAGE = plain
283 );
285 CREATE OR REPLACE FUNCTION CData(cdata, integer, boolean)
286 RETURNS cdata
287 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'cdata_to_cdata'
288 LANGUAGE C IMMUTABLE STRICT;
290 CREATE CAST (cdata AS cdata)
291 WITH FUNCTION CData(cdata, integer, boolean)
292 AS IMPLICIT;
294 CREATE CAST (numeric AS cdata)
295 WITH INOUT
296 AS ASSIGNMENT;
298 CREATE OR REPLACE FUNCTION CData(integer, integer, boolean)
299 RETURNS cdata
300 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'int32_to_cdata'
301 LANGUAGE C IMMUTABLE STRICT;
303 CREATE CAST (integer AS cdata)
304 WITH FUNCTION CData(integer, integer, boolean)
305 AS ASSIGNMENT;
307 CREATE OR REPLACE FUNCTION Float8(cdata)
308 RETURNS double precision
309 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'cdata_to_float8'
310 LANGUAGE C IMMUTABLE STRICT;
312 CREATE CAST (cdata AS double precision)
313 WITH FUNCTION Float8(cdata);
314 -- EXPLICIT
316 CREATE OR REPLACE FUNCTION CData_update(cdata, cdata)
317 RETURNS cdata
318 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'cdata_update'
319 LANGUAGE C IMMUTABLE;
321 CREATE OR REPLACE FUNCTION PostRR_update(name, name, name, timestamptz, double precision)
322 RETURNS cdata
323 LANGUAGE plpgsql
324 AS $$
325 DECLARE
326 tbl ALIAS FOR $1;
327 tscol ALIAS FOR $2;
328 vcol ALIAS FOR $3;
329 ts ALIAS FOR $4;
330 value ALIAS FOR $5;
331 ts_str text;
332 v_str text;
333 update_qry text;
334 status integer;
335 newts rrtimeslice;
336 new cdata;
337 BEGIN
338 tscol := quote_ident(tscol);
339 vcol := quote_ident(vcol);
340 ts_str := quote_literal(ts);
341 v_str := quote_literal(value);
343 update_qry = 'CData_update(' || vcol || ', ' || v_str || ')';
345 -- XXX: handle race conditions
347 LOOP
348 BEGIN
349 -- removing matching (by sequence no) old entries
350 EXECUTE 'DELETE FROM ' || tbl
351 || ' WHERE rrtimeslice_cmp(' || tscol || ', ' || ts_str
352 || ') = -1; '
353 || 'SELECT rrtimeslice_cmp(' || tscol || ', ' || ts_str
354 || ') AS status FROM ' || tbl
355 || ' WHERE ' || tscol || ' = ' || ts_str
356 INTO STRICT status;
358 EXCEPTION
359 WHEN NO_DATA_FOUND THEN
360 EXECUTE 'INSERT INTO ' || tbl
361 || ' (' || tscol || ', ' || vcol
362 || ') VALUES (' || ts_str || ', ' || v_str
363 || ') RETURNING ' || tscol || ', ' || vcol
364 INTO newts, new;
365 -- use strict again; on exception retry?
366 RETURN new;
367 WHEN TOO_MANY_ROWS THEN
368 RAISE EXCEPTION '% is not unique in %.%',
369 ts_str, tbl, tscol;
370 END;
372 IF status = 0 THEN
373 -- timestamps match
374 EXECUTE 'UPDATE ' || tbl
375 || ' SET ' || vcol || ' = ' || update_qry
376 || ' WHERE ' || tscol || ' = ' || ts_str
377 || ' RETURNING ' || tscol || ', ' || vcol
378 INTO STRICT newts, new;
379 ELSIF status < 0 THEN
380 -- someone else inserted older data in the meantime
381 -- => try again
382 CONTINUE;
383 ELSE
384 RAISE EXCEPTION '% is too old in %.%', ts_str, tbl, tscol;
385 END IF;
386 EXIT;
387 END LOOP;
388 RETURN new;
389 END;
390 $$;
392 CREATE OR REPLACE FUNCTION PostRR_update(text, timestamptz, double precision)
393 RETURNS SETOF cdata
394 LANGUAGE plpgsql
395 AS $$
396 DECLARE
397 -- $1: rraname
398 -- $2: timestamp
399 -- $3: value
400 adef RECORD;
401 new cdata;
402 BEGIN
403 FOR adef IN SELECT tbl, tscol, vcol FROM postrr.rrarchives
404 WHERE postrr.rrarchives.rraname = $1 LOOP
405 SELECT PostRR_update(adef.tbl, adef.tscol, adef.vcol, $2, $3)
406 INTO new;
407 RETURN NEXT new;
408 END LOOP;
409 RETURN;
410 END;
411 $$;
413 -- vim: set tw=78 sw=4 ts=4 noexpandtab :