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 -- suppress messages like 'return type foo is only a shell'
31 SET client_min_messages TO WARNING;
33 BEGIN;
35 CREATE SCHEMA postrr;
37 CREATE SEQUENCE postrr.tsid INCREMENT BY 1
38 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 NO CYCLE;
40 CREATE TABLE postrr.rrtimeslices (
41 tsid integer NOT NULL PRIMARY KEY
42 DEFAULT nextval('postrr.tsid'::regclass)
43 CHECK (0 < tsid),
44 tslen integer NOT NULL,
45 tsnum integer NOT NULL
46 );
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 CREATE OR REPLACE FUNCTION PostRR_Version()
57 RETURNS cstring
58 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'postrr_version'
59 LANGUAGE 'C' IMMUTABLE;
61 CREATE TYPE RRTimeslice;
63 CREATE OR REPLACE FUNCTION RRTimeslice_validate(integer)
64 RETURNS cstring
65 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_validate'
66 LANGUAGE 'C' IMMUTABLE STRICT;
68 -- this will abort the transaction in case the expected internal length does
69 -- not match the actual length
70 SELECT RRTimeslice_validate(16);
72 CREATE OR REPLACE FUNCTION RRTimeslice_in(cstring, oid, integer)
73 RETURNS RRTimeslice
74 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_in'
75 LANGUAGE 'C' IMMUTABLE STRICT;
77 CREATE OR REPLACE FUNCTION RRTimeslice_out(RRTimeslice)
78 RETURNS cstring
79 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_out'
80 LANGUAGE 'C' IMMUTABLE STRICT;
82 CREATE OR REPLACE FUNCTION RRTimeslice_typmodin(cstring[])
83 RETURNS integer
84 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_typmodin'
85 LANGUAGE 'C' IMMUTABLE STRICT;
87 CREATE OR REPLACE FUNCTION RRTimeslice_typmodout(integer)
88 RETURNS cstring
89 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_typmodout'
90 LANGUAGE 'C' IMMUTABLE STRICT;
92 CREATE TYPE RRTimeslice (
93 INTERNALLENGTH = 16,
94 INPUT = RRTimeslice_in,
95 OUTPUT = RRTimeslice_out,
96 TYPMOD_IN = RRTimeslice_typmodin,
97 TYPMOD_OUT = RRTimeslice_typmodout,
98 ALIGNMENT = double,
99 STORAGE = plain
100 );
102 CREATE OR REPLACE FUNCTION RRTimeslice(rrtimeslice, integer, boolean)
103 RETURNS rrtimeslice
104 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_to_rrtimeslice'
105 LANGUAGE 'C' IMMUTABLE STRICT;
107 CREATE CAST (rrtimeslice AS rrtimeslice)
108 WITH FUNCTION RRTimeslice(rrtimeslice, integer, boolean)
109 AS IMPLICIT;
111 CREATE OR REPLACE FUNCTION RRTimeslice(timestamptz, integer, boolean)
112 RETURNS rrtimeslice
113 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'timestamptz_to_rrtimeslice'
114 LANGUAGE 'C' IMMUTABLE STRICT;
116 CREATE CAST (timestamptz AS rrtimeslice)
117 WITH FUNCTION RRTimeslice(timestamptz, integer, boolean)
118 AS IMPLICIT;
120 CREATE OR REPLACE FUNCTION Tstamptz(rrtimeslice)
121 RETURNS timestamptz
122 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_to_timestamptz'
123 LANGUAGE 'C' IMMUTABLE STRICT;
125 CREATE CAST (rrtimeslice AS timestamptz)
126 WITH FUNCTION Tstamptz(rrtimeslice);
127 -- EXPLICIT
129 CREATE OR REPLACE FUNCTION rrtimeslice_cmp(rrtimeslice, rrtimeslice)
130 RETURNS integer
131 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_cmp'
132 LANGUAGE 'C' IMMUTABLE STRICT;
134 CREATE OR REPLACE FUNCTION rrtimeslice_seq_eq(rrtimeslice, rrtimeslice)
135 RETURNS boolean
136 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_seq_eq'
137 LANGUAGE 'C' IMMUTABLE STRICT;
139 CREATE OR REPLACE FUNCTION rrtimeslice_seq_ne(rrtimeslice, rrtimeslice)
140 RETURNS boolean
141 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_seq_ne'
142 LANGUAGE 'C' IMMUTABLE STRICT;
144 CREATE OR REPLACE FUNCTION rrtimeslice_seq_lt(rrtimeslice, rrtimeslice)
145 RETURNS boolean
146 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_seq_lt'
147 LANGUAGE 'C' IMMUTABLE STRICT;
149 CREATE OR REPLACE FUNCTION rrtimeslice_seq_le(rrtimeslice, rrtimeslice)
150 RETURNS boolean
151 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_seq_le'
152 LANGUAGE 'C' IMMUTABLE STRICT;
154 CREATE OR REPLACE FUNCTION rrtimeslice_seq_gt(rrtimeslice, rrtimeslice)
155 RETURNS boolean
156 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_seq_gt'
157 LANGUAGE 'C' IMMUTABLE STRICT;
159 CREATE OR REPLACE FUNCTION rrtimeslice_seq_ge(rrtimeslice, rrtimeslice)
160 RETURNS boolean
161 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_seq_ge'
162 LANGUAGE 'C' IMMUTABLE STRICT;
164 CREATE OR REPLACE FUNCTION rrtimeslice_seq_cmp(rrtimeslice, rrtimeslice)
165 RETURNS integer
166 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_seq_cmp'
167 LANGUAGE 'C' IMMUTABLE STRICT;
169 CREATE OR REPLACE FUNCTION rrtimeslice_seq_hash(rrtimeslice)
170 RETURNS integer
171 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_seq_hash'
172 LANGUAGE 'C' IMMUTABLE STRICT;
174 CREATE OPERATOR = (
175 LEFTARG = RRTimeslice,
176 RIGHTARG = RRTimeslice,
177 PROCEDURE = rrtimeslice_seq_eq,
178 COMMUTATOR = =,
179 NEGATOR = <>,
180 RESTRICT = eqsel
181 );
183 CREATE OPERATOR <> (
184 LEFTARG = RRTimeslice,
185 RIGHTARG = RRTimeslice,
186 PROCEDURE = rrtimeslice_seq_ne,
187 COMMUTATOR = <>,
188 NEGATOR = =,
189 RESTRICT = neqsel
190 );
192 CREATE OPERATOR < (
193 LEFTARG = RRTimeslice,
194 RIGHTARG = RRTimeslice,
195 PROCEDURE = rrtimeslice_seq_lt,
196 COMMUTATOR = >,
197 NEGATOR = <=,
198 RESTRICT = scalarltsel
199 );
201 CREATE OPERATOR <= (
202 LEFTARG = RRTimeslice,
203 RIGHTARG = RRTimeslice,
204 PROCEDURE = rrtimeslice_seq_le,
205 COMMUTATOR = >=,
206 NEGATOR = <,
207 RESTRICT = scalarltsel
208 );
210 CREATE OPERATOR > (
211 LEFTARG = RRTimeslice,
212 RIGHTARG = RRTimeslice,
213 PROCEDURE = rrtimeslice_seq_gt,
214 COMMUTATOR = <,
215 NEGATOR = >=,
216 RESTRICT = scalargtsel
217 );
219 CREATE OPERATOR >= (
220 LEFTARG = RRTimeslice,
221 RIGHTARG = RRTimeslice,
222 PROCEDURE = rrtimeslice_seq_ge,
223 COMMUTATOR = <=,
224 NEGATOR = >,
225 RESTRICT = scalargtsel
226 );
228 CREATE OPERATOR CLASS rrtimeslice_ops
229 DEFAULT FOR TYPE RRTimeslice USING btree AS
230 OPERATOR 1 < ,
231 OPERATOR 2 <= ,
232 OPERATOR 3 = ,
233 OPERATOR 4 >= ,
234 OPERATOR 5 > ,
235 FUNCTION 1 rrtimeslice_seq_cmp(rrtimeslice, rrtimeslice);
237 CREATE OPERATOR CLASS rrtimeslice_hash_ops
238 FOR TYPE RRTimeslice USING hash AS
239 OPERATOR 1 = ,
240 FUNCTION 1 rrtimeslice_seq_hash(rrtimeslice);
242 CREATE TYPE CData;
244 CREATE OR REPLACE FUNCTION CData_validate(integer)
245 RETURNS cstring
246 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'cdata_validate'
247 LANGUAGE 'C' IMMUTABLE STRICT;
249 -- this will abort the transaction in case the expected internal length does
250 -- not match the actual length
251 SELECT CData_validate(24);
253 CREATE OR REPLACE FUNCTION CData_in(cstring, oid, integer)
254 RETURNS CData
255 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'cdata_in'
256 LANGUAGE 'C' IMMUTABLE STRICT;
258 CREATE OR REPLACE FUNCTION CData_out(CData)
259 RETURNS cstring
260 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'cdata_out'
261 LANGUAGE 'C' IMMUTABLE STRICT;
263 CREATE OR REPLACE FUNCTION CData_typmodin(cstring[])
264 RETURNS integer
265 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'cdata_typmodin'
266 LANGUAGE 'C' IMMUTABLE STRICT;
268 CREATE OR REPLACE FUNCTION CData_typmodout(integer)
269 RETURNS cstring
270 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'cdata_typmodout'
271 LANGUAGE 'C' IMMUTABLE STRICT;
273 CREATE TYPE CData (
274 INTERNALLENGTH = 24,
275 INPUT = CData_in,
276 OUTPUT = CData_out,
277 TYPMOD_IN = CData_typmodin,
278 TYPMOD_OUT = CData_typmodout,
279 ALIGNMENT = double,
280 STORAGE = plain
281 );
283 CREATE OR REPLACE FUNCTION CData(cdata, integer, boolean)
284 RETURNS cdata
285 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'cdata_to_cdata'
286 LANGUAGE 'C' IMMUTABLE STRICT;
288 CREATE CAST (cdata AS cdata)
289 WITH FUNCTION CData(cdata, integer, boolean)
290 AS IMPLICIT;
292 CREATE CAST (numeric AS cdata)
293 WITH INOUT
294 AS ASSIGNMENT;
296 CREATE OR REPLACE FUNCTION CData(integer, integer, boolean)
297 RETURNS cdata
298 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'int32_to_cdata'
299 LANGUAGE 'C' IMMUTABLE STRICT;
301 CREATE CAST (integer AS cdata)
302 WITH FUNCTION CData(integer, integer, boolean)
303 AS ASSIGNMENT;
305 CREATE OR REPLACE FUNCTION Float8(cdata)
306 RETURNS double precision
307 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'cdata_to_float8'
308 LANGUAGE 'C' IMMUTABLE STRICT;
310 CREATE CAST (cdata AS double precision)
311 WITH FUNCTION Float8(cdata);
312 -- EXPLICIT
314 CREATE OR REPLACE FUNCTION CData_update(cdata, cdata)
315 RETURNS cdata
316 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'cdata_update'
317 LANGUAGE 'C' IMMUTABLE;
319 CREATE OR REPLACE FUNCTION PostRR_update(name, name, name, timestamptz, double precision)
320 RETURNS cdata
321 LANGUAGE plpgsql
322 AS $$
323 DECLARE
324 tbl ALIAS FOR $1;
325 tscol ALIAS FOR $2;
326 vcol ALIAS FOR $3;
327 ts ALIAS FOR $4;
328 value ALIAS FOR $5;
329 ts_str text;
330 v_str text;
331 update_qry text;
332 status integer;
333 newts rrtimeslice;
334 new cdata;
335 BEGIN
336 tscol := quote_ident(tscol);
337 vcol := quote_ident(vcol);
338 ts_str := quote_literal(ts);
339 v_str := quote_literal(value);
341 update_qry = 'CData_update(' || vcol || ', ' || v_str || ')';
343 -- XXX: handle race conditions
345 LOOP
346 BEGIN
347 -- removing matching (by sequence no) old entries
348 EXECUTE 'DELETE FROM ' || tbl
349 || ' WHERE rrtimeslice_cmp(' || tscol || ', ' || ts_str
350 || ') = -1; '
351 || 'SELECT rrtimeslice_cmp(' || tscol || ', ' || ts_str
352 || ') AS status FROM ' || tbl
353 || ' WHERE ' || tscol || ' = ' || ts_str
354 INTO STRICT status;
356 EXCEPTION
357 WHEN NO_DATA_FOUND THEN
358 EXECUTE 'INSERT INTO ' || tbl
359 || ' (' || tscol || ', ' || vcol
360 || ') VALUES (' || ts_str || ', ' || v_str
361 || ') RETURNING ' || tscol || ', ' || vcol
362 INTO newts, new;
363 -- use strict again; on exception retry?
364 RETURN new;
365 WHEN TOO_MANY_ROWS THEN
366 RAISE EXCEPTION '% is not unique in %.%',
367 ts_str, tbl, tscol;
368 END;
370 IF status = 0 THEN
371 -- timestamps match
372 EXECUTE 'UPDATE ' || tbl
373 || ' SET ' || vcol || ' = ' || update_qry
374 || ' WHERE ' || tscol || ' = ' || ts_str
375 || ' RETURNING ' || tscol || ', ' || vcol
376 INTO STRICT newts, new;
377 ELSIF status < 0 THEN
378 -- someone else inserted older data in the meantime
379 -- => try again
380 CONTINUE;
381 ELSE
382 RAISE EXCEPTION '% is too old in %.%', ts_str, tbl, tscol;
383 END IF;
384 EXIT;
385 END LOOP;
386 RETURN new;
387 END;
388 $$;
390 CREATE OR REPLACE FUNCTION PostRR_update(text, timestamptz, double precision)
391 RETURNS SETOF cdata
392 LANGUAGE plpgsql
393 AS $$
394 DECLARE
395 -- $1: rraname
396 -- $2: timestamp
397 -- $3: value
398 adef RECORD;
399 new cdata;
400 BEGIN
401 FOR adef IN SELECT tbl, tscol, vcol FROM postrr.rrarchives
402 WHERE postrr.rrarchives.rraname = $1 LOOP
403 SELECT PostRR_update(adef.tbl, adef.tscol, adef.vcol, $2, $3)
404 INTO new;
405 RETURN NEXT new;
406 END LOOP;
407 RETURN;
408 END;
409 $$;
411 COMMIT;
413 SET client_min_messages TO DEFAULT;
415 -- vim: set tw=78 sw=4 ts=4 noexpandtab :