X-Git-Url: https://git.tokkee.org/?a=blobdiff_plain;f=src%2Fpostrr.sql.in;h=fdbe4b90c1e6800f4e57b6f2a891d57701b4b6f2;hb=HEAD;hp=9f34f22100cfd04151da1afb56ca23ec2c3538d8;hpb=3d39f72435c8664ea9ffce084b1cc8297b6070fd;p=postrr.git

diff --git a/src/postrr.sql.in b/src/postrr.sql.in
index 9f34f22..fdbe4b9 100644
--- a/src/postrr.sql.in
+++ b/src/postrr.sql.in
@@ -27,10 +27,8 @@
 -- 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;
 
@@ -45,6 +43,8 @@ CREATE TABLE postrr.rrtimeslices (
 	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,
@@ -53,17 +53,19 @@ CREATE TABLE postrr.rrarchives (
 	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
@@ -72,22 +74,22 @@ SELECT RRTimeslice_validate(16);
 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,
@@ -102,16 +104,25 @@ CREATE TYPE 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 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);
@@ -120,47 +131,47 @@ CREATE CAST (rrtimeslice AS timestamptz)
 CREATE OR REPLACE FUNCTION rrtimeslice_cmp(rrtimeslice, rrtimeslice)
 	RETURNS integer
 	AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_cmp'
-	LANGUAGE 'C' IMMUTABLE STRICT;
+	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,
@@ -235,7 +246,7 @@ CREATE TYPE CData;
 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
@@ -244,22 +255,22 @@ SELECT CData_validate(24);
 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,
@@ -274,7 +285,7 @@ CREATE TYPE 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)
@@ -287,7 +298,7 @@ CREATE CAST (numeric AS 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)
@@ -296,7 +307,7 @@ CREATE CAST (integer AS cdata)
 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);
@@ -305,7 +316,7 @@ CREATE CAST (cdata AS double precision)
 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
@@ -333,43 +344,47 @@ BEGIN
 
 	-- XXX: handle race conditions
 
-	BEGIN
-		EXECUTE '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
-		-- given timestamp is newer than in the database
-		EXECUTE 'UPDATE ' || tbl
-			|| ' SET ' || tscol || ' = ' || ts_str
-			|| ', ' || vcol || ' = ' || v_str
-			|| ' WHERE ' || tscol || ' = ' || ts_str
-			|| ' RETURNING ' || tscol || ', ' || vcol
-			INTO STRICT newts, new;
-	ELSE
-		RAISE EXCEPTION '% is too old in %.%', ts_str, tbl, tscol;
-	END IF;
+	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;
 $$;
@@ -395,9 +410,5 @@ BEGIN
 END;
 $$;
 
-COMMIT;
-
-SET client_min_messages TO DEFAULT;
-
 -- vim: set tw=78 sw=4 ts=4 noexpandtab :