# HG changeset patch
# User jbe
# Date 1506890218 -7200
# Node ID f28662013308ef2e57aa6744a9e839b5495582b8
# Parent  abf04224f1e1cb4791a7133e69c2e458c740ceb2
Experimental workaround for missing spatio-temporal index when looking up recently closed initiatives (with LIMIT) inside a bounding box

diff -r abf04224f1e1 -r f28662013308 core.sql
--- a/core.sql	Fri Sep 29 22:36:20 2017 +0200
+++ b/core.sql	Sun Oct 01 22:36:58 2017 +0200
@@ -6,7 +6,7 @@
 CREATE EXTENSION IF NOT EXISTS latlon;  -- load pgLatLon extenstion
 
 CREATE VIEW "liquid_feedback_version" AS
-  SELECT * FROM (VALUES ('4.0.0', 4, 0, 0))
+  SELECT * FROM (VALUES ('4.0-dev', 4, 0, -1))
   AS "subquery"("string", "major", "minor", "revision");
 
 
@@ -4552,6 +4552,84 @@
 
 
 
+------------------------
+-- Geospatial lookups --
+------------------------
+
+CREATE FUNCTION "closed_initiatives_in_bounding_box"
+  ( "bounding_box_p" EBOX,
+    "limit_p"        INT4 )
+  RETURNS SETOF "initiative"
+  LANGUAGE 'plpgsql' STABLE AS $$
+    DECLARE
+      "limit_v" INT4;
+      "count_v" INT4;
+    BEGIN
+      "limit_v" := "limit_p" + 1;
+      LOOP
+        SELECT count(1) INTO "count_v"
+          FROM "initiative"
+          JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
+          WHERE "issue"."closed" NOTNULL
+          AND GeoJSON_to_ecluster("initiative"."location") && "bounding_box_p"
+          LIMIT "limit_v";
+        IF "count_v" < "limit_v" THEN
+          RETURN QUERY SELECT "initiative".*
+            FROM (
+              SELECT
+                "initiative"."id" AS "initiative_id",
+                "issue"."closed"
+              FROM "initiative"
+              JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
+              WHERE "issue"."closed" NOTNULL
+              AND GeoJSON_to_ecluster("initiative"."location") && "bounding_box_p"
+            ) AS "subquery"
+            JOIN "initiative" ON "initiative"."id" = "subquery"."initiative_id"
+            ORDER BY "subquery"."closed" DESC
+            LIMIT "limit_p";
+          RETURN;
+        END IF;
+        SELECT count(1) INTO "count_v"
+          FROM (
+            SELECT "initiative"."id" AS "initiative_id"
+            FROM "initiative"
+            JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
+            WHERE "issue"."closed" NOTNULL
+            ORDER BY "closed" DESC
+            LIMIT "limit_v"
+          ) AS "subquery"
+          JOIN "initiative" ON "initiative"."id" = "subquery"."initiative_id"
+          WHERE GeoJSON_to_ecluster("initiative"."location") && "bounding_box_p"
+          LIMIT "limit_p";
+        IF "count_v" >= "limit_p" THEN
+          RETURN QUERY SELECT "initiative".*
+            FROM (
+              SELECT
+                "initiative"."id" AS "initiative_id",
+                "issue"."closed"
+              FROM "initiative"
+              JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
+              WHERE "issue"."closed" NOTNULL
+              ORDER BY "closed" DESC
+              LIMIT "limit_v"
+            ) AS "subquery"
+            JOIN "initiative" ON "initiative"."id" = "subquery"."initiative_id"
+            WHERE GeoJSON_to_ecluster("initiative"."location") && "bounding_box_p"
+            ORDER BY "subquery"."closed" DESC
+            LIMIT "limit_p";
+          RETURN;
+        END IF;
+        "limit_v" := "limit_v" * 2;
+      END LOOP;
+    END;
+  $$;
+
+COMMENT ON FUNCTION "closed_initiatives_in_bounding_box"
+  ( EBOX, INT4 )
+  IS 'TODO';
+
+
+
 ---------------------------
 -- Transaction isolation --
 ---------------------------