diff --git a/src/postrr.sql.in b/src/postrr.sql.in
index af96a920687736caaf66cdf5f1cca6f8d6421eed..fdbe4b90c1e6800f4e57b6f2a891d57701b4b6f2 100644 (file)
--- a/src/postrr.sql.in
+++ b/src/postrr.sql.in
-- PostRR - PostgreSQL Round-Robin Extension
--
-- 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;
CREATE SCHEMA postrr;
tsnum integer NOT NULL
);
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,
CREATE TABLE postrr.rrarchives (
rraname text NOT NULL,
tbl name NOT NULL,
UNIQUE (rraname, tbl, tscol, vcol)
);
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'
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'
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
-- 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'
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'
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'
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'
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 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'
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 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'
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 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'
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'
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'
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'
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'
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'
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'
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'
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 OPERATOR = (
LEFTARG = RRTimeslice,
CREATE OR REPLACE FUNCTION CData_validate(integer)
RETURNS cstring
AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'cdata_validate'
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
-- 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'
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'
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'
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'
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 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'
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 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'
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 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'
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 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'
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
CREATE OR REPLACE FUNCTION PostRR_update(name, name, name, timestamptz, double precision)
RETURNS cdata
ts_str text;
v_str text;
update_qry text;
ts_str text;
v_str text;
update_qry text;
+ status integer;
newts rrtimeslice;
new cdata;
BEGIN
newts rrtimeslice;
new cdata;
BEGIN
update_qry = 'CData_update(' || vcol || ', ' || v_str || ')';
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;
$$;
RETURN new;
END;
$$;
LANGUAGE plpgsql
AS $$
DECLARE
LANGUAGE plpgsql
AS $$
DECLARE
- rraname ALIAS FOR $1;
- ts ALIAS FOR $2;
- value ALIAS FOR $3;
+ -- $1: rraname
+ -- $2: timestamp
+ -- $3: value
adef RECORD;
new cdata;
BEGIN
adef RECORD;
new cdata;
BEGIN
END;
$$;
END;
$$;
-COMMIT;
-
-SET client_min_messages TO DEFAULT;
-
-- vim: set tw=78 sw=4 ts=4 noexpandtab :
-- vim: set tw=78 sw=4 ts=4 noexpandtab :