PostRR_update(): Fixed handling of multiple columns in the same table.

[postrr.git] / src / postrr.sql.in

diff --git a/src/postrr.sql.in b/src/postrr.sql.in
index af96a920687736caaf66cdf5f1cca6f8d6421eed..a3bff336ae94ef5288b4ab927a7a3c4704359d83 100644 (file)
--- a/src/postrr.sql.in
+++ b/src/postrr.sql.in
@@ -108,6 +108,15 @@ 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'
@@ -117,6 +126,11 @@ 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'
@@ -315,6 +329,7 @@ DECLARE
        ts_str text;
        v_str text;
        update_qry text;
+       status integer;
        newts rrtimeslice;
        new cdata;
 BEGIN
@@ -325,25 +340,49 @@ 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;
 $$;
@@ -353,9 +392,9 @@ CREATE OR REPLACE FUNCTION PostRR_update(text, timestamptz, double precision)
        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