![[tokkee]](http://tokkee.org/images/avatar.png){kind=avatar}
diff --git a/src/postrr.sql.in b/src/postrr.sql.in
index fc32f43d7f64f2dabf65391f142349159211a66b..fdbe4b90c1e6800f4e57b6f2a891d57701b4b6f2 100644 (file)
--- a/src/postrr.sql.in
+++ b/src/postrr.sql.in
-- PostRR - PostgreSQL Round-Robin Extension
--
--- suppress messages like 'return type foo is only a shell'
-SET client_min_messages TO WARNING;
-
-BEGIN;
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION postrr" to load this file. \quit
CREATE SCHEMA postrr;
tsnum integer NOT NULL
);
+SELECT pg_catalog.pg_extension_config_dump('postrr.rrtimeslices', '');
+
CREATE TABLE postrr.rrarchives (
rraname text NOT NULL,
tbl name NOT NULL,
UNIQUE (rraname, tbl, tscol, vcol)
);
+SELECT pg_catalog.pg_extension_config_dump('postrr.rrarchives', '');
+
CREATE OR REPLACE FUNCTION PostRR_Version()
RETURNS cstring
AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'postrr_version'
- LANGUAGE 'C' IMMUTABLE;
+ LANGUAGE C IMMUTABLE;
CREATE TYPE RRTimeslice;
CREATE OR REPLACE FUNCTION RRTimeslice_validate(integer)
RETURNS cstring
AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_validate'
- LANGUAGE 'C' IMMUTABLE STRICT;
+ LANGUAGE C IMMUTABLE STRICT;
-- this will abort the transaction in case the expected internal length does
-- not match the actual length
CREATE OR REPLACE FUNCTION RRTimeslice_in(cstring, oid, integer)
RETURNS RRTimeslice
AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_in'
- LANGUAGE 'C' IMMUTABLE STRICT;
+ LANGUAGE C IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION RRTimeslice_out(RRTimeslice)
RETURNS cstring
AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_out'
- LANGUAGE 'C' IMMUTABLE STRICT;
+ LANGUAGE C IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION RRTimeslice_typmodin(cstring[])
RETURNS integer
AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_typmodin'
- LANGUAGE 'C' IMMUTABLE STRICT;
+ LANGUAGE C IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION RRTimeslice_typmodout(integer)
RETURNS cstring
AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_typmodout'
- LANGUAGE 'C' IMMUTABLE STRICT;
+ LANGUAGE C IMMUTABLE STRICT;
CREATE TYPE RRTimeslice (
INTERNALLENGTH = 16,
CREATE OR REPLACE FUNCTION RRTimeslice(rrtimeslice, integer, boolean)
RETURNS rrtimeslice
AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_to_rrtimeslice'
- LANGUAGE 'C' IMMUTABLE STRICT;
+ LANGUAGE C IMMUTABLE STRICT;
CREATE CAST (rrtimeslice AS rrtimeslice)
WITH FUNCTION RRTimeslice(rrtimeslice, integer, boolean)
AS IMPLICIT;
+CREATE OR REPLACE FUNCTION RRTimeslice(timestamptz, integer, boolean)
+ RETURNS rrtimeslice
+ AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'timestamptz_to_rrtimeslice'
+ LANGUAGE C IMMUTABLE STRICT;
+
+CREATE CAST (timestamptz AS rrtimeslice)
+ WITH FUNCTION RRTimeslice(timestamptz, integer, boolean)
+ AS IMPLICIT;
+
CREATE OR REPLACE FUNCTION Tstamptz(rrtimeslice)
RETURNS timestamptz
AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_to_timestamptz'
- LANGUAGE 'C' IMMUTABLE STRICT;
+ LANGUAGE C IMMUTABLE STRICT;
CREATE CAST (rrtimeslice AS timestamptz)
WITH FUNCTION Tstamptz(rrtimeslice);
-- EXPLICIT
+CREATE OR REPLACE FUNCTION rrtimeslice_cmp(rrtimeslice, rrtimeslice)
+ RETURNS integer
+ AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_cmp'
+ LANGUAGE C IMMUTABLE STRICT;
+
CREATE OR REPLACE FUNCTION rrtimeslice_seq_eq(rrtimeslice, rrtimeslice)
RETURNS boolean
AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_seq_eq'
- LANGUAGE 'C' IMMUTABLE STRICT;
+ LANGUAGE C IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION rrtimeslice_seq_ne(rrtimeslice, rrtimeslice)
RETURNS boolean
AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_seq_ne'
- LANGUAGE 'C' IMMUTABLE STRICT;
+ LANGUAGE C IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION rrtimeslice_seq_lt(rrtimeslice, rrtimeslice)
RETURNS boolean
AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_seq_lt'
- LANGUAGE 'C' IMMUTABLE STRICT;
+ LANGUAGE C IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION rrtimeslice_seq_le(rrtimeslice, rrtimeslice)
RETURNS boolean
AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_seq_le'
- LANGUAGE 'C' IMMUTABLE STRICT;
+ LANGUAGE C IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION rrtimeslice_seq_gt(rrtimeslice, rrtimeslice)
RETURNS boolean
AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_seq_gt'
- LANGUAGE 'C' IMMUTABLE STRICT;
+ LANGUAGE C IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION rrtimeslice_seq_ge(rrtimeslice, rrtimeslice)
RETURNS boolean
AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_seq_ge'
- LANGUAGE 'C' IMMUTABLE STRICT;
+ LANGUAGE C IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION rrtimeslice_seq_cmp(rrtimeslice, rrtimeslice)
RETURNS integer
AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_seq_cmp'
- LANGUAGE 'C' IMMUTABLE STRICT;
+ LANGUAGE C IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION rrtimeslice_seq_hash(rrtimeslice)
RETURNS integer
AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_seq_hash'
- LANGUAGE 'C' IMMUTABLE STRICT;
+ LANGUAGE C IMMUTABLE STRICT;
CREATE OPERATOR = (
LEFTARG = RRTimeslice,
CREATE OR REPLACE FUNCTION CData_validate(integer)
RETURNS cstring
AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'cdata_validate'
- LANGUAGE 'C' IMMUTABLE STRICT;
+ LANGUAGE C IMMUTABLE STRICT;
-- this will abort the transaction in case the expected internal length does
-- not match the actual length
CREATE OR REPLACE FUNCTION CData_in(cstring, oid, integer)
RETURNS CData
AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'cdata_in'
- LANGUAGE 'C' IMMUTABLE STRICT;
+ LANGUAGE C IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION CData_out(CData)
RETURNS cstring
AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'cdata_out'
- LANGUAGE 'C' IMMUTABLE STRICT;
+ LANGUAGE C IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION CData_typmodin(cstring[])
RETURNS integer
AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'cdata_typmodin'
- LANGUAGE 'C' IMMUTABLE STRICT;
+ LANGUAGE C IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION CData_typmodout(integer)
RETURNS cstring
AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'cdata_typmodout'
- LANGUAGE 'C' IMMUTABLE STRICT;
+ LANGUAGE C IMMUTABLE STRICT;
CREATE TYPE CData (
INTERNALLENGTH = 24,
CREATE OR REPLACE FUNCTION CData(cdata, integer, boolean)
RETURNS cdata
AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'cdata_to_cdata'
- LANGUAGE 'C' IMMUTABLE STRICT;
+ LANGUAGE C IMMUTABLE STRICT;
CREATE CAST (cdata AS cdata)
WITH FUNCTION CData(cdata, integer, boolean)
CREATE OR REPLACE FUNCTION CData(integer, integer, boolean)
RETURNS cdata
AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'int32_to_cdata'
- LANGUAGE 'C' IMMUTABLE STRICT;
+ LANGUAGE C IMMUTABLE STRICT;
CREATE CAST (integer AS cdata)
WITH FUNCTION CData(integer, integer, boolean)
CREATE OR REPLACE FUNCTION Float8(cdata)
RETURNS double precision
AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'cdata_to_float8'
- LANGUAGE 'C' IMMUTABLE STRICT;
+ LANGUAGE C IMMUTABLE STRICT;
CREATE CAST (cdata AS double precision)
WITH FUNCTION Float8(cdata);
CREATE OR REPLACE FUNCTION CData_update(cdata, cdata)
RETURNS cdata
AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'cdata_update'
- LANGUAGE 'C' IMMUTABLE;
+ LANGUAGE C IMMUTABLE;
CREATE OR REPLACE FUNCTION PostRR_update(name, name, name, timestamptz, double precision)
RETURNS cdata
ts_str text;
v_str text;
update_qry text;
+ status integer;
newts rrtimeslice;
new cdata;
BEGIN
update_qry = 'CData_update(' || vcol || ', ' || v_str || ')';
- BEGIN
- EXECUTE 'UPDATE ' || tbl
- || ' SET ' || tscol || ' = ' || ts_str
- || ', ' || vcol || ' = ' || update_qry
- || ' WHERE ' || tscol || ' = ' || ts_str
- || ' RETURNING ' || tscol || ', ' || vcol
- INTO STRICT newts, new;
-
- EXCEPTION
- WHEN NO_DATA_FOUND THEN
- EXECUTE 'INSERT INTO ' || tbl
- || ' (' || tscol || ', ' || vcol
- || ') VALUES (' || ts_str || ', ' || v_str
- || ') RETURNING ' || tscol || ', ' || vcol
- INTO newts, new;
- -- use strict again; on exception retry?
- WHEN TOO_MANY_ROWS THEN
- RAISE EXCEPTION '% is not unique in %.%', ts_str, tbl, tscol;
- END;
+ -- XXX: handle race conditions
+
+ LOOP
+ BEGIN
+ -- removing matching (by sequence no) old entries
+ EXECUTE 'DELETE FROM ' || tbl
+ || ' WHERE rrtimeslice_cmp(' || tscol || ', ' || ts_str
+ || ') = -1; '
+ || 'SELECT rrtimeslice_cmp(' || tscol || ', ' || ts_str
+ || ') AS status FROM ' || tbl
+ || ' WHERE ' || tscol || ' = ' || ts_str
+ INTO STRICT status;
+
+ EXCEPTION
+ WHEN NO_DATA_FOUND THEN
+ EXECUTE 'INSERT INTO ' || tbl
+ || ' (' || tscol || ', ' || vcol
+ || ') VALUES (' || ts_str || ', ' || v_str
+ || ') RETURNING ' || tscol || ', ' || vcol
+ INTO newts, new;
+ -- use strict again; on exception retry?
+ RETURN new;
+ WHEN TOO_MANY_ROWS THEN
+ RAISE EXCEPTION '% is not unique in %.%',
+ ts_str, tbl, tscol;
+ END;
+
+ IF status = 0 THEN
+ -- timestamps match
+ EXECUTE 'UPDATE ' || tbl
+ || ' SET ' || vcol || ' = ' || update_qry
+ || ' WHERE ' || tscol || ' = ' || ts_str
+ || ' RETURNING ' || tscol || ', ' || vcol
+ INTO STRICT newts, new;
+ ELSIF status < 0 THEN
+ -- someone else inserted older data in the meantime
+ -- => try again
+ CONTINUE;
+ ELSE
+ RAISE EXCEPTION '% is too old in %.%', ts_str, tbl, tscol;
+ END IF;
+ EXIT;
+ END LOOP;
RETURN new;
END;
$$;
END;
$$;
-COMMIT;
-
-SET client_min_messages TO DEFAULT;
-
-- vim: set tw=78 sw=4 ts=4 noexpandtab :