liquid_feedback_core
changeset 573:f28662013308
Experimental workaround for missing spatio-temporal index when looking up recently closed initiatives (with LIMIT) inside a bounding box
author | jbe |
---|---|
date | Sun Oct 01 22:36:58 2017 +0200 (2017-10-01) |
parents | abf04224f1e1 |
children | 3536fb4148dc |
files | core.sql |
line diff
1.1 --- a/core.sql Fri Sep 29 22:36:20 2017 +0200 1.2 +++ b/core.sql Sun Oct 01 22:36:58 2017 +0200 1.3 @@ -6,7 +6,7 @@ 1.4 CREATE EXTENSION IF NOT EXISTS latlon; -- load pgLatLon extenstion 1.5 1.6 CREATE VIEW "liquid_feedback_version" AS 1.7 - SELECT * FROM (VALUES ('4.0.0', 4, 0, 0)) 1.8 + SELECT * FROM (VALUES ('4.0-dev', 4, 0, -1)) 1.9 AS "subquery"("string", "major", "minor", "revision"); 1.10 1.11 1.12 @@ -4552,6 +4552,84 @@ 1.13 1.14 1.15 1.16 +------------------------ 1.17 +-- Geospatial lookups -- 1.18 +------------------------ 1.19 + 1.20 +CREATE FUNCTION "closed_initiatives_in_bounding_box" 1.21 + ( "bounding_box_p" EBOX, 1.22 + "limit_p" INT4 ) 1.23 + RETURNS SETOF "initiative" 1.24 + LANGUAGE 'plpgsql' STABLE AS $$ 1.25 + DECLARE 1.26 + "limit_v" INT4; 1.27 + "count_v" INT4; 1.28 + BEGIN 1.29 + "limit_v" := "limit_p" + 1; 1.30 + LOOP 1.31 + SELECT count(1) INTO "count_v" 1.32 + FROM "initiative" 1.33 + JOIN "issue" ON "issue"."id" = "initiative"."issue_id" 1.34 + WHERE "issue"."closed" NOTNULL 1.35 + AND GeoJSON_to_ecluster("initiative"."location") && "bounding_box_p" 1.36 + LIMIT "limit_v"; 1.37 + IF "count_v" < "limit_v" THEN 1.38 + RETURN QUERY SELECT "initiative".* 1.39 + FROM ( 1.40 + SELECT 1.41 + "initiative"."id" AS "initiative_id", 1.42 + "issue"."closed" 1.43 + FROM "initiative" 1.44 + JOIN "issue" ON "issue"."id" = "initiative"."issue_id" 1.45 + WHERE "issue"."closed" NOTNULL 1.46 + AND GeoJSON_to_ecluster("initiative"."location") && "bounding_box_p" 1.47 + ) AS "subquery" 1.48 + JOIN "initiative" ON "initiative"."id" = "subquery"."initiative_id" 1.49 + ORDER BY "subquery"."closed" DESC 1.50 + LIMIT "limit_p"; 1.51 + RETURN; 1.52 + END IF; 1.53 + SELECT count(1) INTO "count_v" 1.54 + FROM ( 1.55 + SELECT "initiative"."id" AS "initiative_id" 1.56 + FROM "initiative" 1.57 + JOIN "issue" ON "issue"."id" = "initiative"."issue_id" 1.58 + WHERE "issue"."closed" NOTNULL 1.59 + ORDER BY "closed" DESC 1.60 + LIMIT "limit_v" 1.61 + ) AS "subquery" 1.62 + JOIN "initiative" ON "initiative"."id" = "subquery"."initiative_id" 1.63 + WHERE GeoJSON_to_ecluster("initiative"."location") && "bounding_box_p" 1.64 + LIMIT "limit_p"; 1.65 + IF "count_v" >= "limit_p" THEN 1.66 + RETURN QUERY SELECT "initiative".* 1.67 + FROM ( 1.68 + SELECT 1.69 + "initiative"."id" AS "initiative_id", 1.70 + "issue"."closed" 1.71 + FROM "initiative" 1.72 + JOIN "issue" ON "issue"."id" = "initiative"."issue_id" 1.73 + WHERE "issue"."closed" NOTNULL 1.74 + ORDER BY "closed" DESC 1.75 + LIMIT "limit_v" 1.76 + ) AS "subquery" 1.77 + JOIN "initiative" ON "initiative"."id" = "subquery"."initiative_id" 1.78 + WHERE GeoJSON_to_ecluster("initiative"."location") && "bounding_box_p" 1.79 + ORDER BY "subquery"."closed" DESC 1.80 + LIMIT "limit_p"; 1.81 + RETURN; 1.82 + END IF; 1.83 + "limit_v" := "limit_v" * 2; 1.84 + END LOOP; 1.85 + END; 1.86 + $$; 1.87 + 1.88 +COMMENT ON FUNCTION "closed_initiatives_in_bounding_box" 1.89 + ( EBOX, INT4 ) 1.90 + IS 'TODO'; 1.91 + 1.92 + 1.93 + 1.94 --------------------------- 1.95 -- Transaction isolation -- 1.96 ---------------------------