From 2072e784f2fab50bff6aa25831d14424d0f575ec Mon Sep 17 00:00:00 2001
From: Sebastian Harl <sh@tokkee.org>
Date: Fri, 9 Nov 2012 08:41:06 +0100
Subject: [PATCH] PostRR_update(): Fixed handling of multiple columns in the
 same table.

Previously, after updating the first column, the next columns would already
find a matching timeslice (by timestamp), thus updating the old value.

Now, all old data will simply be removed (for timeslices which are strictly
older than the new timestamp (by timestamp)). This ensures that all data
related to the old timestamp is removed.

Note: However, this *might* destroy unrelated data as well in case a user
stores additional information in that table. For now, I will simply ignore
that as I don't see any reasonable justification for doing so ;-)
---
 src/postrr.sql.in | 78 +++++++++++++++++++++++++----------------------
 1 file changed, 41 insertions(+), 37 deletions(-)

diff --git a/src/postrr.sql.in b/src/postrr.sql.in
index eb19745..a3bff33 100644
--- a/src/postrr.sql.in
+++ b/src/postrr.sql.in
@@ -342,43 +342,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;
 $$;
-- 
2.30.2