liquid_feedback_core

view lf_update.c @ 295:69d6fba0f84c

find changesets by author, revision, files, or words in the commit message
Use EXCLUSIVE MODE table locks in function "lock_issue"("issue"."id")

Avoids deadlocks caused by explicit FOR UPDATE row locks when updating member statements and implicit FOR SHARE row locks when writing snapshots.
author jbe
date Thu Sep 13 17:02:22 2012 +0200 (2012-09-13)
parents 2a6984869ba3
children a2ab4fb1d0c7
line source
1 #include <stdlib.h>
2 #include <stdio.h>
3 #include <string.h>
4 #include <libpq-fe.h>
5
6 int main(int argc, char **argv) {
7
8 // variable declarations:
9 int err = 0;
10 int i, count;
11 char *conninfo;
12 PGconn *db;
13 PGresult *list;
14 PGresult *status;
15
16 // parse command line:
17 if (argc == 0) return 1;
18 if (argc == 1 || !strcmp(argv[1], "-h") || !strcmp(argv[1], "--help")) {
19 FILE *out;
20 out = argc == 1 ? stderr : stdout;
21 fprintf(stdout, "\n");
22 fprintf(stdout, "Usage: %s <conninfo>\n", argv[0]);
23 fprintf(stdout, "\n");
24 fprintf(stdout, "<conninfo> is specified by PostgreSQL's libpq,\n");
25 fprintf(stdout, "see http://www.postgresql.org/docs/8.4/static/libpq-connect.html\n");
26 fprintf(stdout, "\n");
27 fprintf(stdout, "Example: %s dbname=liquid_feedback\n", argv[0]);
28 fprintf(stdout, "\n");
29 return argc == 1 ? 1 : 0;
30 }
31 {
32 size_t len = 0;
33 for (i=1; i<argc; i++) len += strlen(argv[i]) + 1;
34 conninfo = malloc(len * sizeof(char));
35 if (!conninfo) {
36 fprintf(stderr, "Error: Could not allocate memory for conninfo string\n");
37 return 1;
38 }
39 conninfo[0] = 0;
40 for (i=1; i<argc; i++) {
41 if (i>1) strcat(conninfo, " ");
42 strcat(conninfo, argv[i]);
43 }
44 }
45
46 // connect to database:
47 db = PQconnectdb(conninfo);
48 if (!db) {
49 fprintf(stderr, "Error: Could not create database handle\n");
50 return 1;
51 }
52 if (PQstatus(db) != CONNECTION_OK) {
53 fprintf(stderr, "Could not open connection:\n%s", PQerrorMessage(db));
54 return 1;
55 }
56
57 // delete expired sessions:
58 status = PQexec(db, "DELETE FROM \"expired_session\"");
59 if (!status) {
60 fprintf(stderr, "Error in pqlib while sending SQL command deleting expired sessions\n");
61 err = 1;
62 } else if (
63 PQresultStatus(status) != PGRES_COMMAND_OK &&
64 PQresultStatus(status) != PGRES_TUPLES_OK
65 ) {
66 fprintf(stderr, "Error while executing SQL command deleting expired sessions:\n%s", PQresultErrorMessage(status));
67 err = 1;
68 PQclear(status);
69 } else {
70 PQclear(status);
71 }
72
73 // check member activity:
74 status = PQexec(db, "SELECT \"check_activity\"()");
75 if (!status) {
76 fprintf(stderr, "Error in pqlib while sending SQL command checking member activity\n");
77 err = 1;
78 } else if (
79 PQresultStatus(status) != PGRES_COMMAND_OK &&
80 PQresultStatus(status) != PGRES_TUPLES_OK
81 ) {
82 fprintf(stderr, "Error while executing SQL command checking member activity:\n%s", PQresultErrorMessage(status));
83 err = 1;
84 PQclear(status);
85 } else {
86 PQclear(status);
87 }
88
89 // calculate member counts:
90 status = PQexec(db, "SELECT \"calculate_member_counts\"()");
91 if (!status) {
92 fprintf(stderr, "Error in pqlib while sending SQL command calculating member counts\n");
93 err = 1;
94 } else if (
95 PQresultStatus(status) != PGRES_COMMAND_OK &&
96 PQresultStatus(status) != PGRES_TUPLES_OK
97 ) {
98 fprintf(stderr, "Error while executing SQL command calculating member counts:\n%s", PQresultErrorMessage(status));
99 err = 1;
100 PQclear(status);
101 } else {
102 PQclear(status);
103 }
104
105 // update open issues:
106 list = PQexec(db, "SELECT \"id\" FROM \"open_issue\"");
107 if (!list) {
108 fprintf(stderr, "Error in pqlib while sending SQL command selecting open issues\n");
109 err = 1;
110 } else if (PQresultStatus(list) != PGRES_TUPLES_OK) {
111 fprintf(stderr, "Error while executing SQL command selecting open issues:\n%s", PQresultErrorMessage(list));
112 err = 1;
113 PQclear(list);
114 } else {
115 count = PQntuples(list);
116 for (i=0; i<count; i++) {
117 const char *params[1];
118 params[0] = PQgetvalue(list, i, 0);
119 status = PQexecParams(
120 db, "SELECT \"check_issue\"($1)", 1, NULL, params, NULL, NULL, 0
121 );
122 if (!status) {
123 fprintf(stderr, "Error in pqlib while sending SQL command to call function \"check_issue\"(...):\n");
124 err = 1;
125 } else if (
126 PQresultStatus(status) != PGRES_COMMAND_OK &&
127 PQresultStatus(status) != PGRES_TUPLES_OK
128 ) {
129 fprintf(stderr, "Error while calling SQL function \"check_issue\"(...):\n%s", PQresultErrorMessage(status));
130 err = 1;
131 PQclear(status);
132 } else {
133 PQclear(status);
134 }
135 }
136 PQclear(list);
137 }
138
139 // calculate ranks after voting is finished:
140 // (NOTE: This is a seperate process to avoid long transactions with locking)
141 list = PQexec(db, "SELECT \"id\" FROM \"issue_with_ranks_missing\"");
142 if (!list) {
143 fprintf(stderr, "Error in pqlib while sending SQL command selecting issues where ranks are missing\n");
144 err = 1;
145 } else if (PQresultStatus(list) != PGRES_TUPLES_OK) {
146 fprintf(stderr, "Error while executing SQL command selecting issues where ranks are missing:\n%s", PQresultErrorMessage(list));
147 err = 1;
148 PQclear(list);
149 } else {
150 count = PQntuples(list);
151 for (i=0; i<count; i++) {
152 const char *params[1];
153 params[0] = PQgetvalue(list, i, 0);
154 status = PQexecParams(
155 db, "SELECT \"calculate_ranks\"($1)", 1, NULL, params, NULL, NULL, 0
156 );
157 if (!status) {
158 fprintf(stderr, "Error in pqlib while sending SQL command to call function \"calculate_ranks\"(...):\n");
159 err = 1;
160 } else if (
161 PQresultStatus(status) != PGRES_COMMAND_OK &&
162 PQresultStatus(status) != PGRES_TUPLES_OK
163 ) {
164 fprintf(stderr, "Error while calling SQL function \"calculate_ranks\"(...):\n%s", PQresultErrorMessage(status));
165 err = 1;
166 PQclear(status);
167 } else {
168 PQclear(status);
169 }
170 }
171 PQclear(list);
172 }
173
174 // cleanup and exit
175 PQfinish(db);
176 return err;
177
178 }

Impressum / About Us