X-Git-Url: https://git.tokkee.org/?a=blobdiff_plain;f=src%2Fpostgresql.c;h=a5bacf49b2a4fc8df8aeaabbbdb2c8b4e3e63e4b;hb=568420ac172981edccb8587cc9651952fe350365;hp=5c5fbb38e0d26a21972e9152bc8f75588a5c1e08;hpb=09c001b38efcb0344c517c5944df87127ea30ee6;p=collectd.git diff --git a/src/postgresql.c b/src/postgresql.c index 5c5fbb38..a5bacf49 100644 --- a/src/postgresql.c +++ b/src/postgresql.c @@ -76,6 +76,12 @@ C_PSQL_IS_UNIX_DOMAIN_SOCKET (host) ? "/.s.PGSQL." : ":", \ port +typedef enum { + C_PSQL_PARAM_HOST = 1, + C_PSQL_PARAM_DB, + C_PSQL_PARAM_USER, +} c_psql_param_t; + typedef struct { char *type; char *type_instance; @@ -86,14 +92,24 @@ typedef struct { char *name; char *query; + c_psql_param_t *params; + int params_num; + c_psql_col_t *cols; int cols_num; + + int min_pg_version; + int max_pg_version; } c_psql_query_t; typedef struct { PGconn *conn; c_complain_t conn_complaint; + int proto_version; + + int max_params_num; + /* user configuration */ c_psql_query_t **queries; int queries_num; @@ -112,8 +128,13 @@ typedef struct { } c_psql_database_t; static char *def_queries[] = { - "user_tables", - "io_user_tables" + "backends", + "transactions", + "queries", + "query_plans", + "table_states", + "disk_io", + "disk_usage" }; static int def_queries_num = STATIC_ARRAY_SIZE (def_queries); @@ -138,8 +159,14 @@ static c_psql_query_t *c_psql_query_new (const char *name) query->name = sstrdup (name); query->query = NULL; + query->params = NULL; + query->params_num = 0; + query->cols = NULL; query->cols_num = 0; + + query->min_pg_version = 0; + query->max_pg_version = INT_MAX; return query; } /* c_psql_query_new */ @@ -150,6 +177,9 @@ static void c_psql_query_delete (c_psql_query_t *query) sfree (query->name); sfree (query->query); + sfree (query->params); + query->params_num = 0; + for (i = 0; i < query->cols_num; ++i) { sfree (query->cols[i].type); sfree (query->cols[i].type_instance); @@ -159,12 +189,15 @@ static void c_psql_query_delete (c_psql_query_t *query) return; } /* c_psql_query_delete */ -static c_psql_query_t *c_psql_query_get (const char *name) +static c_psql_query_t *c_psql_query_get (const char *name, int server_version) { int i; for (i = 0; i < queries_num; ++i) - if (0 == strcasecmp (name, queries[i].name)) + if (0 == strcasecmp (name, queries[i].name) + && ((-1 == server_version) + || ((queries[i].min_pg_version <= server_version) + && (server_version <= queries[i].max_pg_version)))) return queries + i; return NULL; } /* c_psql_query_get */ @@ -184,8 +217,11 @@ static c_psql_database_t *c_psql_database_new (const char *name) db->conn = NULL; - db->conn_complaint.last = 0; - db->conn_complaint.interval = 0; + C_COMPLAIN_INIT (&db->conn_complaint); + + db->proto_version = 0; + + db->max_params_num = 0; db->queries = NULL; db->queries_num = 0; @@ -207,6 +243,7 @@ static c_psql_database_t *c_psql_database_new (const char *name) static void c_psql_database_delete (c_psql_database_t *db) { PQfinish (db->conn); + db->conn = NULL; sfree (db->queries); db->queries_num = 0; @@ -299,6 +336,11 @@ static int c_psql_check_connection (c_psql_database_t *db) db->database, PQerrorMessage (db->conn)); return -1; } + + db->proto_version = PQprotocolVersion (db->conn); + if (3 > db->proto_version) + log_warn ("Protocol version %d does not support parameters.", + db->proto_version); } c_release (LOG_INFO, &db->conn_complaint, @@ -306,6 +348,42 @@ static int c_psql_check_connection (c_psql_database_t *db) return 0; } /* c_psql_check_connection */ +static PGresult *c_psql_exec_query_params (c_psql_database_t *db, + c_psql_query_t *query) +{ + char *params[db->max_params_num]; + int i; + + assert (db->max_params_num >= query->params_num); + + for (i = 0; i < query->params_num; ++i) { + switch (query->params[i]) { + case C_PSQL_PARAM_HOST: + params[i] = C_PSQL_IS_UNIX_DOMAIN_SOCKET (db->host) + ? "localhost" : db->host; + break; + case C_PSQL_PARAM_DB: + params[i] = db->database; + break; + case C_PSQL_PARAM_USER: + params[i] = db->user; + break; + default: + assert (0); + } + } + + return PQexecParams (db->conn, query->query, query->params_num, NULL, + (const char *const *)((0 == query->params_num) ? NULL : params), + NULL, NULL, /* return text data */ 0); +} /* c_psql_exec_query_params */ + +static PGresult *c_psql_exec_query_noparams (c_psql_database_t *db, + c_psql_query_t *query) +{ + return PQexec (db->conn, query->query); +} /* c_psql_exec_query_noparams */ + static int c_psql_exec_query (c_psql_database_t *db, int idx) { c_psql_query_t *query; @@ -319,7 +397,16 @@ static int c_psql_exec_query (c_psql_database_t *db, int idx) query = db->queries[idx]; - res = PQexec (db->conn, query->query); + if (3 <= db->proto_version) + res = c_psql_exec_query_params (db, query); + else if (0 == query->params_num) + res = c_psql_exec_query_noparams (db, query); + else { + log_err ("Connection to database \"%s\" does not support parameters " + "(protocol version %d) - cannot execute query \"%s\".", + db->database, db->proto_version, query->name); + return -1; + } if (PGRES_TUPLES_OK != PQresultStatus (res)) { log_err ("Failed to execute SQL query: %s", @@ -330,14 +417,17 @@ static int c_psql_exec_query (c_psql_database_t *db, int idx) } rows = PQntuples (res); - if (1 > rows) + if (1 > rows) { + PQclear (res); return 0; + } cols = PQnfields (res); if (query->cols_num != cols) { log_err ("SQL query returned wrong number of fields " "(expected: %i, got: %i)", query->cols_num, cols); log_info ("SQL query was: %s", query->query); + PQclear (res); return -1; } @@ -355,53 +445,9 @@ static int c_psql_exec_query (c_psql_database_t *db, int idx) submit_gauge (db, col.type, col.type_instance, value); } } - return 0; -} /* c_psql_exec_query */ - -static int c_psql_stat_database (c_psql_database_t *db) -{ - const char *const query = - "SELECT numbackends, xact_commit, xact_rollback " - "FROM pg_stat_database " - "WHERE datname = $1;"; - - PGresult *res; - - int n; - - res = PQexecParams (db->conn, query, /* number of parameters */ 1, - NULL, (const char *const *)&db->database, NULL, NULL, - /* return text data */ 0); - - if (PGRES_TUPLES_OK != PQresultStatus (res)) { - log_err ("Failed to execute SQL query: %s", - PQerrorMessage (db->conn)); - log_info ("SQL query was: %s", query); - PQclear (res); - return -1; - } - - n = PQntuples (res); - if (1 < n) { - log_warn ("pg_stat_database has more than one entry " - "for database %s - ignoring additional results.", - db->database); - } - else if (1 > n) { - log_err ("pg_stat_database has no entry for database %s", - db->database); - PQclear (res); - return -1; - } - - submit_gauge (db, "pg_numbackends", NULL, PQgetvalue (res, 0, 0)); - - submit_counter (db, "pg_xact", "commit", PQgetvalue (res, 0, 1)); - submit_counter (db, "pg_xact", "rollback", PQgetvalue (res, 0, 2)); - PQclear (res); return 0; -} /* c_psql_stat_database */ +} /* c_psql_exec_query */ static int c_psql_read (void) { @@ -418,8 +464,6 @@ static int c_psql_read (void) if (0 != c_psql_check_connection (db)) continue; - c_psql_stat_database (db); - for (j = 0; j < db->queries_num; ++j) c_psql_exec_query (db, j); @@ -504,6 +548,14 @@ static int c_psql_init (void) char *server_host; int server_version; + int j; + + /* this will happen during reinitialization */ + if (NULL != db->conn) { + c_psql_check_connection (db); + continue; + } + status = ssnprintf (buf, buf_len, "dbname = '%s'", db->database); if (0 < status) { buf += status; @@ -522,6 +574,8 @@ static int c_psql_init (void) if (0 != c_psql_check_connection (db)) continue; + db->proto_version = PQprotocolVersion (db->conn); + server_host = PQhost (db->conn); server_version = PQserverVersion (db->conn); log_info ("Sucessfully connected to database %s (user %s) " @@ -530,7 +584,38 @@ static int c_psql_init (void) PQdb (db->conn), PQuser (db->conn), C_PSQL_SOCKET3 (server_host, PQport (db->conn)), C_PSQL_SERVER_VERSION3 (server_version), - PQprotocolVersion (db->conn), PQbackendPID (db->conn)); + db->proto_version, PQbackendPID (db->conn)); + + if (3 > db->proto_version) + log_warn ("Protocol version %d does not support parameters.", + db->proto_version); + + /* Now that we know the PostgreSQL server version, we can get the + * right version of each query definition. */ + for (j = 0; j < db->queries_num; ++j) { + c_psql_query_t *tmp; + + tmp = c_psql_query_get (db->queries[j]->name, server_version); + + if (tmp == db->queries[j]) + continue; + + if (NULL == tmp) { + log_err ("Query \"%s\" not found for server version %i - " + "please check your configuration.", + db->queries[j]->name, server_version); + + if (db->queries_num - j - 1 > 0) + memmove (db->queries + j, db->queries + j + 1, + (db->queries_num - j - 1) * sizeof (*db->queries)); + + --db->queries_num; + --j; + continue; + } + + db->queries[j] = tmp; + } } plugin_register_read ("postgresql", c_psql_read); @@ -538,7 +623,7 @@ static int c_psql_init (void) return 0; } /* c_psql_init */ -static int config_set (char *name, char **var, const oconfig_item_t *ci) +static int config_set_s (char *name, char **var, const oconfig_item_t *ci) { if ((0 != ci->children_num) || (1 != ci->values_num) || (OCONFIG_TYPE_STRING != ci->values[0].type)) { @@ -549,7 +634,53 @@ static int config_set (char *name, char **var, const oconfig_item_t *ci) sfree (*var); *var = sstrdup (ci->values[0].value.string); return 0; -} /* config_set */ +} /* config_set_s */ + +static int config_set_i (char *name, int *var, const oconfig_item_t *ci) +{ + if ((0 != ci->children_num) || (1 != ci->values_num) + || (OCONFIG_TYPE_NUMBER != ci->values[0].type)) { + log_err ("%s expects a single number argument.", name); + return 1; + } + + *var = (int)ci->values[0].value.number; + return 0; +} /* config_set_i */ + +static int config_set_param (c_psql_query_t *query, const oconfig_item_t *ci) +{ + c_psql_param_t param; + char *param_str; + + if ((0 != ci->children_num) || (1 != ci->values_num) + || (OCONFIG_TYPE_STRING != ci->values[0].type)) { + log_err ("Param expects a single string argument."); + return 1; + } + + param_str = ci->values[0].value.string; + if (0 == strcasecmp (param_str, "hostname")) + param = C_PSQL_PARAM_HOST; + else if (0 == strcasecmp (param_str, "database")) + param = C_PSQL_PARAM_DB; + else if (0 == strcasecmp (param_str, "username")) + param = C_PSQL_PARAM_USER; + else { + log_err ("Invalid parameter \"%s\".", param_str); + return 1; + } + + ++query->params_num; + if (NULL == (query->params = (c_psql_param_t *)realloc (query->params, + query->params_num * sizeof (*query->params)))) { + log_err ("Out of memory."); + exit (5); + } + + query->params[query->params_num - 1] = param; + return 0; +} /* config_set_param */ static int config_set_column (c_psql_query_t *query, const oconfig_item_t *ci) { @@ -587,20 +718,14 @@ static int config_set_column (c_psql_query_t *query, const oconfig_item_t *ci) return 0; } /* config_set_column */ -static int config_set_query (c_psql_database_t *db, const oconfig_item_t *ci) +static int set_query (c_psql_database_t *db, const char *name) { c_psql_query_t *query; - if ((0 != ci->children_num) || (1 != ci->values_num) - || (OCONFIG_TYPE_STRING != ci->values[0].type)) { - log_err ("Query expects a single string argument."); - return 1; - } - - query = c_psql_query_get (ci->values[0].value.string); + query = c_psql_query_get (name, -1); if (NULL == query) { log_err ("Query \"%s\" not found - please check your configuration.", - ci->values[0].value.string); + name); return 1; } @@ -611,8 +736,21 @@ static int config_set_query (c_psql_database_t *db, const oconfig_item_t *ci) exit (5); } + if (query->params_num > db->max_params_num) + db->max_params_num = query->params_num; + db->queries[db->queries_num - 1] = query; return 0; +} /* set_query */ + +static int config_set_query (c_psql_database_t *db, const oconfig_item_t *ci) +{ + if ((0 != ci->children_num) || (1 != ci->values_num) + || (OCONFIG_TYPE_STRING != ci->values[0].type)) { + log_err ("Query expects a single string argument."); + return 1; + } + return set_query (db, ci->values[0].value.string); } /* config_set_query */ static int c_psql_config_query (oconfig_item_t *ci) @@ -633,12 +771,48 @@ static int c_psql_config_query (oconfig_item_t *ci) oconfig_item_t *c = ci->children + i; if (0 == strcasecmp (c->key, "Query")) - config_set ("Query", &query->query, c); + config_set_s ("Query", &query->query, c); + else if (0 == strcasecmp (c->key, "Param")) + config_set_param (query, c); else if (0 == strcasecmp (c->key, "Column")) config_set_column (query, c); + else if (0 == strcasecmp (c->key, "MinPGVersion")) + config_set_i ("MinPGVersion", &query->min_pg_version, c); + else if (0 == strcasecmp (c->key, "MaxPGVersion")) + config_set_i ("MaxPGVersion", &query->max_pg_version, c); else log_warn ("Ignoring unknown config key \"%s\".", c->key); } + + for (i = 0; i < queries_num - 1; ++i) { + c_psql_query_t *q = queries + i; + + if ((0 == strcasecmp (q->name, query->name)) + && (q->min_pg_version <= query->max_pg_version) + && (query->min_pg_version <= q->max_pg_version)) { + log_err ("Ignoring redefinition (with overlapping version ranges) " + "of query \"%s\".", query->name); + c_psql_query_delete (query); + --queries_num; + return 1; + } + } + + if (query->min_pg_version > query->max_pg_version) { + log_err ("Query \"%s\": MinPGVersion > MaxPGVersion.", + query->name); + c_psql_query_delete (query); + --queries_num; + return 1; + } + + if (NULL == query->query) { + log_err ("Query \"%s\" does not include an SQL query string - " + "please check your configuration.", query->name); + c_psql_query_delete (query); + --queries_num; + return 1; + } return 0; } /* c_psql_config_query */ @@ -660,19 +834,19 @@ static int c_psql_config_database (oconfig_item_t *ci) oconfig_item_t *c = ci->children + i; if (0 == strcasecmp (c->key, "Host")) - config_set ("Host", &db->host, c); + config_set_s ("Host", &db->host, c); else if (0 == strcasecmp (c->key, "Port")) - config_set ("Port", &db->port, c); + config_set_s ("Port", &db->port, c); else if (0 == strcasecmp (c->key, "User")) - config_set ("User", &db->user, c); + config_set_s ("User", &db->user, c); else if (0 == strcasecmp (c->key, "Password")) - config_set ("Password", &db->password, c); + config_set_s ("Password", &db->password, c); else if (0 == strcasecmp (c->key, "SSLMode")) - config_set ("SSLMode", &db->sslmode, c); + config_set_s ("SSLMode", &db->sslmode, c); else if (0 == strcasecmp (c->key, "KRBSrvName")) - config_set ("KRBSrvName", &db->krbsrvname, c); + config_set_s ("KRBSrvName", &db->krbsrvname, c); else if (0 == strcasecmp (c->key, "Service")) - config_set ("Service", &db->service, c); + config_set_s ("Service", &db->service, c); else if (0 == strcasecmp (c->key, "Query")) config_set_query (db, c); else @@ -680,18 +854,8 @@ static int c_psql_config_database (oconfig_item_t *ci) } if (NULL == db->queries) { - db->queries = (c_psql_query_t **)malloc (def_queries_num - * sizeof (*db->queries)); - - for (i = 0; i < def_queries_num; ++i) { - db->queries[i] = c_psql_query_get (def_queries[i]); - if (NULL == db->queries[i]) - log_err ("Query \"%s\" not found - " - "please check your installation.", - def_queries[i]); - else - ++db->queries_num; - } + for (i = 0; i < def_queries_num; ++i) + set_query (db, def_queries[i]); } return 0; }