liquid_feedback_core
annotate update/core-update.v2.0.10-v2.0.11.sql @ 297:779ba19a07df
Different locking levels and different locking order in function "lock_issue"("issue"."id") to avoid deadlocks
(includes update of update/core-update.v2.0.11-v2.1.0.sql)
(includes update of update/core-update.v2.0.11-v2.1.0.sql)
author | jbe |
---|---|
date | Tue Sep 25 02:32:51 2012 +0200 (2012-09-25) |
parents | e818f83e133b |
children |
rev | line source |
---|---|
jbe@256 | 1 BEGIN; |
jbe@256 | 2 |
jbe@256 | 3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS |
jbe@256 | 4 SELECT * FROM (VALUES ('2.0.11', 2, 0, 11)) |
jbe@256 | 5 AS "subquery"("string", "major", "minor", "revision"); |
jbe@256 | 6 |
jbe@256 | 7 DROP FUNCTION "delegation_info" |
jbe@256 | 8 ( "member"."id"%TYPE, |
jbe@256 | 9 "unit"."id"%TYPE, |
jbe@256 | 10 "area"."id"%TYPE, |
jbe@256 | 11 "issue"."id"%TYPE, |
jbe@256 | 12 "member"."id"%TYPE ); |
jbe@256 | 13 |
jbe@256 | 14 DROP FUNCTION "delegation_chain" |
jbe@256 | 15 ( "member"."id"%TYPE, |
jbe@256 | 16 "unit"."id"%TYPE, |
jbe@256 | 17 "area"."id"%TYPE, |
jbe@256 | 18 "issue"."id"%TYPE, |
jbe@256 | 19 "member"."id"%TYPE ); |
jbe@256 | 20 |
jbe@256 | 21 CREATE FUNCTION "delegation_chain" |
jbe@256 | 22 ( "member_id_p" "member"."id"%TYPE, |
jbe@256 | 23 "unit_id_p" "unit"."id"%TYPE, |
jbe@256 | 24 "area_id_p" "area"."id"%TYPE, |
jbe@256 | 25 "issue_id_p" "issue"."id"%TYPE, |
jbe@256 | 26 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL, |
jbe@256 | 27 "simulate_default_p" BOOLEAN DEFAULT FALSE ) |
jbe@256 | 28 RETURNS SETOF "delegation_chain_row" |
jbe@256 | 29 LANGUAGE 'plpgsql' STABLE AS $$ |
jbe@256 | 30 DECLARE |
jbe@256 | 31 "scope_v" "delegation_scope"; |
jbe@256 | 32 "unit_id_v" "unit"."id"%TYPE; |
jbe@256 | 33 "area_id_v" "area"."id"%TYPE; |
jbe@256 | 34 "issue_row" "issue"%ROWTYPE; |
jbe@256 | 35 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[] |
jbe@256 | 36 "loop_member_id_v" "member"."id"%TYPE; |
jbe@256 | 37 "output_row" "delegation_chain_row"; |
jbe@256 | 38 "output_rows" "delegation_chain_row"[]; |
jbe@256 | 39 "simulate_v" BOOLEAN; |
jbe@256 | 40 "simulate_here_v" BOOLEAN; |
jbe@256 | 41 "delegation_row" "delegation"%ROWTYPE; |
jbe@256 | 42 "row_count" INT4; |
jbe@256 | 43 "i" INT4; |
jbe@256 | 44 "loop_v" BOOLEAN; |
jbe@256 | 45 BEGIN |
jbe@256 | 46 IF "simulate_trustee_id_p" NOTNULL AND "simulate_default_p" THEN |
jbe@256 | 47 RAISE EXCEPTION 'Both "simulate_trustee_id_p" is set, and "simulate_default_p" is true'; |
jbe@256 | 48 END IF; |
jbe@256 | 49 IF "simulate_trustee_id_p" NOTNULL OR "simulate_default_p" THEN |
jbe@256 | 50 "simulate_v" := TRUE; |
jbe@256 | 51 ELSE |
jbe@256 | 52 "simulate_v" := FALSE; |
jbe@256 | 53 END IF; |
jbe@256 | 54 IF |
jbe@256 | 55 "unit_id_p" NOTNULL AND |
jbe@256 | 56 "area_id_p" ISNULL AND |
jbe@256 | 57 "issue_id_p" ISNULL |
jbe@256 | 58 THEN |
jbe@256 | 59 "scope_v" := 'unit'; |
jbe@256 | 60 "unit_id_v" := "unit_id_p"; |
jbe@256 | 61 ELSIF |
jbe@256 | 62 "unit_id_p" ISNULL AND |
jbe@256 | 63 "area_id_p" NOTNULL AND |
jbe@256 | 64 "issue_id_p" ISNULL |
jbe@256 | 65 THEN |
jbe@256 | 66 "scope_v" := 'area'; |
jbe@256 | 67 "area_id_v" := "area_id_p"; |
jbe@256 | 68 SELECT "unit_id" INTO "unit_id_v" |
jbe@256 | 69 FROM "area" WHERE "id" = "area_id_v"; |
jbe@256 | 70 ELSIF |
jbe@256 | 71 "unit_id_p" ISNULL AND |
jbe@256 | 72 "area_id_p" ISNULL AND |
jbe@256 | 73 "issue_id_p" NOTNULL |
jbe@256 | 74 THEN |
jbe@256 | 75 SELECT INTO "issue_row" * FROM "issue" WHERE "id" = "issue_id_p"; |
jbe@256 | 76 IF "issue_row"."id" ISNULL THEN |
jbe@256 | 77 RETURN; |
jbe@256 | 78 END IF; |
jbe@256 | 79 IF "issue_row"."closed" NOTNULL THEN |
jbe@256 | 80 IF "simulate_v" THEN |
jbe@256 | 81 RAISE EXCEPTION 'Tried to simulate delegation chain for closed issue.'; |
jbe@256 | 82 END IF; |
jbe@256 | 83 FOR "output_row" IN |
jbe@256 | 84 SELECT * FROM |
jbe@256 | 85 "delegation_chain_for_closed_issue"("member_id_p", "issue_id_p") |
jbe@256 | 86 LOOP |
jbe@256 | 87 RETURN NEXT "output_row"; |
jbe@256 | 88 END LOOP; |
jbe@256 | 89 RETURN; |
jbe@256 | 90 END IF; |
jbe@256 | 91 "scope_v" := 'issue'; |
jbe@256 | 92 SELECT "area_id" INTO "area_id_v" |
jbe@256 | 93 FROM "issue" WHERE "id" = "issue_id_p"; |
jbe@256 | 94 SELECT "unit_id" INTO "unit_id_v" |
jbe@256 | 95 FROM "area" WHERE "id" = "area_id_v"; |
jbe@256 | 96 ELSE |
jbe@256 | 97 RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.'; |
jbe@256 | 98 END IF; |
jbe@256 | 99 "visited_member_ids" := '{}'; |
jbe@256 | 100 "loop_member_id_v" := NULL; |
jbe@256 | 101 "output_rows" := '{}'; |
jbe@256 | 102 "output_row"."index" := 0; |
jbe@256 | 103 "output_row"."member_id" := "member_id_p"; |
jbe@256 | 104 "output_row"."member_valid" := TRUE; |
jbe@256 | 105 "output_row"."participation" := FALSE; |
jbe@256 | 106 "output_row"."overridden" := FALSE; |
jbe@256 | 107 "output_row"."disabled_out" := FALSE; |
jbe@256 | 108 "output_row"."scope_out" := NULL; |
jbe@256 | 109 LOOP |
jbe@256 | 110 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN |
jbe@256 | 111 "loop_member_id_v" := "output_row"."member_id"; |
jbe@256 | 112 ELSE |
jbe@256 | 113 "visited_member_ids" := |
jbe@256 | 114 "visited_member_ids" || "output_row"."member_id"; |
jbe@256 | 115 END IF; |
jbe@256 | 116 IF "output_row"."participation" ISNULL THEN |
jbe@256 | 117 "output_row"."overridden" := NULL; |
jbe@256 | 118 ELSIF "output_row"."participation" THEN |
jbe@256 | 119 "output_row"."overridden" := TRUE; |
jbe@256 | 120 END IF; |
jbe@256 | 121 "output_row"."scope_in" := "output_row"."scope_out"; |
jbe@256 | 122 "output_row"."member_valid" := EXISTS ( |
jbe@256 | 123 SELECT NULL FROM "member" JOIN "privilege" |
jbe@256 | 124 ON "privilege"."member_id" = "member"."id" |
jbe@256 | 125 AND "privilege"."unit_id" = "unit_id_v" |
jbe@256 | 126 WHERE "id" = "output_row"."member_id" |
jbe@256 | 127 AND "member"."active" AND "privilege"."voting_right" |
jbe@256 | 128 ); |
jbe@256 | 129 "simulate_here_v" := ( |
jbe@256 | 130 "simulate_v" AND |
jbe@256 | 131 "output_row"."member_id" = "member_id_p" |
jbe@256 | 132 ); |
jbe@256 | 133 "delegation_row" := ROW(NULL); |
jbe@256 | 134 IF "output_row"."member_valid" OR "simulate_here_v" THEN |
jbe@256 | 135 IF "scope_v" = 'unit' THEN |
jbe@256 | 136 IF NOT "simulate_here_v" THEN |
jbe@256 | 137 SELECT * INTO "delegation_row" FROM "delegation" |
jbe@256 | 138 WHERE "truster_id" = "output_row"."member_id" |
jbe@256 | 139 AND "unit_id" = "unit_id_v"; |
jbe@256 | 140 END IF; |
jbe@256 | 141 ELSIF "scope_v" = 'area' THEN |
jbe@256 | 142 "output_row"."participation" := EXISTS ( |
jbe@256 | 143 SELECT NULL FROM "membership" |
jbe@256 | 144 WHERE "area_id" = "area_id_p" |
jbe@256 | 145 AND "member_id" = "output_row"."member_id" |
jbe@256 | 146 ); |
jbe@256 | 147 IF "simulate_here_v" THEN |
jbe@256 | 148 IF "simulate_trustee_id_p" ISNULL THEN |
jbe@256 | 149 SELECT * INTO "delegation_row" FROM "delegation" |
jbe@256 | 150 WHERE "truster_id" = "output_row"."member_id" |
jbe@256 | 151 AND "unit_id" = "unit_id_v"; |
jbe@256 | 152 END IF; |
jbe@256 | 153 ELSE |
jbe@256 | 154 SELECT * INTO "delegation_row" FROM "delegation" |
jbe@256 | 155 WHERE "truster_id" = "output_row"."member_id" |
jbe@256 | 156 AND ( |
jbe@256 | 157 "unit_id" = "unit_id_v" OR |
jbe@256 | 158 "area_id" = "area_id_v" |
jbe@256 | 159 ) |
jbe@256 | 160 ORDER BY "scope" DESC; |
jbe@256 | 161 END IF; |
jbe@256 | 162 ELSIF "scope_v" = 'issue' THEN |
jbe@256 | 163 IF "issue_row"."fully_frozen" ISNULL THEN |
jbe@256 | 164 "output_row"."participation" := EXISTS ( |
jbe@256 | 165 SELECT NULL FROM "interest" |
jbe@256 | 166 WHERE "issue_id" = "issue_id_p" |
jbe@256 | 167 AND "member_id" = "output_row"."member_id" |
jbe@256 | 168 ); |
jbe@256 | 169 ELSE |
jbe@256 | 170 IF "output_row"."member_id" = "member_id_p" THEN |
jbe@256 | 171 "output_row"."participation" := EXISTS ( |
jbe@256 | 172 SELECT NULL FROM "direct_voter" |
jbe@256 | 173 WHERE "issue_id" = "issue_id_p" |
jbe@256 | 174 AND "member_id" = "output_row"."member_id" |
jbe@256 | 175 ); |
jbe@256 | 176 ELSE |
jbe@256 | 177 "output_row"."participation" := NULL; |
jbe@256 | 178 END IF; |
jbe@256 | 179 END IF; |
jbe@256 | 180 IF "simulate_here_v" THEN |
jbe@256 | 181 IF "simulate_trustee_id_p" ISNULL THEN |
jbe@256 | 182 SELECT * INTO "delegation_row" FROM "delegation" |
jbe@256 | 183 WHERE "truster_id" = "output_row"."member_id" |
jbe@256 | 184 AND ( |
jbe@256 | 185 "unit_id" = "unit_id_v" OR |
jbe@256 | 186 "area_id" = "area_id_v" |
jbe@256 | 187 ) |
jbe@256 | 188 ORDER BY "scope" DESC; |
jbe@256 | 189 END IF; |
jbe@256 | 190 ELSE |
jbe@256 | 191 SELECT * INTO "delegation_row" FROM "delegation" |
jbe@256 | 192 WHERE "truster_id" = "output_row"."member_id" |
jbe@256 | 193 AND ( |
jbe@256 | 194 "unit_id" = "unit_id_v" OR |
jbe@256 | 195 "area_id" = "area_id_v" OR |
jbe@256 | 196 "issue_id" = "issue_id_p" |
jbe@256 | 197 ) |
jbe@256 | 198 ORDER BY "scope" DESC; |
jbe@256 | 199 END IF; |
jbe@256 | 200 END IF; |
jbe@256 | 201 ELSE |
jbe@256 | 202 "output_row"."participation" := FALSE; |
jbe@256 | 203 END IF; |
jbe@256 | 204 IF "simulate_here_v" AND "simulate_trustee_id_p" NOTNULL THEN |
jbe@256 | 205 "output_row"."scope_out" := "scope_v"; |
jbe@256 | 206 "output_rows" := "output_rows" || "output_row"; |
jbe@256 | 207 "output_row"."member_id" := "simulate_trustee_id_p"; |
jbe@256 | 208 ELSIF "delegation_row"."trustee_id" NOTNULL THEN |
jbe@256 | 209 "output_row"."scope_out" := "delegation_row"."scope"; |
jbe@256 | 210 "output_rows" := "output_rows" || "output_row"; |
jbe@256 | 211 "output_row"."member_id" := "delegation_row"."trustee_id"; |
jbe@256 | 212 ELSIF "delegation_row"."scope" NOTNULL THEN |
jbe@256 | 213 "output_row"."scope_out" := "delegation_row"."scope"; |
jbe@256 | 214 "output_row"."disabled_out" := TRUE; |
jbe@256 | 215 "output_rows" := "output_rows" || "output_row"; |
jbe@256 | 216 EXIT; |
jbe@256 | 217 ELSE |
jbe@256 | 218 "output_row"."scope_out" := NULL; |
jbe@256 | 219 "output_rows" := "output_rows" || "output_row"; |
jbe@256 | 220 EXIT; |
jbe@256 | 221 END IF; |
jbe@256 | 222 EXIT WHEN "loop_member_id_v" NOTNULL; |
jbe@256 | 223 "output_row"."index" := "output_row"."index" + 1; |
jbe@256 | 224 END LOOP; |
jbe@256 | 225 "row_count" := array_upper("output_rows", 1); |
jbe@256 | 226 "i" := 1; |
jbe@256 | 227 "loop_v" := FALSE; |
jbe@256 | 228 LOOP |
jbe@256 | 229 "output_row" := "output_rows"["i"]; |
jbe@256 | 230 EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results! |
jbe@256 | 231 IF "loop_v" THEN |
jbe@256 | 232 IF "i" + 1 = "row_count" THEN |
jbe@256 | 233 "output_row"."loop" := 'last'; |
jbe@256 | 234 ELSIF "i" = "row_count" THEN |
jbe@256 | 235 "output_row"."loop" := 'repetition'; |
jbe@256 | 236 ELSE |
jbe@256 | 237 "output_row"."loop" := 'intermediate'; |
jbe@256 | 238 END IF; |
jbe@256 | 239 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN |
jbe@256 | 240 "output_row"."loop" := 'first'; |
jbe@256 | 241 "loop_v" := TRUE; |
jbe@256 | 242 END IF; |
jbe@256 | 243 IF "scope_v" = 'unit' THEN |
jbe@256 | 244 "output_row"."participation" := NULL; |
jbe@256 | 245 END IF; |
jbe@256 | 246 RETURN NEXT "output_row"; |
jbe@256 | 247 "i" := "i" + 1; |
jbe@256 | 248 END LOOP; |
jbe@256 | 249 RETURN; |
jbe@256 | 250 END; |
jbe@256 | 251 $$; |
jbe@256 | 252 |
jbe@256 | 253 COMMENT ON FUNCTION "delegation_chain" |
jbe@256 | 254 ( "member"."id"%TYPE, |
jbe@256 | 255 "unit"."id"%TYPE, |
jbe@256 | 256 "area"."id"%TYPE, |
jbe@256 | 257 "issue"."id"%TYPE, |
jbe@256 | 258 "member"."id"%TYPE, |
jbe@256 | 259 BOOLEAN ) |
jbe@256 | 260 IS 'Shows a delegation chain for unit, area, or issue; See "delegation_chain_row" type for more information'; |
jbe@256 | 261 |
jbe@256 | 262 CREATE FUNCTION "delegation_info" |
jbe@256 | 263 ( "member_id_p" "member"."id"%TYPE, |
jbe@256 | 264 "unit_id_p" "unit"."id"%TYPE, |
jbe@256 | 265 "area_id_p" "area"."id"%TYPE, |
jbe@256 | 266 "issue_id_p" "issue"."id"%TYPE, |
jbe@256 | 267 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL, |
jbe@256 | 268 "simulate_default_p" BOOLEAN DEFAULT FALSE ) |
jbe@256 | 269 RETURNS "delegation_info_type" |
jbe@256 | 270 LANGUAGE 'plpgsql' STABLE AS $$ |
jbe@256 | 271 DECLARE |
jbe@256 | 272 "current_row" "delegation_chain_row"; |
jbe@256 | 273 "result" "delegation_info_type"; |
jbe@256 | 274 BEGIN |
jbe@256 | 275 "result"."own_participation" := FALSE; |
jbe@256 | 276 FOR "current_row" IN |
jbe@256 | 277 SELECT * FROM "delegation_chain"( |
jbe@256 | 278 "member_id_p", |
jbe@256 | 279 "unit_id_p", "area_id_p", "issue_id_p", |
jbe@256 | 280 "simulate_trustee_id_p", "simulate_default_p") |
jbe@256 | 281 LOOP |
jbe@256 | 282 IF |
jbe@256 | 283 "result"."participating_member_id" ISNULL AND |
jbe@256 | 284 "current_row"."participation" |
jbe@256 | 285 THEN |
jbe@256 | 286 "result"."participating_member_id" := "current_row"."member_id"; |
jbe@256 | 287 END IF; |
jbe@256 | 288 IF "current_row"."member_id" = "member_id_p" THEN |
jbe@256 | 289 "result"."own_participation" := "current_row"."participation"; |
jbe@256 | 290 "result"."own_delegation_scope" := "current_row"."scope_out"; |
jbe@256 | 291 IF "current_row"."loop" = 'first' THEN |
jbe@256 | 292 "result"."delegation_loop" := 'own'; |
jbe@256 | 293 END IF; |
jbe@256 | 294 ELSIF |
jbe@256 | 295 "current_row"."member_valid" AND |
jbe@256 | 296 ( "current_row"."loop" ISNULL OR |
jbe@256 | 297 "current_row"."loop" != 'repetition' ) |
jbe@256 | 298 THEN |
jbe@256 | 299 IF "result"."first_trustee_id" ISNULL THEN |
jbe@256 | 300 "result"."first_trustee_id" := "current_row"."member_id"; |
jbe@256 | 301 "result"."first_trustee_participation" := "current_row"."participation"; |
jbe@256 | 302 "result"."first_trustee_ellipsis" := FALSE; |
jbe@256 | 303 IF "current_row"."loop" = 'first' THEN |
jbe@256 | 304 "result"."delegation_loop" := 'first'; |
jbe@256 | 305 END IF; |
jbe@256 | 306 ELSIF "result"."other_trustee_id" ISNULL THEN |
jbe@256 | 307 IF "current_row"."participation" AND NOT "current_row"."overridden" THEN |
jbe@256 | 308 "result"."other_trustee_id" := "current_row"."member_id"; |
jbe@256 | 309 "result"."other_trustee_participation" := TRUE; |
jbe@256 | 310 "result"."other_trustee_ellipsis" := FALSE; |
jbe@256 | 311 IF "current_row"."loop" = 'first' THEN |
jbe@256 | 312 "result"."delegation_loop" := 'other'; |
jbe@256 | 313 END IF; |
jbe@256 | 314 ELSE |
jbe@256 | 315 "result"."first_trustee_ellipsis" := TRUE; |
jbe@256 | 316 IF "current_row"."loop" = 'first' THEN |
jbe@256 | 317 "result"."delegation_loop" := 'first_ellipsis'; |
jbe@256 | 318 END IF; |
jbe@256 | 319 END IF; |
jbe@256 | 320 ELSE |
jbe@256 | 321 "result"."other_trustee_ellipsis" := TRUE; |
jbe@256 | 322 IF "current_row"."loop" = 'first' THEN |
jbe@256 | 323 "result"."delegation_loop" := 'other_ellipsis'; |
jbe@256 | 324 END IF; |
jbe@256 | 325 END IF; |
jbe@256 | 326 END IF; |
jbe@256 | 327 END LOOP; |
jbe@256 | 328 RETURN "result"; |
jbe@256 | 329 END; |
jbe@256 | 330 $$; |
jbe@256 | 331 |
jbe@256 | 332 COMMENT ON FUNCTION "delegation_info" |
jbe@256 | 333 ( "member"."id"%TYPE, |
jbe@256 | 334 "unit"."id"%TYPE, |
jbe@256 | 335 "area"."id"%TYPE, |
jbe@256 | 336 "issue"."id"%TYPE, |
jbe@256 | 337 "member"."id"%TYPE, |
jbe@256 | 338 BOOLEAN ) |
jbe@256 | 339 IS 'Notable information about a delegation chain for unit, area, or issue; See "delegation_info_type" for more information'; |
jbe@256 | 340 |
jbe@256 | 341 COMMIT; |