liquid_feedback_core
changeset 334:a2ab4fb1d0c7
Use different transaction isolation levels in "lf_update" program
author | jbe |
---|---|
date | Tue Feb 19 14:40:38 2013 +0100 (2013-02-19) |
parents | b83ab26828a8 |
children | ab370f3b9892 |
files | lf_update.c test.sql |
line diff
1.1 --- a/lf_update.c Fri Feb 15 02:51:56 2013 +0100 1.2 +++ b/lf_update.c Tue Feb 19 14:40:38 2013 +0100 1.3 @@ -10,8 +10,7 @@ 1.4 int i, count; 1.5 char *conninfo; 1.6 PGconn *db; 1.7 - PGresult *list; 1.8 - PGresult *status; 1.9 + PGresult *res; 1.10 1.11 // parse command line: 1.12 if (argc == 0) return 1; 1.13 @@ -22,7 +21,7 @@ 1.14 fprintf(stdout, "Usage: %s <conninfo>\n", argv[0]); 1.15 fprintf(stdout, "\n"); 1.16 fprintf(stdout, "<conninfo> is specified by PostgreSQL's libpq,\n"); 1.17 - fprintf(stdout, "see http://www.postgresql.org/docs/8.4/static/libpq-connect.html\n"); 1.18 + fprintf(stdout, "see http://www.postgresql.org/docs/9.1/static/libpq-connect.html\n"); 1.19 fprintf(stdout, "\n"); 1.20 fprintf(stdout, "Example: %s dbname=liquid_feedback\n", argv[0]); 1.21 fprintf(stdout, "\n"); 1.22 @@ -55,120 +54,128 @@ 1.23 } 1.24 1.25 // delete expired sessions: 1.26 - status = PQexec(db, "DELETE FROM \"expired_session\""); 1.27 - if (!status) { 1.28 + res = PQexec(db, "DELETE FROM \"expired_session\""); 1.29 + if (!res) { 1.30 fprintf(stderr, "Error in pqlib while sending SQL command deleting expired sessions\n"); 1.31 err = 1; 1.32 } else if ( 1.33 - PQresultStatus(status) != PGRES_COMMAND_OK && 1.34 - PQresultStatus(status) != PGRES_TUPLES_OK 1.35 + PQresultStatus(res) != PGRES_COMMAND_OK && 1.36 + PQresultStatus(res) != PGRES_TUPLES_OK 1.37 ) { 1.38 - fprintf(stderr, "Error while executing SQL command deleting expired sessions:\n%s", PQresultErrorMessage(status)); 1.39 + fprintf(stderr, "Error while executing SQL command deleting expired sessions:\n%s", PQresultErrorMessage(res)); 1.40 err = 1; 1.41 - PQclear(status); 1.42 + PQclear(res); 1.43 } else { 1.44 - PQclear(status); 1.45 + PQclear(res); 1.46 } 1.47 1.48 // check member activity: 1.49 - status = PQexec(db, "SELECT \"check_activity\"()"); 1.50 - if (!status) { 1.51 + res = PQexec(db, "SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SELECT \"check_activity\"()"); 1.52 + if (!res) { 1.53 fprintf(stderr, "Error in pqlib while sending SQL command checking member activity\n"); 1.54 err = 1; 1.55 } else if ( 1.56 - PQresultStatus(status) != PGRES_COMMAND_OK && 1.57 - PQresultStatus(status) != PGRES_TUPLES_OK 1.58 + PQresultStatus(res) != PGRES_COMMAND_OK && 1.59 + PQresultStatus(res) != PGRES_TUPLES_OK 1.60 ) { 1.61 - fprintf(stderr, "Error while executing SQL command checking member activity:\n%s", PQresultErrorMessage(status)); 1.62 + fprintf(stderr, "Error while executing SQL command checking member activity:\n%s", PQresultErrorMessage(res)); 1.63 err = 1; 1.64 - PQclear(status); 1.65 + PQclear(res); 1.66 } else { 1.67 - PQclear(status); 1.68 + PQclear(res); 1.69 } 1.70 1.71 // calculate member counts: 1.72 - status = PQexec(db, "SELECT \"calculate_member_counts\"()"); 1.73 - if (!status) { 1.74 + res = PQexec(db, "SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; SELECT \"calculate_member_counts\"()"); 1.75 + if (!res) { 1.76 fprintf(stderr, "Error in pqlib while sending SQL command calculating member counts\n"); 1.77 err = 1; 1.78 } else if ( 1.79 - PQresultStatus(status) != PGRES_COMMAND_OK && 1.80 - PQresultStatus(status) != PGRES_TUPLES_OK 1.81 + PQresultStatus(res) != PGRES_COMMAND_OK && 1.82 + PQresultStatus(res) != PGRES_TUPLES_OK 1.83 ) { 1.84 - fprintf(stderr, "Error while executing SQL command calculating member counts:\n%s", PQresultErrorMessage(status)); 1.85 + fprintf(stderr, "Error while executing SQL command calculating member counts:\n%s", PQresultErrorMessage(res)); 1.86 err = 1; 1.87 - PQclear(status); 1.88 + PQclear(res); 1.89 } else { 1.90 - PQclear(status); 1.91 + PQclear(res); 1.92 } 1.93 1.94 // update open issues: 1.95 - list = PQexec(db, "SELECT \"id\" FROM \"open_issue\""); 1.96 - if (!list) { 1.97 + res = PQexec(db, "SELECT \"id\" FROM \"open_issue\""); 1.98 + if (!res) { 1.99 fprintf(stderr, "Error in pqlib while sending SQL command selecting open issues\n"); 1.100 err = 1; 1.101 - } else if (PQresultStatus(list) != PGRES_TUPLES_OK) { 1.102 - fprintf(stderr, "Error while executing SQL command selecting open issues:\n%s", PQresultErrorMessage(list)); 1.103 + } else if (PQresultStatus(res) != PGRES_TUPLES_OK) { 1.104 + fprintf(stderr, "Error while executing SQL command selecting open issues:\n%s", PQresultErrorMessage(res)); 1.105 err = 1; 1.106 - PQclear(list); 1.107 + PQclear(res); 1.108 } else { 1.109 - count = PQntuples(list); 1.110 + count = PQntuples(res); 1.111 for (i=0; i<count; i++) { 1.112 - const char *params[1]; 1.113 - params[0] = PQgetvalue(list, i, 0); 1.114 - status = PQexecParams( 1.115 - db, "SELECT \"check_issue\"($1)", 1, NULL, params, NULL, NULL, 0 1.116 - ); 1.117 - if (!status) { 1.118 - fprintf(stderr, "Error in pqlib while sending SQL command to call function \"check_issue\"(...):\n"); 1.119 - err = 1; 1.120 - } else if ( 1.121 - PQresultStatus(status) != PGRES_COMMAND_OK && 1.122 - PQresultStatus(status) != PGRES_TUPLES_OK 1.123 - ) { 1.124 - fprintf(stderr, "Error while calling SQL function \"check_issue\"(...):\n%s", PQresultErrorMessage(status)); 1.125 - err = 1; 1.126 - PQclear(status); 1.127 - } else { 1.128 - PQclear(status); 1.129 + char *issue_id, *escaped_issue_id; 1.130 + PGresult *res2, *old_res2; 1.131 + int j; 1.132 + issue_id = PQgetvalue(res, i, 0); 1.133 + escaped_issue_id = PQescapeLiteral(db, issue_id, strlen(issue_id)); 1.134 + old_res2 = NULL; 1.135 + for (j=0; ; j++) { 1.136 + fprintf(stderr, "%i\n", j); // DEBUG 1.137 + if (j >= 20) { // safety to avoid endless loops 1.138 + fprintf(stderr, "Function \"check_issue\"(...) returned non-null value too often.\n"); 1.139 + err = 1; 1.140 + if (j > 0) PQclear(old_res2); 1.141 + break; 1.142 + } 1.143 + if (j == 0) { 1.144 + char *cmd; 1.145 + if (asprintf(&cmd, "SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; SELECT \"check_issue\"(%s, NULL)", escaped_issue_id) < 0) { 1.146 + fprintf(stderr, "Could not prepare query string in memory.\n"); 1.147 + err = 1; 1.148 + break; 1.149 + } 1.150 + res2 = PQexec(db, cmd); 1.151 + free(cmd); 1.152 + } else { 1.153 + char *persist, *escaped_persist, *cmd; 1.154 + persist = PQgetvalue(old_res2, 0, 0); 1.155 + escaped_persist = PQescapeLiteral(db, persist, strlen(persist)); 1.156 + if (asprintf(&cmd, "SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; SELECT \"check_issue\"(%s, %s::\"check_issue_persistence\")", escaped_issue_id, escaped_persist) < 0) { 1.157 + PQfreemem(escaped_persist); 1.158 + fprintf(stderr, "Could not prepare query string in memory.\n"); 1.159 + err = 1; 1.160 + PQclear(old_res2); 1.161 + break; 1.162 + } 1.163 + PQfreemem(escaped_persist); 1.164 + res2 = PQexec(db, cmd); 1.165 + free(cmd); 1.166 + PQclear(old_res2); 1.167 + } 1.168 + if (!res2) { 1.169 + fprintf(stderr, "Error in pqlib while sending SQL command to call function \"check_issue\"(...):\n"); 1.170 + err = 1; 1.171 + break; 1.172 + } else if ( 1.173 + PQresultStatus(res2) != PGRES_COMMAND_OK && 1.174 + PQresultStatus(res2) != PGRES_TUPLES_OK 1.175 + ) { 1.176 + fprintf(stderr, "Error while calling SQL function \"check_issue\"(...):\n%s", PQresultErrorMessage(res2)); 1.177 + err = 1; 1.178 + PQclear(res2); 1.179 + break; 1.180 + } else { 1.181 + if (PQntuples(res2) >= 1 && !PQgetisnull(res2, 0, 0)) { 1.182 + old_res2 = res2; 1.183 + } else { 1.184 + PQclear(res2); 1.185 + break; 1.186 + } 1.187 + } 1.188 } 1.189 + PQfreemem(escaped_issue_id); 1.190 } 1.191 - PQclear(list); 1.192 - } 1.193 - 1.194 - // calculate ranks after voting is finished: 1.195 - // (NOTE: This is a seperate process to avoid long transactions with locking) 1.196 - list = PQexec(db, "SELECT \"id\" FROM \"issue_with_ranks_missing\""); 1.197 - if (!list) { 1.198 - fprintf(stderr, "Error in pqlib while sending SQL command selecting issues where ranks are missing\n"); 1.199 - err = 1; 1.200 - } else if (PQresultStatus(list) != PGRES_TUPLES_OK) { 1.201 - fprintf(stderr, "Error while executing SQL command selecting issues where ranks are missing:\n%s", PQresultErrorMessage(list)); 1.202 - err = 1; 1.203 - PQclear(list); 1.204 - } else { 1.205 - count = PQntuples(list); 1.206 - for (i=0; i<count; i++) { 1.207 - const char *params[1]; 1.208 - params[0] = PQgetvalue(list, i, 0); 1.209 - status = PQexecParams( 1.210 - db, "SELECT \"calculate_ranks\"($1)", 1, NULL, params, NULL, NULL, 0 1.211 - ); 1.212 - if (!status) { 1.213 - fprintf(stderr, "Error in pqlib while sending SQL command to call function \"calculate_ranks\"(...):\n"); 1.214 - err = 1; 1.215 - } else if ( 1.216 - PQresultStatus(status) != PGRES_COMMAND_OK && 1.217 - PQresultStatus(status) != PGRES_TUPLES_OK 1.218 - ) { 1.219 - fprintf(stderr, "Error while calling SQL function \"calculate_ranks\"(...):\n%s", PQresultErrorMessage(status)); 1.220 - err = 1; 1.221 - PQclear(status); 1.222 - } else { 1.223 - PQclear(status); 1.224 - } 1.225 - } 1.226 - PQclear(list); 1.227 + PQclear(res); 1.228 } 1.229 1.230 // cleanup and exit