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
     2.1 --- a/test.sql	Fri Feb 15 02:51:56 2013 +0100
     2.2 +++ b/test.sql	Tue Feb 19 14:40:38 2013 +0100
     2.3 @@ -469,5 +469,6 @@
     2.4          0, 100,
     2.5          0, 100
     2.6      );
     2.7 +
     2.8  END;
     2.9  

Impressum / About Us