liquid_feedback_core: 779ba19a07df update/core-update.v2.0.2-v2.0.3.sql

liquid_feedback_core

view update/core-update.v2.0.2-v2.0.3.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)

author	jbe
date	Tue Sep 25 02:32:51 2012 +0200 (2012-09-25)
parents	2571bb3bdf56
children

line source

1 BEGIN;

3 -- update version number:

4 CREATE OR REPLACE VIEW "liquid_feedback_version" AS

5 SELECT * FROM (VALUES ('2.0.3', 2, 0, 3))

6 AS "subquery"("string", "major", "minor", "revision");

8 -- update function "calculate_ranks"("issue"."id")

9 -- regarding setting "eligible"=FALSE for non-eligible admitted initiatives

10 -- and regarding "rank" ordering:

11 CREATE OR REPLACE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)

12 RETURNS VOID

13 LANGUAGE 'plpgsql' VOLATILE AS $$

14 DECLARE

15 "issue_row" "issue"%ROWTYPE;

16 "policy_row" "policy"%ROWTYPE;

17 "dimension_v" INTEGER;

18 "vote_matrix" INT4[][]; -- absolute votes

19 "matrix" INT8[][]; -- defeat strength / best paths

20 "i" INTEGER;

21 "j" INTEGER;

22 "k" INTEGER;

23 "battle_row" "battle"%ROWTYPE;

24 "rank_ary" INT4[];

25 "rank_v" INT4;

26 "done_v" INTEGER;

27 "winners_ary" INTEGER[];

28 "initiative_id_v" "initiative"."id"%TYPE;

29 BEGIN

30 SELECT * INTO "issue_row"

31 FROM "issue" WHERE "id" = "issue_id_p"

32 FOR UPDATE;

33 SELECT * INTO "policy_row"

34 FROM "policy" WHERE "id" = "issue_row"."policy_id";

35 SELECT count(1) INTO "dimension_v"

36 FROM "battle_participant" WHERE "issue_id" = "issue_id_p";

37 -- Create "vote_matrix" with absolute number of votes in pairwise

38 -- comparison:

39 "vote_matrix" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]);

40 "i" := 1;

41 "j" := 2;

42 FOR "battle_row" IN

43 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"

44 ORDER BY

45 "winning_initiative_id" NULLS LAST,

46 "losing_initiative_id" NULLS LAST

47 LOOP

48 "vote_matrix"["i"]["j"] := "battle_row"."count";

49 IF "j" = "dimension_v" THEN

50 "i" := "i" + 1;

51 "j" := 1;

52 ELSE

53 "j" := "j" + 1;

54 IF "j" = "i" THEN

55 "j" := "j" + 1;

56 END IF;

57 END IF;

58 END LOOP;

59 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN

60 RAISE EXCEPTION 'Wrong battle count (should not happen)';

61 END IF;

62 -- Store defeat strengths in "matrix" using "defeat_strength"

63 -- function:

64 "matrix" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]);

65 "i" := 1;

66 LOOP

67 "j" := 1;

68 LOOP

69 IF "i" != "j" THEN

70 "matrix"["i"]["j"] := "defeat_strength"(

71 "vote_matrix"["i"]["j"],

72 "vote_matrix"["j"]["i"]

73 );

74 END IF;

75 EXIT WHEN "j" = "dimension_v";

76 "j" := "j" + 1;

77 END LOOP;

78 EXIT WHEN "i" = "dimension_v";

79 "i" := "i" + 1;

80 END LOOP;

81 -- Find best paths:

82 "i" := 1;

83 LOOP

84 "j" := 1;

85 LOOP

86 IF "i" != "j" THEN

87 "k" := 1;

88 LOOP

89 IF "i" != "k" AND "j" != "k" THEN

90 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN

91 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN

92 "matrix"["j"]["k"] := "matrix"["j"]["i"];

93 END IF;

94 ELSE

95 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN

96 "matrix"["j"]["k"] := "matrix"["i"]["k"];

97 END IF;

98 END IF;

99 END IF;

100 EXIT WHEN "k" = "dimension_v";

101 "k" := "k" + 1;

102 END LOOP;

103 END IF;

104 EXIT WHEN "j" = "dimension_v";

105 "j" := "j" + 1;

106 END LOOP;

107 EXIT WHEN "i" = "dimension_v";

108 "i" := "i" + 1;

109 END LOOP;

110 -- Determine order of winners:

111 "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]);

112 "rank_v" := 1;

113 "done_v" := 0;

114 LOOP

115 "winners_ary" := '{}';

116 "i" := 1;

117 LOOP

118 IF "rank_ary"["i"] ISNULL THEN

119 "j" := 1;

120 LOOP

121 IF

122 "i" != "j" AND

123 "rank_ary"["j"] ISNULL AND

124 "matrix"["j"]["i"] > "matrix"["i"]["j"]

125 THEN

126 -- someone else is better

127 EXIT;

128 END IF;

129 IF "j" = "dimension_v" THEN

130 -- noone is better

131 "winners_ary" := "winners_ary" || "i";

132 EXIT;

133 END IF;

134 "j" := "j" + 1;

135 END LOOP;

136 END IF;

137 EXIT WHEN "i" = "dimension_v";

138 "i" := "i" + 1;

139 END LOOP;

140 "i" := 1;

141 LOOP

142 "rank_ary"["winners_ary"["i"]] := "rank_v";

143 "done_v" := "done_v" + 1;

144 EXIT WHEN "i" = array_upper("winners_ary", 1);

145 "i" := "i" + 1;

146 END LOOP;

147 EXIT WHEN "done_v" = "dimension_v";

148 "rank_v" := "rank_v" + 1;

149 END LOOP;

150 -- write preliminary results:

151 "i" := 1;

152 FOR "initiative_id_v" IN

153 SELECT "id" FROM "initiative"

154 WHERE "issue_id" = "issue_id_p" AND "admitted"

155 ORDER BY "id"

156 LOOP

157 UPDATE "initiative" SET

158 "direct_majority" =

159 CASE WHEN "policy_row"."direct_majority_strict" THEN

160 "positive_votes" * "policy_row"."direct_majority_den" >

161 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")

162 ELSE

163 "positive_votes" * "policy_row"."direct_majority_den" >=

164 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")

165 END

166 AND "positive_votes" >= "policy_row"."direct_majority_positive"

167 AND "issue_row"."voter_count"-"negative_votes" >=

168 "policy_row"."direct_majority_non_negative",

169 "indirect_majority" =

170 CASE WHEN "policy_row"."indirect_majority_strict" THEN

171 "positive_votes" * "policy_row"."indirect_majority_den" >

172 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")

173 ELSE

174 "positive_votes" * "policy_row"."indirect_majority_den" >=

175 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")

176 END

177 AND "positive_votes" >= "policy_row"."indirect_majority_positive"

178 AND "issue_row"."voter_count"-"negative_votes" >=

179 "policy_row"."indirect_majority_non_negative",

180 "schulze_rank" = "rank_ary"["i"],

181 "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"["dimension_v"],

182 "worse_than_status_quo" = "rank_ary"["i"] > "rank_ary"["dimension_v"],

183 "multistage_majority" = "rank_ary"["i"] >= "rank_ary"["dimension_v"],

184 "reverse_beat_path" = "matrix"["dimension_v"]["i"] >= 0,

185 "eligible" = FALSE,

186 "winner" = FALSE

187 WHERE "id" = "initiative_id_v";

188 "i" := "i" + 1;

189 END LOOP;

190 IF "i" != "dimension_v" THEN

191 RAISE EXCEPTION 'Wrong winner count (should not happen)';

192 END IF;

193 -- take indirect majorities into account:

194 LOOP

195 UPDATE "initiative" SET "indirect_majority" = TRUE

196 FROM (

197 SELECT "new_initiative"."id" AS "initiative_id"

198 FROM "initiative" "old_initiative"

199 JOIN "initiative" "new_initiative"

200 ON "new_initiative"."issue_id" = "issue_id_p"

201 AND "new_initiative"."indirect_majority" = FALSE

202 JOIN "battle" "battle_win"

203 ON "battle_win"."issue_id" = "issue_id_p"

204 AND "battle_win"."winning_initiative_id" = "new_initiative"."id"

205 AND "battle_win"."losing_initiative_id" = "old_initiative"."id"

206 JOIN "battle" "battle_lose"

207 ON "battle_lose"."issue_id" = "issue_id_p"

208 AND "battle_lose"."losing_initiative_id" = "new_initiative"."id"

209 AND "battle_lose"."winning_initiative_id" = "old_initiative"."id"

210 WHERE "old_initiative"."issue_id" = "issue_id_p"

211 AND "old_initiative"."indirect_majority" = TRUE

212 AND CASE WHEN "policy_row"."indirect_majority_strict" THEN

213 "battle_win"."count" * "policy_row"."indirect_majority_den" >

214 "policy_row"."indirect_majority_num" *

215 ("battle_win"."count"+"battle_lose"."count")

216 ELSE

217 "battle_win"."count" * "policy_row"."indirect_majority_den" >=

218 "policy_row"."indirect_majority_num" *

219 ("battle_win"."count"+"battle_lose"."count")

220 END

221 AND "battle_win"."count" >= "policy_row"."indirect_majority_positive"

222 AND "issue_row"."voter_count"-"battle_lose"."count" >=

223 "policy_row"."indirect_majority_non_negative"

224 ) AS "subquery"

225 WHERE "id" = "subquery"."initiative_id";

226 EXIT WHEN NOT FOUND;

227 END LOOP;

228 -- set "multistage_majority" for remaining matching initiatives:

229 UPDATE "initiative" SET "multistage_majority" = TRUE

230 FROM (

231 SELECT "losing_initiative"."id" AS "initiative_id"

232 FROM "initiative" "losing_initiative"

233 JOIN "initiative" "winning_initiative"

234 ON "winning_initiative"."issue_id" = "issue_id_p"

235 AND "winning_initiative"."admitted"

236 JOIN "battle" "battle_win"

237 ON "battle_win"."issue_id" = "issue_id_p"

238 AND "battle_win"."winning_initiative_id" = "winning_initiative"."id"

239 AND "battle_win"."losing_initiative_id" = "losing_initiative"."id"

240 JOIN "battle" "battle_lose"

241 ON "battle_lose"."issue_id" = "issue_id_p"

242 AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id"

243 AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id"

244 WHERE "losing_initiative"."issue_id" = "issue_id_p"

245 AND "losing_initiative"."admitted"

246 AND "winning_initiative"."schulze_rank" <

247 "losing_initiative"."schulze_rank"

248 AND "battle_win"."count" > "battle_lose"."count"

249 AND (

250 "battle_win"."count" > "winning_initiative"."positive_votes" OR

251 "battle_lose"."count" < "losing_initiative"."negative_votes" )

252 ) AS "subquery"

253 WHERE "id" = "subquery"."initiative_id";

254 -- mark eligible initiatives:

255 UPDATE "initiative" SET "eligible" = TRUE

256 WHERE "issue_id" = "issue_id_p"

257 AND "initiative"."direct_majority"

258 AND "initiative"."indirect_majority"

259 AND "initiative"."better_than_status_quo"

260 AND (

261 "policy_row"."no_multistage_majority" = FALSE OR

262 "initiative"."multistage_majority" = FALSE )

263 AND (

264 "policy_row"."no_reverse_beat_path" = FALSE OR

265 "initiative"."reverse_beat_path" = FALSE );

266 -- mark final winner:

267 UPDATE "initiative" SET "winner" = TRUE

268 FROM (

269 SELECT "id" AS "initiative_id"

270 FROM "initiative"

271 WHERE "issue_id" = "issue_id_p" AND "eligible"

272 ORDER BY

273 "schulze_rank",

274 "vote_ratio"("positive_votes", "negative_votes"),

275 "id"

276 LIMIT 1

277 ) AS "subquery"

278 WHERE "id" = "subquery"."initiative_id";

279 -- write (final) ranks:

280 "rank_v" := 1;

281 FOR "initiative_id_v" IN

282 SELECT "id"

283 FROM "initiative"

284 WHERE "issue_id" = "issue_id_p" AND "admitted"

285 ORDER BY

286 "winner" DESC,

287 "eligible" DESC,

288 "schulze_rank",

289 "vote_ratio"("positive_votes", "negative_votes"),

290 "id"

291 LOOP

292 UPDATE "initiative" SET "rank" = "rank_v"

293 WHERE "id" = "initiative_id_v";

294 "rank_v" := "rank_v" + 1;

295 END LOOP;

296 -- set schulze rank of status quo and mark issue as finished:

297 UPDATE "issue" SET

298 "status_quo_schulze_rank" = "rank_ary"["dimension_v"],

299 "state" =

300 CASE WHEN EXISTS (

301 SELECT NULL FROM "initiative"

302 WHERE "issue_id" = "issue_id_p" AND "winner"

303 ) THEN

304 'finished_with_winner'::"issue_state"

305 ELSE

306 'finished_without_winner'::"issue_state"

307 END,

308 "ranks_available" = TRUE

309 WHERE "id" = "issue_id_p";

310 RETURN;

311 END;

312 $$;

313

314 -- set "eligible" to FALSE for all non-eligible

315 -- but admitted initiatives of closed issues:

316 UPDATE "initiative" SET "eligible" = FALSE WHERE "eligible" ISNULL AND "rank" NOTNULL;

317

318 -- NOTE: "rank" ordering of initiatives of closed issues are

319 -- not changed, as v2.0.2 should not be in productive use yet.

320

321 COMMIT;