Ejemplo n.º 1
0
static int c_psql_check_connection (c_psql_database_t *db)
{
	_Bool init = 0;

	if (! db->conn) {
		init = 1;

		/* trigger c_release() */
		if (0 == db->conn_complaint.interval)
			db->conn_complaint.interval = 1;

		c_psql_connect (db);
	}

	/* "ping" */
	PQclear (PQexec (db->conn, "SELECT 42;"));

	if (CONNECTION_OK != PQstatus (db->conn)) {
		PQreset (db->conn);

		/* trigger c_release() */
		if (0 == db->conn_complaint.interval)
			db->conn_complaint.interval = 1;

		if (CONNECTION_OK != PQstatus (db->conn)) {
			c_complain (LOG_ERR, &db->conn_complaint,
					"Failed to connect to database %s (%s): %s",
					db->database, db->instance,
					PQerrorMessage (db->conn));
			return -1;
		}

		db->proto_version = PQprotocolVersion (db->conn);
	}

	db->server_version = PQserverVersion (db->conn);

	if (c_would_release (&db->conn_complaint)) {
		char *server_host;
		int   server_version;

		server_host    = PQhost (db->conn);
		server_version = PQserverVersion (db->conn);

		c_do_release (LOG_INFO, &db->conn_complaint,
				"Successfully %sconnected to database %s (user %s) "
				"at server %s%s%s (server version: %d.%d.%d, "
				"protocol version: %d, pid: %d)", init ? "" : "re",
				PQdb (db->conn), PQuser (db->conn),
				C_PSQL_SOCKET3 (server_host, PQport (db->conn)),
				C_PSQL_SERVER_VERSION3 (server_version),
				db->proto_version, PQbackendPID (db->conn));

		if (3 > db->proto_version)
			log_warn ("Protocol version %d does not support parameters.",
					db->proto_version);
	}
	return 0;
} /* c_psql_check_connection */
Ejemplo n.º 2
0
PQLExtension *
getExtensions(PGconn *c, int *n)
{
	PQLExtension	*e;
	PGresult		*res;
	int				i;

	logNoise("extension: server version: %d", PQserverVersion(c));

	/* bail out if we do not support it */
	if (PQserverVersion(c) < 90100)
	{
		logWarning("ignoring extensions because server does not support it");
		return NULL;
	}

	res = PQexec(c,
				 "SELECT e.oid, extname AS extensionname, nspname, extversion AS version, extrelocatable, obj_description(e.oid, 'pg_extension') AS description FROM pg_extension e LEFT JOIN pg_namespace n ON (e.extnamespace = n.oid) ORDER BY extname");

	if (PQresultStatus(res) != PGRES_TUPLES_OK)
	{
		logError("query failed: %s", PQresultErrorMessage(res));
		PQclear(res);
		PQfinish(c);
		/* XXX leak another connection? */
		exit(EXIT_FAILURE);
	}

	*n = PQntuples(res);
	if (*n > 0)
		e = (PQLExtension *) malloc(*n * sizeof(PQLExtension));
	else
		e = NULL;

	logDebug("number of extensions in server: %d", *n);

	for (i = 0; i < *n; i++)
	{
		e[i].oid = strtoul(PQgetvalue(res, i, PQfnumber(res, "oid")), NULL, 10);
		e[i].extensionname = strdup(PQgetvalue(res, i, PQfnumber(res,
											   "extensionname")));
		e[i].schemaname = strdup(PQgetvalue(res, i, PQfnumber(res, "nspname")));
		e[i].version = strdup(PQgetvalue(res, i, PQfnumber(res, "version")));
		e[i].relocatable = (PQgetvalue(res, i, PQfnumber(res,
									   "extrelocatable"))[0] == 't');
		if (PQgetisnull(res, i, PQfnumber(res, "description")))
			e[i].comment = NULL;
		else
			e[i].comment = strdup(PQgetvalue(res, i, PQfnumber(res, "description")));

		logDebug("extension \"%s\"", e[i].extensionname);
	}

	PQclear(res);

	return e;
}
Ejemplo n.º 3
0
static void
_check_database_version(ArchiveHandle *AH)
{
	const char *remoteversion_str;
	int			remoteversion;

	remoteversion_str = PQparameterStatus(AH->connection, "server_version");
	remoteversion = PQserverVersion(AH->connection);
	if (remoteversion == 0 || !remoteversion_str)
		exit_horribly(modulename, "could not get server_version from libpq\n");

	AH->public__.remoteVersionStr = pg_strdup(remoteversion_str);
	AH->public__.remoteVersion = remoteversion;
	if (!AH->archiveRemoteVersion)
		AH->archiveRemoteVersion = AH->public__.remoteVersionStr;

	if (remoteversion != PG_VERSION_NUM
		&& (remoteversion < AH->public__.minRemoteVersion ||
			remoteversion > AH->public__.maxRemoteVersion))
	{
		write_msg(NULL, "server version: %s; %s version: %s\n",
				  remoteversion_str, progname, PG_VERSION);
		exit_horribly(NULL, "aborting because of server version mismatch\n");
	}
}
Ejemplo n.º 4
0
void
DropBlobIfExists(ArchiveHandle *AH, Oid oid)
{
	/*
	 * If we are not restoring to a direct database connection, we have to
	 * guess about how to detect whether the blob exists.  Assume new-style.
	 */
	if (AH->connection == NULL ||
		PQserverVersion(AH->connection) >= 90000)
	{
		ahprintf(AH,
				 "SELECT pg_catalog.lo_unlink(oid) "
				 "FROM pg_catalog.pg_largeobject_metadata "
				 "WHERE oid = '%u';\n",
				 oid);
	}
	else
	{
		/* Restoring to pre-9.0 server, so do it the old way */
		ahprintf(AH,
				 "SELECT CASE WHEN EXISTS("
				 "SELECT 1 FROM pg_catalog.pg_largeobject WHERE loid = '%u'"
				 ") THEN pg_catalog.lo_unlink('%u') END;\n",
				 oid, oid);
	}
}
void Database_PostgreSQL::connectToDatabase()
{
	m_conn = PQconnectdb(m_connect_string.c_str());

	if (PQstatus(m_conn) != CONNECTION_OK) {
		throw DatabaseException(std::string(
			"PostgreSQL database error: ") +
			PQerrorMessage(m_conn));
	}

	m_pgversion = PQserverVersion(m_conn);

	/*
	* We are using UPSERT feature from PostgreSQL 9.5
	* to have the better performance where possible.
	*/
	if (m_pgversion < 90500) {
		warningstream << "Your PostgreSQL server lacks UPSERT "
			<< "support. Use version 9.5 or better if possible."
			<< std::endl;
	}

	infostream << "PostgreSQL Database: Version " << m_pgversion
			<< " Connection made." << std::endl;

	createDatabase();
	initStatements();
}
Ejemplo n.º 6
0
/*
 * Issue SET commands to make sure remote session is configured properly.
 *
 * We do this just once at connection, assuming nothing will change the
 * values later.  Since we'll never send volatile function calls to the
 * remote, there shouldn't be any way to break this assumption from our end.
 * It's possible to think of ways to break it at the remote end, eg making
 * a foreign table point to a view that includes a set_config call ---
 * but once you admit the possibility of a malicious view definition,
 * there are any number of ways to break things.
 */
static void
configure_remote_session(PGconn *conn)
{
	int			remoteversion = PQserverVersion(conn);

	/* Force the search path to contain only pg_catalog (see deparse.c) */
	do_sql_command(conn, "SET search_path = pg_catalog");

	/*
	 * Set remote timezone; this is basically just cosmetic, since all
	 * transmitted and returned timestamptzs should specify a zone explicitly
	 * anyway.	However it makes the regression test outputs more predictable.
	 *
	 * We don't risk setting remote zone equal to ours, since the remote
	 * server might use a different timezone database.	Instead, use UTC
	 * (quoted, because very old servers are picky about case).
	 */
	do_sql_command(conn, "SET timezone = 'UTC'");

	/*
	 * Set values needed to ensure unambiguous data output from remote.  (This
	 * logic should match what pg_dump does.  See also set_transmission_modes
	 * in postgres_fdw.c.)
	 */
	do_sql_command(conn, "SET datestyle = ISO");
	if (remoteversion >= 80400)
		do_sql_command(conn, "SET intervalstyle = postgres");
	if (remoteversion >= 90000)
		do_sql_command(conn, "SET extra_float_digits = 3");
	else
		do_sql_command(conn, "SET extra_float_digits = 2");
}
Ejemplo n.º 7
0
/* [Re]connects to database */
static GSM_Error SMSDPgSQL_Connect(GSM_SMSDConfig * Config)
{
	unsigned char buf[400];
	PGresult *Res;

	unsigned int port = 5432;
	char *pport;

	pport = strstr(Config->host, ":");
	if (pport) {
		*pport++ = '\0';
		port = atoi(pport);
	}

	sprintf(buf, "host = '%s' user = '******' password = '******' dbname = '%s' port = %d", Config->host, Config->user, Config->password, Config->database, port);

	SMSDPgSQL_Free(Config);
	Config->conn.pg = PQconnectdb(buf);
	if (PQstatus(Config->conn.pg) != CONNECTION_OK) {
		SMSD_Log(DEBUG_ERROR, Config, "Error connecting to database: %s", PQerrorMessage(Config->conn.pg));
		PQfinish(Config->conn.pg);
		return ERR_DB_CONNECT;
	}

	Res = PQexec(Config->conn.pg, "SET NAMES UTF8");
	PQclear(Res);
	SMSD_Log(DEBUG_INFO, Config, "Connected to database: %s on %s. Server version: %d Protocol: %d",
		 PQdb(Config->conn.pg), PQhost(Config->conn.pg), PQserverVersion(Config->conn.pg), PQprotocolVersion(Config->conn.pg));

	return ERR_NONE;
}
Ejemplo n.º 8
0
static int c_psql_check_connection (c_psql_database_t *db)
{
	/* "ping" */
	PQclear (PQexec (db->conn, "SELECT 42;"));

	if (CONNECTION_OK != PQstatus (db->conn)) {
		PQreset (db->conn);

		/* trigger c_release() */
		if (0 == db->conn_complaint.interval)
			db->conn_complaint.interval = 1;

		if (CONNECTION_OK != PQstatus (db->conn)) {
			c_complain (LOG_ERR, &db->conn_complaint,
					"Failed to connect to database %s: %s",
					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);
	}

	db->server_version = PQserverVersion (db->conn);

	c_release (LOG_INFO, &db->conn_complaint,
			"Successfully reconnected to database %s", PQdb (db->conn));
	return 0;
} /* c_psql_check_connection */
Ejemplo n.º 9
0
/*
 * Convert a string value to an SQL string literal and append it to
 * the given buffer.  Encoding and string syntax rules are as indicated
 * by current settings of the PGconn.
 */
void
appendStringLiteralConn(PQExpBuffer buf, const char *str, PGconn *conn)
{
	size_t		length = strlen(str);

	/*
	 * XXX This is a kluge to silence escape_string_warning in our utility
	 * programs.  It should go away someday.
	 */
	if (strchr(str, '\\') != NULL && PQserverVersion(conn) >= 80100)
	{
		/* ensure we are not adjacent to an identifier */
		if (buf->len > 0 && buf->data[buf->len - 1] != ' ')
			appendPQExpBufferChar(buf, ' ');
		appendPQExpBufferChar(buf, ESCAPE_STRING_SYNTAX);
		appendStringLiteral(buf, str, PQclientEncoding(conn), false);
		return;
	}
	/* XXX end kluge */

	if (!enlargePQExpBuffer(buf, 2 * length + 2))
		return;
	appendPQExpBufferChar(buf, '\'');
	buf->len += PQescapeStringConn(conn, buf->data + buf->len,
								   str, length, NULL);
	appendPQExpBufferChar(buf, '\'');
}
Ejemplo n.º 10
0
PQLSequence *
getSequences(PGconn *c, int *n)
{
	PQLSequence		*s;
	PGresult		*res;
	int				i;

	logNoise("sequence: server version: %d", PQserverVersion(c));

	res = PQexec(c,
				 "SELECT c.oid, n.nspname, c.relname, obj_description(c.oid, 'pg_class') AS description, pg_get_userbyid(c.relowner) AS relowner, relacl FROM pg_class c INNER JOIN pg_namespace n ON (c.relnamespace = n.oid) WHERE relkind = 'S' AND nspname !~ '^pg_' AND nspname <> 'information_schema' ORDER BY nspname, relname");

	if (PQresultStatus(res) != PGRES_TUPLES_OK)
	{
		logError("query failed: %s", PQresultErrorMessage(res));
		PQclear(res);
		PQfinish(c);
		/* XXX leak another connection? */
		exit(EXIT_FAILURE);
	}

	*n = PQntuples(res);
	if (*n > 0)
		s = (PQLSequence *) malloc(*n * sizeof(PQLSequence));
	else
		s = NULL;

	logDebug("number of sequences in server: %d", *n);

	for (i = 0; i < *n; i++)
	{
		s[i].obj.oid = strtoul(PQgetvalue(res, i, PQfnumber(res, "oid")), NULL, 10);
		s[i].obj.schemaname = strdup(PQgetvalue(res, i, PQfnumber(res, "nspname")));
		s[i].obj.objectname = strdup(PQgetvalue(res, i, PQfnumber(res, "relname")));
		if (PQgetisnull(res, i, PQfnumber(res, "description")))
			s[i].comment = NULL;
		else
			s[i].comment = strdup(PQgetvalue(res, i, PQfnumber(res, "description")));
		if (PQgetisnull(res, i, PQfnumber(res, "description")))
			s[i].comment = NULL;
		else
			s[i].comment = strdup(PQgetvalue(res, i, PQfnumber(res, "description")));

		s[i].owner = strdup(PQgetvalue(res, i, PQfnumber(res, "relowner")));
		if (PQgetisnull(res, i, PQfnumber(res, "relacl")))
			s[i].acl = NULL;
		else
			s[i].acl = strdup(PQgetvalue(res, i, PQfnumber(res, "relacl")));

		logDebug("sequence %s.%s", formatObjectIdentifier(s[i].obj.schemaname),
				 formatObjectIdentifier(s[i].obj.objectname));
	}

	PQclear(res);

	return s;
}
Ejemplo n.º 11
0
int toQPSqlConnectionSub::nativeVersion()
{
    QVariant v = Connection.driver()->handle();
    if (v.isValid() && v.typeName() == QString("PGconn*"))
    {
#ifdef HAVE_POSTGRESQL_LIBPQ_FE_H
        PGconn *handle = *static_cast<PGconn **>(v.data());
        if (handle)
            return PQserverVersion(handle);
#endif
    }
    return 0;
}
Ejemplo n.º 12
0
bool DatabasePostgre::Initialize(const char *infoString)
{
    if(!Database::Initialize(infoString))
        return false;

    tranThread = NULL;

    InitDelayThread();

    Tokens tokens = StrSplit(infoString, ";");

    Tokens::iterator iter;

    std::string host, port_or_socket_dir, user, password, database;

    iter = tokens.begin();

    if(iter != tokens.end())
        host = *iter++;
    if(iter != tokens.end())
        port_or_socket_dir = *iter++;
    if(iter != tokens.end())
        user = *iter++;
    if(iter != tokens.end())
        password = *iter++;
    if(iter != tokens.end())
        database = *iter++;

    if (host == ".")
        mPGconn = PQsetdbLogin(NULL, port_or_socket_dir == "." ? NULL : port_or_socket_dir.c_str(), NULL, NULL, database.c_str(), user.c_str(), password.c_str());
    else
        mPGconn = PQsetdbLogin(host.c_str(), port_or_socket_dir.c_str(), NULL, NULL, database.c_str(), user.c_str(), password.c_str());

    /* check to see that the backend connection was successfully made */
    if (PQstatus(mPGconn) != CONNECTION_OK)
    {
        sLog.outError( "Could not connect to Postgre database at %s: %s",
            host.c_str(), PQerrorMessage(mPGconn));
        PQfinish(mPGconn);
        mPGconn = NULL;
        return false;
    }
    else
    {
        sLog.outDetail( "Connected to Postgre database at %s",
            host.c_str());
        sLog.outString( "PostgreSQL server ver: %d",PQserverVersion(mPGconn));
        return true;
    }

}
Ejemplo n.º 13
0
QString  DBConnection::getDBMSVersion(void)
{
	QString version;

	if(!connection)
		throw Exception(ERR_OPR_NOT_ALOC_CONN, __PRETTY_FUNCTION__, __FILE__, __LINE__);

	version=QString("%1").arg(PQserverVersion(connection));

	return(QString("%1.%2.%3")
				 .arg(version.mid(0,2).toInt()/10)
				 .arg(version.mid(2,2).toInt()/10)
				 .arg(version.mid(4,1).toInt()));
}
Ejemplo n.º 14
0
//-----------------------------------------------------------
QString  ConexaoBD::obterVersaoSGBD(void)
{
    QString versao;

    if(!conexao)
        throw Excecao(ERR_CONEXBD_OPRCONEXNAOALOC, __PRETTY_FUNCTION__, __FILE__, __LINE__);

    versao=QString("%1").arg(PQserverVersion(conexao));

    return(QString("%1.%2.%3")
           .arg(versao.mid(0,2).toInt()/10)
           .arg(versao.mid(2,2).toInt()/10)
           .arg(versao.mid(4,1).toInt()));
}
/*************************************************************************
 *
 *	Function: sql_create_socket
 *
 *	Purpose: Establish connection to the db
 *
 *************************************************************************/
static int sql_init_socket(rlm_sql_handle_t *handle, rlm_sql_config_t *config) {
	char *dbstring;
	rlm_sql_postgres_conn_t *conn;

#ifdef HAVE_OPENSSL_CRYPTO_H
	static bool ssl_init = false;

	if (!ssl_init) {
		PQinitOpenSSL(0, 0);
		ssl_init = true;
	}
#endif

	MEM(conn = handle->conn = talloc_zero(handle, rlm_sql_postgres_conn_t));
	talloc_set_destructor(conn, _sql_socket_destructor);

	dbstring = strchr(config->sql_db, '=') ?
		talloc_strdup(conn, config->sql_db) :
		talloc_asprintf(conn, "dbname='%s'", config->sql_db);

	if (config->sql_server[0] != '\0') {
		dbstring = talloc_asprintf_append(dbstring, " host='%s'", config->sql_server);
	}

	if (config->sql_port[0] != '\0') {
		dbstring = talloc_asprintf_append(dbstring, " port=%s", config->sql_port);
	}

	if (config->sql_login[0] != '\0') {
		dbstring = talloc_asprintf_append(dbstring, " user='******'", config->sql_login);
	}

	if (config->sql_password[0] != '\0') {
		dbstring = talloc_asprintf_append(dbstring, " password='******'", config->sql_password);
	}

	conn->dbstring = dbstring;
	conn->db = PQconnectdb(dbstring);
	DEBUG2("rlm_sql_postgresql: Connecting using parameters: %s", dbstring);
	if (!conn->db || (PQstatus(conn->db) != CONNECTION_OK)) {
		ERROR("rlm_sql_postgresql: Connection failed: %s", PQerrorMessage(conn->db));
		return -1;
	}
	DEBUG2("Connected to database '%s' on '%s' server version %i, protocol version %i, backend PID %i ",
	       PQdb(conn->db), PQhost(conn->db), PQserverVersion(conn->db), PQprotocolVersion(conn->db),
	       PQbackendPID(conn->db));

	return 0;
}
Ejemplo n.º 16
0
PQLTrigger *
getTriggers(PGconn *c, int *n)
{
	PQLTrigger	*t;
	PGresult	*res;
	int			i;

	logNoise("trigger: server version: %d", PQserverVersion(c));

	res = PQexec(c,
				 "SELECT t.oid, t.tgname AS trgname, n.nspname AS nspname, c.relname AS relname, pg_get_triggerdef(t.oid, false) AS trgdef, obj_description(t.oid, 'pg_rewrite') AS description FROM pg_trigger t INNER JOIN pg_class c ON (t.tgrelid = c.oid) INNER JOIN pg_namespace n ON (c.relnamespace = n.oid) WHERE NOT tgisinternal");

	if (PQresultStatus(res) != PGRES_TUPLES_OK)
	{
		logError("query failed: %s", PQresultErrorMessage(res));
		PQclear(res);
		PQfinish(c);
		/* XXX leak another connection? */
		exit(EXIT_FAILURE);
	}

	*n = PQntuples(res);
	if (*n > 0)
		t = (PQLTrigger *) malloc(*n * sizeof(PQLTrigger));
	else
		t = NULL;

	logDebug("number of triggers in server: %d", *n);

	for (i = 0; i < *n; i++)
	{
		t[i].oid = strtoul(PQgetvalue(res, i, PQfnumber(res, "oid")), NULL, 10);
		t[i].trgname = strdup(PQgetvalue(res, i, PQfnumber(res, "tgname")));
		t[i].table.schemaname = strdup(PQgetvalue(res, i, PQfnumber(res, "nspname")));
		t[i].table.objectname = strdup(PQgetvalue(res, i, PQfnumber(res, "relname")));
		t[i].trgdef = strdup(PQgetvalue(res, i, PQfnumber(res, "trgdef")));
		if (PQgetisnull(res, i, PQfnumber(res, "description")))
			t[i].comment = NULL;
		else
			t[i].comment = strdup(PQgetvalue(res, i, PQfnumber(res, "description")));

		logDebug("trigger \"%s\" on \"%s\".\"%s\"", t[i].trgname, t[i].table.schemaname, t[i].table.objectname);
	}

	PQclear(res);

	return t;
}
Ejemplo n.º 17
0
void
pqt_getfmtinfo(const PGconn *conn, PGtypeFormatInfo *info)
{
	const char *value;

	memset(info, 0, sizeof(PGtypeFormatInfo));

	if ((value = PQparameterStatus(conn, "DateStyle")))
		pqt_strcpy(info->datestyle, sizeof(info->datestyle), value);

	if ((value = PQparameterStatus(conn, "integer_datetimes")))
		info->integer_datetimes = strcmp(value, "on")==0 ? TRUE : FALSE;

	info->sversion = PQserverVersion(conn);
	info->pversion = PQprotocolVersion(conn);
}
Ejemplo n.º 18
0
static awk_value_t *
do_pg_serverversion(int nargs, awk_value_t *result)
{
  PGconn *conn;
  int version;

  if (do_lint && (nargs > 1))
    lintwarn(ext_id, _("pg_serverversion: called with too many arguments"));

  if (!(conn = find_handle(conns, 0))) {
    set_ERRNO(_("pg_serverversion called with unknown connection handle"));
    RET_NUM(0);
  }
  if (!(version = PQserverVersion(conn)))
    set_ERRNO(PQerrorMessage(conn));
  RET_NUM(version);
}
Ejemplo n.º 19
0
Archivo: command.c Proyecto: 50wu/gpdb
/*
 * SyncVariables
 *
 * Make psql's internal variables agree with connection state upon
 * establishing a new connection.
 */
void
SyncVariables(void)
{
	/* get stuff from connection */
	pset.encoding = PQclientEncoding(pset.db);
	pset.popt.topt.encoding = pset.encoding;
	pset.sversion = PQserverVersion(pset.db);

	SetVariable(pset.vars, "DBNAME", PQdb(pset.db));
	SetVariable(pset.vars, "USER", PQuser(pset.db));
	SetVariable(pset.vars, "HOST", PQhost(pset.db));
	SetVariable(pset.vars, "PORT", PQport(pset.db));
	SetVariable(pset.vars, "ENCODING", pg_encoding_to_char(pset.encoding));

	/* send stuff to it, too */
	PQsetErrorVerbosity(pset.db, pset.verbosity);
}
Ejemplo n.º 20
0
PQLRule *
getRules(PGconn *c, int *n)
{
	PQLRule		*r;
	PGresult	*res;
	int			i;

	logNoise("rule: server version: %d", PQserverVersion(c));

	res = PQexec(c,
				"SELECT n.nspname AS schemaname, c.relname AS tablename, r.rulename, pg_get_ruledef(r.oid) AS definition, obj_description(r.oid, 'pg_rewrite') AS description FROM pg_rewrite r INNER JOIN pg_class c ON (c.oid = r.ev_class) INNER JOIN pg_namespace n ON (n.oid = c.relnamespace) WHERE r.rulename <> '_RETURN'::name AND n.nspname !~ '^pg_' AND n.nspname <> 'information_schema' ORDER BY n.nspname, c.relname, r.rulename");

	if (PQresultStatus(res) != PGRES_TUPLES_OK)
	{
		logError("query failed: %s", PQresultErrorMessage(res));
		PQclear(res);
		PQfinish(c);
		/* XXX leak another connection? */
		exit(EXIT_FAILURE);
	}

	*n = PQntuples(res);
	if (*n > 0)
		r = (PQLRule *) malloc(*n * sizeof(PQLRule));
	else
		r = NULL;

	logDebug("number of rules in server: %d", *n);

	for (i = 0; i < *n; i++)
	{
		r[i].table.schemaname = strdup(PQgetvalue(res, i, PQfnumber(res, "schemaname")));
		r[i].table.objectname = strdup(PQgetvalue(res, i, PQfnumber(res, "tablename")));
		r[i].rulename = strdup(PQgetvalue(res, i, PQfnumber(res, "rulename")));
		r[i].ruledef = strdup(PQgetvalue(res, i, PQfnumber(res, "definition")));
		if (PQgetisnull(res, i, PQfnumber(res, "description")))
			r[i].comment = NULL;
		else
			r[i].comment = strdup(PQgetvalue(res, i, PQfnumber(res, "description")));
		logDebug("rule %s on %s.%s", formatObjectIdentifier(r[i].rulename), formatObjectIdentifier(r[i].table.schemaname), formatObjectIdentifier(r[i].table.objectname));
	}

	PQclear(res);

	return r;
}
Ejemplo n.º 21
0
void
getLanguageSecurityLabels(PGconn *c, PQLLanguage *l)
{
	char		query[200];
	PGresult	*res;
	int			i;

	if (PQserverVersion(c) < 90100)
	{
		logWarning("ignoring security labels because server does not support it");
		return;
	}

	snprintf(query, 200, "SELECT provider, label FROM pg_seclabel s INNER JOIN pg_class c ON (s.classoid = c.oid) WHERE c.relname = 'pg_language' AND s.objoid = %u ORDER BY provider", l->oid);

	res = PQexec(c, query);

	if (PQresultStatus(res) != PGRES_TUPLES_OK)
	{
		logError("query failed: %s", PQresultErrorMessage(res));
		PQclear(res);
		PQfinish(c);
		/* XXX leak another connection? */
		exit(EXIT_FAILURE);
	}

	l->nseclabels = PQntuples(res);
	if (l->nseclabels > 0)
		l->seclabels = (PQLSecLabel *) malloc(l->nseclabels * sizeof(PQLSecLabel));
	else
		l->seclabels = NULL;

	logDebug("number of security labels in language \"%s\": %d", l->languagename, l->nseclabels);

	for (i = 0; i < l->nseclabels; i++)
	{
		l->seclabels[i].provider = strdup(PQgetvalue(res, i, PQfnumber(res, "provider")));
		l->seclabels[i].label = strdup(PQgetvalue(res, i, PQfnumber(res, "label")));
	}

	PQclear(res);
}
Ejemplo n.º 22
0
int storage_init(const char *file)
{
    if(db != NULL)
    {
        LOGW(tag, "Database already open, closing it");
        sqlite3_close(db);
    }

    // Open database
#if SCH_STORAGE_MODE == 1
    if(sqlite3_open(file, &db) != SQLITE_OK)
    {
        LOGE(tag, "Can't open database: %s", sqlite3_errmsg(db));
        return -1;
    }
    else
    {
        LOGD(tag, "Opened database successfully");
        return 0;
    }
#elif SCH_STORAGE_MODE == 2
    sprintf(postgres_conf_s, "user=%s dbname=fs_db", SCH_STORAGE_PGUSER);
    conn = PQconnectdb(postgres_conf_s);

    if (PQstatus(conn) == CONNECTION_BAD) {
        LOGE(tag, "Connection to database failed: %s\n", PQerrorMessage(conn));
        PQfinish(conn);
        return -1;
    }

    int ver = PQserverVersion(conn);

    printf("Server version: %d\n", ver);
#endif
    return 0;
}
Ejemplo n.º 23
0
/*
 * This vacuums LOs of one database. It returns 0 on success, -1 on failure.
 */
static int
vacuumlo(const char *database, const struct _param *param)
{
	PGconn	   *conn;
	PGresult   *res,
			   *res2;
	char		buf[BUFSIZE];
	long		matched;
	long		deleted;
	int			i;
	bool		new_pass;
	bool		success = true;
	static bool have_password = false;
	static char password[100];

	/* Note: password can be carried over from a previous call */
	if (param->pg_prompt == TRI_YES && !have_password)
	{
		simple_prompt("Password: "******"host";
		values[0] = param->pg_host;
		keywords[1] = "port";
		values[1] = param->pg_port;
		keywords[2] = "user";
		values[2] = param->pg_user;
		keywords[3] = "password";
		values[3] = have_password ? password : NULL;
		keywords[4] = "dbname";
		values[4] = database;
		keywords[5] = "fallback_application_name";
		values[5] = param->progname;
		keywords[6] = NULL;
		values[6] = NULL;

		new_pass = false;
		conn = PQconnectdbParams(keywords, values, true);
		if (!conn)
		{
			fprintf(stderr, "Connection to database \"%s\" failed\n",
					database);
			return -1;
		}

		if (PQstatus(conn) == CONNECTION_BAD &&
			PQconnectionNeedsPassword(conn) &&
			!have_password &&
			param->pg_prompt != TRI_NO)
		{
			PQfinish(conn);
			simple_prompt("Password: "******"Connection to database \"%s\" failed:\n%s",
				database, PQerrorMessage(conn));
		PQfinish(conn);
		return -1;
	}

	if (param->verbose)
	{
		fprintf(stdout, "Connected to database \"%s\"\n", database);
		if (param->dry_run)
			fprintf(stdout, "Test run: no large objects will be removed!\n");
	}

	res = PQexec(conn, ALWAYS_SECURE_SEARCH_PATH_SQL);
	if (PQresultStatus(res) != PGRES_TUPLES_OK)
	{
		fprintf(stderr, "Failed to set search_path:\n");
		fprintf(stderr, "%s", PQerrorMessage(conn));
		PQclear(res);
		PQfinish(conn);
		return -1;
	}
	PQclear(res);

	/*
	 * First we create and populate the LO temp table
	 */
	buf[0] = '\0';
	strcat(buf, "CREATE TEMP TABLE vacuum_l AS ");
	if (PQserverVersion(conn) >= 90000)
		strcat(buf, "SELECT oid AS lo FROM pg_largeobject_metadata");
	else
		strcat(buf, "SELECT DISTINCT loid AS lo FROM pg_largeobject");
	res = PQexec(conn, buf);
	if (PQresultStatus(res) != PGRES_COMMAND_OK)
	{
		fprintf(stderr, "Failed to create temp table:\n");
		fprintf(stderr, "%s", PQerrorMessage(conn));
		PQclear(res);
		PQfinish(conn);
		return -1;
	}
	PQclear(res);

	/*
	 * Analyze the temp table so that planner will generate decent plans for
	 * the DELETEs below.
	 */
	buf[0] = '\0';
	strcat(buf, "ANALYZE vacuum_l");
	res = PQexec(conn, buf);
	if (PQresultStatus(res) != PGRES_COMMAND_OK)
	{
		fprintf(stderr, "Failed to vacuum temp table:\n");
		fprintf(stderr, "%s", PQerrorMessage(conn));
		PQclear(res);
		PQfinish(conn);
		return -1;
	}
	PQclear(res);

	/*
	 * Now find any candidate tables that have columns of type oid.
	 *
	 * NOTE: we ignore system tables and temp tables by the expedient of
	 * rejecting tables in schemas named 'pg_*'.  In particular, the temp
	 * table formed above is ignored, and pg_largeobject will be too. If
	 * either of these were scanned, obviously we'd end up with nothing to
	 * delete...
	 *
	 * NOTE: the system oid column is ignored, as it has attnum < 1. This
	 * shouldn't matter for correctness, but it saves time.
	 */
	buf[0] = '\0';
	strcat(buf, "SELECT s.nspname, c.relname, a.attname ");
	strcat(buf, "FROM pg_class c, pg_attribute a, pg_namespace s, pg_type t ");
	strcat(buf, "WHERE a.attnum > 0 AND NOT a.attisdropped ");
	strcat(buf, "      AND a.attrelid = c.oid ");
	strcat(buf, "      AND a.atttypid = t.oid ");
	strcat(buf, "      AND c.relnamespace = s.oid ");
	strcat(buf, "      AND t.typname in ('oid', 'lo') ");
	strcat(buf, "      AND c.relkind in (" CppAsString2(RELKIND_RELATION) ", " CppAsString2(RELKIND_MATVIEW) ")");
	strcat(buf, "      AND s.nspname !~ '^pg_'");
	res = PQexec(conn, buf);
	if (PQresultStatus(res) != PGRES_TUPLES_OK)
	{
		fprintf(stderr, "Failed to find OID columns:\n");
		fprintf(stderr, "%s", PQerrorMessage(conn));
		PQclear(res);
		PQfinish(conn);
		return -1;
	}

	for (i = 0; i < PQntuples(res); i++)
	{
		char	   *schema,
				   *table,
				   *field;

		schema = PQgetvalue(res, i, 0);
		table = PQgetvalue(res, i, 1);
		field = PQgetvalue(res, i, 2);

		if (param->verbose)
			fprintf(stdout, "Checking %s in %s.%s\n", field, schema, table);

		schema = PQescapeIdentifier(conn, schema, strlen(schema));
		table = PQescapeIdentifier(conn, table, strlen(table));
		field = PQescapeIdentifier(conn, field, strlen(field));

		if (!schema || !table || !field)
		{
			fprintf(stderr, "%s", PQerrorMessage(conn));
			PQclear(res);
			PQfinish(conn);
			if (schema != NULL)
				PQfreemem(schema);
			if (schema != NULL)
				PQfreemem(table);
			if (schema != NULL)
				PQfreemem(field);
			return -1;
		}

		snprintf(buf, BUFSIZE,
				 "DELETE FROM vacuum_l "
				 "WHERE lo IN (SELECT %s FROM %s.%s)",
				 field, schema, table);
		res2 = PQexec(conn, buf);
		if (PQresultStatus(res2) != PGRES_COMMAND_OK)
		{
			fprintf(stderr, "Failed to check %s in table %s.%s:\n",
					field, schema, table);
			fprintf(stderr, "%s", PQerrorMessage(conn));
			PQclear(res2);
			PQclear(res);
			PQfinish(conn);
			PQfreemem(schema);
			PQfreemem(table);
			PQfreemem(field);
			return -1;
		}
		PQclear(res2);

		PQfreemem(schema);
		PQfreemem(table);
		PQfreemem(field);
	}
	PQclear(res);

	/*
	 * Now, those entries remaining in vacuum_l are orphans.  Delete 'em.
	 *
	 * We don't want to run each delete as an individual transaction, because
	 * the commit overhead would be high.  However, since 9.0 the backend will
	 * acquire a lock per deleted LO, so deleting too many LOs per transaction
	 * risks running out of room in the shared-memory lock table. Accordingly,
	 * we delete up to transaction_limit LOs per transaction.
	 */
	res = PQexec(conn, "begin");
	if (PQresultStatus(res) != PGRES_COMMAND_OK)
	{
		fprintf(stderr, "Failed to start transaction:\n");
		fprintf(stderr, "%s", PQerrorMessage(conn));
		PQclear(res);
		PQfinish(conn);
		return -1;
	}
	PQclear(res);

	buf[0] = '\0';
	strcat(buf,
		   "DECLARE myportal CURSOR WITH HOLD FOR SELECT lo FROM vacuum_l");
	res = PQexec(conn, buf);
	if (PQresultStatus(res) != PGRES_COMMAND_OK)
	{
		fprintf(stderr, "DECLARE CURSOR failed: %s", PQerrorMessage(conn));
		PQclear(res);
		PQfinish(conn);
		return -1;
	}
	PQclear(res);

	snprintf(buf, BUFSIZE, "FETCH FORWARD %ld IN myportal",
			 param->transaction_limit > 0 ? param->transaction_limit : 1000L);

	deleted = 0;

	while (1)
	{
		res = PQexec(conn, buf);
		if (PQresultStatus(res) != PGRES_TUPLES_OK)
		{
			fprintf(stderr, "FETCH FORWARD failed: %s", PQerrorMessage(conn));
			PQclear(res);
			PQfinish(conn);
			return -1;
		}

		matched = PQntuples(res);
		if (matched <= 0)
		{
			/* at end of resultset */
			PQclear(res);
			break;
		}

		for (i = 0; i < matched; i++)
		{
			Oid			lo = atooid(PQgetvalue(res, i, 0));

			if (param->verbose)
			{
				fprintf(stdout, "\rRemoving lo %6u   ", lo);
				fflush(stdout);
			}

			if (param->dry_run == 0)
			{
				if (lo_unlink(conn, lo) < 0)
				{
					fprintf(stderr, "\nFailed to remove lo %u: ", lo);
					fprintf(stderr, "%s", PQerrorMessage(conn));
					if (PQtransactionStatus(conn) == PQTRANS_INERROR)
					{
						success = false;
						PQclear(res);
						break;
					}
				}
				else
					deleted++;
			}
			else
				deleted++;

			if (param->transaction_limit > 0 &&
				(deleted % param->transaction_limit) == 0)
			{
				res2 = PQexec(conn, "commit");
				if (PQresultStatus(res2) != PGRES_COMMAND_OK)
				{
					fprintf(stderr, "Failed to commit transaction:\n");
					fprintf(stderr, "%s", PQerrorMessage(conn));
					PQclear(res2);
					PQclear(res);
					PQfinish(conn);
					return -1;
				}
				PQclear(res2);
				res2 = PQexec(conn, "begin");
				if (PQresultStatus(res2) != PGRES_COMMAND_OK)
				{
					fprintf(stderr, "Failed to start transaction:\n");
					fprintf(stderr, "%s", PQerrorMessage(conn));
					PQclear(res2);
					PQclear(res);
					PQfinish(conn);
					return -1;
				}
				PQclear(res2);
			}
		}

		PQclear(res);
	}

	/*
	 * That's all folks!
	 */
	res = PQexec(conn, "commit");
	if (PQresultStatus(res) != PGRES_COMMAND_OK)
	{
		fprintf(stderr, "Failed to commit transaction:\n");
		fprintf(stderr, "%s", PQerrorMessage(conn));
		PQclear(res);
		PQfinish(conn);
		return -1;
	}
	PQclear(res);

	PQfinish(conn);

	if (param->verbose)
	{
		if (param->dry_run)
			fprintf(stdout, "\rWould remove %ld large objects from database \"%s\".\n",
					deleted, database);
		else if (success)
			fprintf(stdout,
					"\rSuccessfully removed %ld large objects from database \"%s\".\n",
					deleted, database);
		else
			fprintf(stdout, "\rRemoval from database \"%s\" failed at object %ld of %ld.\n",
					database, deleted, matched);
	}

	return ((param->dry_run || success) ? 0 : -1);
}
Ejemplo n.º 24
0
static void
vacuum_one_database(const char *dbname, bool full, bool verbose, bool and_analyze,
					bool analyze_only, bool freeze, const char *table,
					const char *host, const char *port,
					const char *username, enum trivalue prompt_password,
					const char *progname, bool echo)
{
	PQExpBufferData sql;

	PGconn	   *conn;

	initPQExpBuffer(&sql);

	conn = connectDatabase(dbname, host, port, username, prompt_password,
						   progname, false);

	if (analyze_only)
	{
		appendPQExpBuffer(&sql, "ANALYZE");
		if (verbose)
			appendPQExpBuffer(&sql, " VERBOSE");
	}
	else
	{
		appendPQExpBuffer(&sql, "VACUUM");
		if (PQserverVersion(conn) >= 90000)
		{
			const char *paren = " (";
			const char *comma = ", ";
			const char *sep = paren;

			if (full)
			{
				appendPQExpBuffer(&sql, "%sFULL", sep);
				sep = comma;
			}
			if (freeze)
			{
				appendPQExpBuffer(&sql, "%sFREEZE", sep);
				sep = comma;
			}
			if (verbose)
			{
				appendPQExpBuffer(&sql, "%sVERBOSE", sep);
				sep = comma;
			}
			if (and_analyze)
			{
				appendPQExpBuffer(&sql, "%sANALYZE", sep);
				sep = comma;
			}
			if (sep != paren)
				appendPQExpBuffer(&sql, ")");
		}
		else
		{
			if (full)
				appendPQExpBuffer(&sql, " FULL");
			if (freeze)
				appendPQExpBuffer(&sql, " FREEZE");
			if (verbose)
				appendPQExpBuffer(&sql, " VERBOSE");
			if (and_analyze)
				appendPQExpBuffer(&sql, " ANALYZE");
		}
	}
	if (table)
		appendPQExpBuffer(&sql, " %s", table);
	appendPQExpBuffer(&sql, ";\n");

	if (!executeMaintenanceCommand(conn, sql.data, echo))
	{
		if (table)
			fprintf(stderr, _("%s: vacuuming of table \"%s\" in database \"%s\" failed: %s"),
					progname, table, dbname, PQerrorMessage(conn));
		else
			fprintf(stderr, _("%s: vacuuming of database \"%s\" failed: %s"),
					progname, dbname, PQerrorMessage(conn));
		PQfinish(conn);
		exit(1);
	}
	PQfinish(conn);
	termPQExpBuffer(&sql);
}
Ejemplo n.º 25
0
/*
 * Start the log streaming
 */
static void
StreamLog(void)
{
	PGresult   *res;
	uint32		timeline;
	XLogRecPtr	startpos;
	int			minServerMajor,
				maxServerMajor;
	int			serverMajor;

	/*
	 * Connect in replication mode to the server
	 */
	conn = GetConnection();
	if (!conn)
		/* Error message already written in GetConnection() */
		return;

	/*
	 * Check server version. IDENTIFY_SYSTEM didn't return the current xlog
	 * position before 9.1, so we can't work with servers older than 9.1. And
	 * we don't support servers newer than the client.
	 */
	minServerMajor = 901;
	maxServerMajor = PG_VERSION_NUM / 100;
	serverMajor = PQserverVersion(conn) / 100;
	if (serverMajor < minServerMajor || serverMajor > maxServerMajor)
	{
		fprintf(stderr, _("%s: unsupported server version %s\n"),
				progname, PQparameterStatus(conn, "server_version"));
		disconnect_and_exit(1);
	}

	/*
	 * Run IDENTIFY_SYSTEM so we can get the timeline and current xlog
	 * position.
	 */
	res = PQexec(conn, "IDENTIFY_SYSTEM");
	if (PQresultStatus(res) != PGRES_TUPLES_OK)
	{
		fprintf(stderr, _("%s: could not send replication command \"%s\": %s"),
				progname, "IDENTIFY_SYSTEM", PQerrorMessage(conn));

		disconnect_and_exit(1);
	}
	if (PQntuples(res) != 1 || PQnfields(res) != 3)
	{
		fprintf(stderr,
				_("%s: could not identify system: got %d rows and %d fields, expected %d rows and %d fields\n"),
				progname, PQntuples(res), PQnfields(res), 1, 3);

		disconnect_and_exit(1);
	}
	timeline = atoi(PQgetvalue(res, 0, 1));
	if (sscanf(PQgetvalue(res, 0, 2), "%X/%X", &startpos.xlogid, &startpos.xrecoff) != 2)
	{
		fprintf(stderr,
				_("%s: could not parse transaction log location \"%s\"\n"),
				progname, PQgetvalue(res, 0, 2));
		disconnect_and_exit(1);
	}
	PQclear(res);

	/*
	 * Figure out where to start streaming.
	 */
	startpos = FindStreamingStart(startpos, timeline);

	/*
	 * Always start streaming at the beginning of a segment
	 */
	startpos.xrecoff -= startpos.xrecoff % XLOG_SEG_SIZE;

	/*
	 * Start the replication
	 */
	if (verbose)
		fprintf(stderr,
				_("%s: starting log streaming at %X/%X (timeline %u)\n"),
				progname, startpos.xlogid, startpos.xrecoff, timeline);

	ReceiveXlogStream(conn, startpos, timeline, NULL, basedir,
					  stop_streaming, standby_message_timeout, false);

	PQfinish(conn);
}
Ejemplo n.º 26
0
int pg_con_connect(db_con_t *con)
{
	struct pg_con *pcon;
	struct pg_uri *puri;
	char *port_str;
	int ret, i = 0;
	const char *keywords[10], *values[10];
	char to[16];

	pcon = DB_GET_PAYLOAD(con);
	puri = DB_GET_PAYLOAD(con->uri);

	/* Do not reconnect already connected connections */
	if(pcon->flags & PG_CONNECTED)
		return 0;

	DBG("postgres: Connecting to %.*s:%.*s\n", con->uri->scheme.len,
			ZSW(con->uri->scheme.s), con->uri->body.len, ZSW(con->uri->body.s));

	if(puri->port > 0) {
		port_str = int2str(puri->port, 0);
		keywords[i] = "port";
		values[i++] = port_str;
	} else {
		port_str = NULL;
	}

	if(pcon->con) {
		PQfinish(pcon->con);
		pcon->con = NULL;
	}

	keywords[i] = "host";
	values[i++] = puri->host;
	keywords[i] = "dbname";
	values[i++] = puri->database;
	keywords[i] = "user";
	values[i++] = puri->username;
	keywords[i] = "password";
	values[i++] = puri->password;
	if(pg_timeout > 0) {
		snprintf(to, sizeof(to) - 1, "%d", pg_timeout + 3);
		keywords[i] = "connect_timeout";
		values[i++] = to;
	}

	keywords[i] = values[i] = NULL;

	pcon->con = PQconnectdbParams(keywords, values, 1);

	if(pcon->con == NULL) {
		ERR("postgres: PQconnectdbParams ran out of memory\n");
		goto error;
	}

	if(PQstatus(pcon->con) != CONNECTION_OK) {
		ERR("postgres: %s\n", PQerrorMessage(pcon->con));
		goto error;
	}

	/* Override default notice processor */
	PQsetNoticeProcessor(pcon->con, notice_processor, 0);

#ifdef HAVE_PGSERVERVERSION
	DBG("postgres: Connected. Protocol version=%d, Server version=%d\n",
			PQprotocolVersion(pcon->con), PQserverVersion(pcon->con));
#else
	DBG("postgres: Connected. Protocol version=%d, Server version=%d\n",
			PQprotocolVersion(pcon->con), 0);
#endif

#if defined(SO_KEEPALIVE) && defined(TCP_KEEPIDLE)
	if(pg_keepalive) {
		i = 1;
		if(setsockopt(
				   PQsocket(pcon->con), SOL_SOCKET, SO_KEEPALIVE, &i, sizeof(i))
				< 0) {
			LM_WARN("failed to set socket option keepalive\n");
		}
		if(setsockopt(PQsocket(pcon->con), IPPROTO_TCP, TCP_KEEPIDLE,
				   &pg_keepalive, sizeof(pg_keepalive))
				< 0) {
			LM_WARN("failed to set socket option keepidle\n");
		}
	}
#endif

	ret = timestamp_format(pcon->con);
	if(ret == 1 || ret == -1) {
		/* Assume INT8 representation if detection fails */
		pcon->flags |= PG_INT8_TIMESTAMP;
	} else {
		pcon->flags &= ~PG_INT8_TIMESTAMP;
	}

	if(get_oids(con) < 0)
		goto error;

	pcon->flags |= PG_CONNECTED;
	return 0;

error:
	if(pcon->con)
		PQfinish(pcon->con);
	pcon->con = NULL;
	return -1;
}
Ejemplo n.º 27
0
void PostgreSQLConnection::connect()
{
	bool reconnecting = false;
	if (_pgConn != nullptr) //reconnection attempt
	{
		if (!_ConnectionLost())
			return;
		else
			reconnecting = true;
	}

	//remove any state from previous session
	this->clear();

	Poco::Logger& logger = _dbEngine->getLogger();
	for(;;)
	{
		if (reconnecting)
			PQreset(_pgConn);
		else
		{
			if (_host == ".")
				_pgConn = PQsetdbLogin(nullptr, _port == "." ? nullptr : _port.c_str(), nullptr, nullptr, _database.c_str(), _user.c_str(), _password.c_str());
			else
				_pgConn = PQsetdbLogin(_host.c_str(), _port.c_str(), nullptr, nullptr, _database.c_str(), _user.c_str(), _password.c_str());
		}
		
		//check to see that the backend connection was successfully made
		if (_ConnectionLost())
		{
			const char* actionToDo = "connect";
			if (reconnecting)
				actionToDo = "reconnect";

			static const long sleepTime = 1000;
			logger.warning(Poco::format("Could not %s to Postgre database at %s: %s, retrying in %d seconds",
				string(actionToDo),_host,lastErrorDescr(),static_cast<int>(sleepTime/1000)));
			Poco::Thread::sleep(sleepTime);

			continue;
		}
		break;
	}

	string actionDone = (reconnecting)?string("Reconnected"):string("Connected");
	poco_information(logger,Poco::format("%s to Postgre database %s:%s/%s server ver: %d",actionDone,_host,_port,_database,PQserverVersion(_pgConn)));
}
Ejemplo n.º 28
0
/*
 * vacuum_one_database
 *
 * Process tables in the given database.  If the 'tables' list is empty,
 * process all tables in the database.
 *
 * Note that this function is only concerned with running exactly one stage
 * when in analyze-in-stages mode; caller must iterate on us if necessary.
 *
 * If concurrentCons is > 1, multiple connections are used to vacuum tables
 * in parallel.  In this case and if the table list is empty, we first obtain
 * a list of tables from the database.
 */
static void
vacuum_one_database(const char *dbname, vacuumingOptions *vacopts,
					int stage,
					SimpleStringList *tables,
					const char *host, const char *port,
					const char *username, enum trivalue prompt_password,
					int concurrentCons,
					const char *progname, bool echo, bool quiet)
{
	PQExpBufferData sql;
	PGconn	   *conn;
	SimpleStringListCell *cell;
	ParallelSlot *slots = NULL;
	SimpleStringList dbtables = {NULL, NULL};
	int			i;
	bool		result = 0;
	bool		parallel = concurrentCons > 1;
	const char *stage_commands[] = {
		"SET default_statistics_target=1; SET vacuum_cost_delay=0;",
		"SET default_statistics_target=10; RESET vacuum_cost_delay;",
		"RESET default_statistics_target;"
	};
	const char *stage_messages[] = {
		gettext_noop("Generating minimal optimizer statistics (1 target)"),
		gettext_noop("Generating medium optimizer statistics (10 targets)"),
		gettext_noop("Generating default (full) optimizer statistics")
	};

	Assert(stage == ANALYZE_NO_STAGE ||
		   (stage >= 0 && stage < ANALYZE_NUM_STAGES));

	if (!quiet)
	{
		if (stage != ANALYZE_NO_STAGE)
			printf(_("%s: processing database \"%s\": %s\n"), progname, dbname,
				   stage_messages[stage]);
		else
			printf(_("%s: vacuuming database \"%s\"\n"), progname, dbname);
		fflush(stdout);
	}

	conn = connectDatabase(dbname, host, port, username, prompt_password,
						   progname, false);

	initPQExpBuffer(&sql);

	/*
	 * If a table list is not provided and we're using multiple connections,
	 * prepare the list of tables by querying the catalogs.
	 */
	if (parallel && (!tables || !tables->head))
	{
		PQExpBufferData buf;
		PGresult   *res;
		int			ntups;
		int			i;

		initPQExpBuffer(&buf);

		res = executeQuery(conn,
			"SELECT c.relname, ns.nspname FROM pg_class c, pg_namespace ns\n"
			 " WHERE relkind IN (\'r\', \'m\') AND c.relnamespace = ns.oid\n"
						   " ORDER BY c.relpages DESC;",
						   progname, echo);

		ntups = PQntuples(res);
		for (i = 0; i < ntups; i++)
		{
			appendPQExpBuffer(&buf, "%s",
							  fmtQualifiedId(PQserverVersion(conn),
											 PQgetvalue(res, i, 1),
											 PQgetvalue(res, i, 0)));

			simple_string_list_append(&dbtables, buf.data);
			resetPQExpBuffer(&buf);
		}

		termPQExpBuffer(&buf);
		tables = &dbtables;

		/*
		 * If there are more connections than vacuumable relations, we don't
		 * need to use them all.
		 */
		if (concurrentCons > ntups)
			concurrentCons = ntups;
		if (concurrentCons <= 1)
			parallel = false;
	}

	/*
	 * Setup the database connections. We reuse the connection we already have
	 * for the first slot.  If not in parallel mode, the first slot in the
	 * array contains the connection.
	 */
	slots = (ParallelSlot *) pg_malloc(sizeof(ParallelSlot) * concurrentCons);
	init_slot(slots, conn);
	if (parallel)
	{
		for (i = 1; i < concurrentCons; i++)
		{
			conn = connectDatabase(dbname, host, port, username, prompt_password,
								   progname, false);
			init_slot(slots + i, conn);
		}
	}

	/*
	 * Prepare all the connections to run the appropriate analyze stage, if
	 * caller requested that mode.
	 */
	if (stage != ANALYZE_NO_STAGE)
	{
		int			j;

		/* We already emitted the message above */

		for (j = 0; j < concurrentCons; j++)
			executeCommand((slots + j)->connection,
						   stage_commands[stage], progname, echo);
	}

	cell = tables ? tables->head : NULL;
	do
	{
		ParallelSlot *free_slot;
		const char *tabname = cell ? cell->val : NULL;

		prepare_vacuum_command(&sql, conn, vacopts, tabname);

		if (CancelRequested)
		{
			result = -1;
			goto finish;
		}

		/*
		 * Get the connection slot to use.  If in parallel mode, here we wait
		 * for one connection to become available if none already is.  In
		 * non-parallel mode we simply use the only slot we have, which we
		 * know to be free.
		 */
		if (parallel)
		{
			/*
			 * Get a free slot, waiting until one becomes free if none
			 * currently is.
			 */
			free_slot = GetIdleSlot(slots, concurrentCons, dbname, progname);
			if (!free_slot)
			{
				result = -1;
				goto finish;
			}

			free_slot->isFree = false;
		}
		else
			free_slot = slots;

		run_vacuum_command(free_slot->connection, sql.data,
						   echo, dbname, tabname, progname, parallel);

		if (cell)
			cell = cell->next;
	} while (cell != NULL);

	if (parallel)
	{
		int			j;

		for (j = 0; j < concurrentCons; j++)
		{
			/* wait for all connection to return the results */
			if (!GetQueryResult((slots + j)->connection, dbname, progname))
				goto finish;

			(slots + j)->isFree = true;
		}
	}

finish:
	for (i = 0; i < concurrentCons; i++)
		DisconnectDatabase(slots + i);
	pfree(slots);

	termPQExpBuffer(&sql);

	if (result == -1)
		exit(1);
}
Ejemplo n.º 29
0
/*
 * Construct a vacuum/analyze command to run based on the given options, in the
 * given string buffer, which may contain previous garbage.
 *
 * An optional table name can be passed; this must be already be properly
 * quoted.  The command is semicolon-terminated.
 */
static void
prepare_vacuum_command(PQExpBuffer sql, PGconn *conn, vacuumingOptions *vacopts,
					   const char *table)
{
	resetPQExpBuffer(sql);

	if (vacopts->analyze_only)
	{
		appendPQExpBufferStr(sql, "ANALYZE");
		if (vacopts->verbose)
			appendPQExpBufferStr(sql, " VERBOSE");
	}
	else
	{
		appendPQExpBufferStr(sql, "VACUUM");
		if (PQserverVersion(conn) >= 90000)
		{
			const char *paren = " (";
			const char *comma = ", ";
			const char *sep = paren;

			if (vacopts->full)
			{
				appendPQExpBuffer(sql, "%sFULL", sep);
				sep = comma;
			}
			if (vacopts->freeze)
			{
				appendPQExpBuffer(sql, "%sFREEZE", sep);
				sep = comma;
			}
			if (vacopts->verbose)
			{
				appendPQExpBuffer(sql, "%sVERBOSE", sep);
				sep = comma;
			}
			if (vacopts->and_analyze)
			{
				appendPQExpBuffer(sql, "%sANALYZE", sep);
				sep = comma;
			}
			if (sep != paren)
				appendPQExpBufferStr(sql, ")");
		}
		else
		{
			if (vacopts->full)
				appendPQExpBufferStr(sql, " FULL");
			if (vacopts->freeze)
				appendPQExpBufferStr(sql, " FREEZE");
			if (vacopts->verbose)
				appendPQExpBufferStr(sql, " VERBOSE");
			if (vacopts->and_analyze)
				appendPQExpBufferStr(sql, " ANALYZE");
		}
	}

	if (table)
		appendPQExpBuffer(sql, " %s", table);
	appendPQExpBufferChar(sql, ';');
}
Ejemplo n.º 30
0
/* ----------
 * slon_remoteListenThread
 *
 * Listen for events on a remote database connection. This means, events
 * generated by every other node we listen for on this one.
 * ----------
 */
void *
remoteListenThread_main(void *cdata)
{
	SlonNode   *node = (SlonNode *) cdata;
	SlonConn   *conn = NULL;
	char	   *conn_conninfo = NULL;
	char		conn_symname[64];
	ScheduleStatus rc;
	int			retVal;
	SlonDString query1;
	PGconn	   *dbconn = NULL;
	PGresult   *res;

	struct listat *listat_head;
	struct listat *listat_tail;
	int64		last_config_seq = 0;
	int64		new_config_seq = 0;

	slon_log(SLON_INFO,
			 "remoteListenThread_%d: thread starts\n",
			 node->no_id);

	/*
	 * Initialize local data
	 */
	listat_head = NULL;
	listat_tail = NULL;
	dstring_init(&query1);

	poll_sleep = 0;

	sprintf(conn_symname, "node_%d_listen", node->no_id);

	/*
	 * Work until doomsday
	 */
	while (true)
	{
		if (last_config_seq != (new_config_seq = rtcfg_seq_get()))
		{
			/*
			 * Lock the configuration and check if we are (still) supposed to
			 * exist.
			 */
			rtcfg_lock();

			/*
			 * If we have a database connection to the remote node, check if
			 * there was a change in the connection information.
			 */
			if (conn != NULL)
			{
				if (node->pa_conninfo == NULL ||
					strcmp(conn_conninfo, node->pa_conninfo) != 0)
				{
					slon_log(SLON_CONFIG,
							 "remoteListenThread_%d: "
							 "disconnecting from '%s'\n",
							 node->no_id, conn_conninfo);
					slon_disconnectdb(conn);
					free(conn_conninfo);

					conn = NULL;
					conn_conninfo = NULL;
				}
			}

			/*
			 * Check our node's listen_status
			 */
			if (node->listen_status == SLON_TSTAT_NONE ||
				node->listen_status == SLON_TSTAT_SHUTDOWN ||
				!((bool) node->no_active))
			{
				rtcfg_unlock();
				break;
			}
			if (node->listen_status == SLON_TSTAT_RESTART)
				node->listen_status = SLON_TSTAT_RUNNING;

			/*
			 * Adjust the listat list and see if there is anything to listen
			 * for. If not, sleep for a while and check again, some node
			 * reconfiguration must be going on here.
			 */
			remoteListen_adjust_listat(node, &listat_head, &listat_tail);

			last_config_seq = new_config_seq;

			if (listat_head == NULL)
			{
				rtcfg_unlock();
				slon_log(SLON_DEBUG2,
						 "remoteListenThread_%d: nothing to listen for\n",
						 node->no_id);
				rc = sched_msleep(node, 10000);
				if (rc != SCHED_STATUS_OK && rc != SCHED_STATUS_CANCEL)
					break;
				continue;
			}
			rtcfg_unlock();
		}

		/*
		 * Check if we have a database connection
		 */
		if (conn == NULL)
		{
			int			pa_connretry;

			/*
			 * Make sure we have connection info
			 */
			rtcfg_lock();
			if (node->pa_conninfo == NULL)
			{
				slon_log(SLON_WARN,
						 "remoteListenThread_%d: no conninfo - "
						 "sleep 10 seconds\n",
						 node->no_id);

				rtcfg_unlock();
				rc = sched_msleep(node, 10000);
				if (rc != SCHED_STATUS_OK && rc != SCHED_STATUS_CANCEL)
					break;

				continue;
			}

			/*
			 * Try to establish a database connection to the remote node's
			 * database.
			 */
			conn_conninfo = strdup(node->pa_conninfo);
			pa_connretry = node->pa_connretry;
			rtcfg_unlock();

			conn = slon_connectdb(conn_conninfo, conn_symname);
			if (conn == NULL)
			{
				free(conn_conninfo);
				conn_conninfo = NULL;

				slon_log(SLON_WARN,
						 "remoteListenThread_%d: DB connection failed - "
						 "sleep %d seconds\n",
						 node->no_id, pa_connretry);

				rc = sched_msleep(node, pa_connretry * 1000);
				if (rc != SCHED_STATUS_OK && rc != SCHED_STATUS_CANCEL)
					break;

				continue;
			}
			dbconn = conn->dbconn;
			monitor_state("remote listener", node->no_id, conn->conn_pid, "thread main loop", 0, "n/a");

			/*
			 * Listen on the connection for events and confirmations and
			 * register the node connection.
			 */
			(void) slon_mkquery(&query1,
								"select %s.registerNodeConnection(%d); ",
								rtcfg_namespace, rtcfg_nodeid);

			res = PQexec(dbconn, dstring_data(&query1));
			if (PQresultStatus(res) != PGRES_TUPLES_OK)
			{
				slon_log(SLON_ERROR,
						 "remoteListenThread_%d: \"%s\" - %s",
						 node->no_id,
						 dstring_data(&query1), PQresultErrorMessage(res));
				PQclear(res);
				slon_disconnectdb(conn);
				free(conn_conninfo);
				conn = NULL;
				conn_conninfo = NULL;

				rc = sched_msleep(node, pa_connretry * 1000);
				if (rc != SCHED_STATUS_OK && rc != SCHED_STATUS_CANCEL)
					break;

				continue;
			}
			PQclear(res);
			retVal = db_getLocalNodeId(dbconn);
			if (retVal != node->no_id)
			{
				slon_log(SLON_ERROR,
						 "remoteListenThread_%d: db_getLocalNodeId() "
						 "returned %d - wrong database?\n",
						 node->no_id, retVal);

				slon_disconnectdb(conn);
				free(conn_conninfo);
				conn = NULL;
				conn_conninfo = NULL;

				rc = sched_msleep(node, pa_connretry * 1000);
				if (rc != SCHED_STATUS_OK && rc != SCHED_STATUS_CANCEL)
					break;

				continue;
			}
			if (db_checkSchemaVersion(dbconn) < 0)
			{
				slon_log(SLON_ERROR,
						 "remoteListenThread_%d: db_checkSchemaVersion() "
						 "failed\n",
						 node->no_id);

				slon_disconnectdb(conn);
				free(conn_conninfo);
				conn = NULL;
				conn_conninfo = NULL;

				rc = sched_msleep(node, pa_connretry * 1000);
				if (rc != SCHED_STATUS_OK && rc != SCHED_STATUS_CANCEL)
					break;

				continue;
			}
			if (PQserverVersion(dbconn) >= 90100)
			{
				slon_mkquery(&query1, "SET SESSION CHARACTERISTICS AS TRANSACTION read only deferrable");
				res = PQexec(dbconn, dstring_data(&query1));
				if (PQresultStatus(res) != PGRES_COMMAND_OK)
				{
					slon_log(SLON_ERROR,
							 "remoteListenThread_%d: \"%s\" - %s",
							 node->no_id,
						   dstring_data(&query1), PQresultErrorMessage(res));
					PQclear(res);
					slon_disconnectdb(conn);
					free(conn_conninfo);
					conn = NULL;
					conn_conninfo = NULL;
					rc = sched_msleep(node, pa_connretry * 1000);
					if (rc != SCHED_STATUS_OK && rc != SCHED_STATUS_CANCEL)
						break;

					continue;
				}

			}
			if (PQserverVersion(dbconn) >= 90100)
			{
				slon_mkquery(&query1, "SET SESSION CHARACTERISTICS AS TRANSACTION read only isolation level serializable deferrable");
				res = PQexec(dbconn, dstring_data(&query1));
				if (PQresultStatus(res) != PGRES_COMMAND_OK)
				{
					slon_log(SLON_ERROR,
							 "remoteListenThread_%d: \"%s\" - %s",
							 node->no_id,
						   dstring_data(&query1), PQresultErrorMessage(res));
					PQclear(res);
					slon_disconnectdb(conn);
					free(conn_conninfo);
					conn = NULL;
					conn_conninfo = NULL;
					rc = sched_msleep(node, pa_connretry * 1000);
					if (rc != SCHED_STATUS_OK && rc != SCHED_STATUS_CANCEL)
						break;

					continue;
				}

			}
			slon_log(SLON_DEBUG1,
					 "remoteListenThread_%d: connected to '%s'\n",
					 node->no_id, conn_conninfo);

		}

		/*
		 * Receive events from the provider node
		 */
		retVal = remoteListen_receive_events(node, conn, listat_head);
		if (retVal < 0)
		{
			slon_disconnectdb(conn);
			free(conn_conninfo);
			conn = NULL;
			conn_conninfo = NULL;

			rc = sched_msleep(node, 10000);
			if (rc != SCHED_STATUS_OK && rc != SCHED_STATUS_CANCEL)
				break;

			continue;
		}

		/*
		 * If the remote node notified for new confirmations, read them and
		 * queue them into the remote worker for storage in our local
		 * database.
		 */

		retVal = remoteListen_forward_confirm(node, conn);
		if (retVal < 0)
		{
			slon_disconnectdb(conn);
			free(conn_conninfo);
			conn = NULL;
			conn_conninfo = NULL;

			rc = sched_msleep(node, 10000);
			if (rc != SCHED_STATUS_OK && rc != SCHED_STATUS_CANCEL)
				break;

			continue;
		}

		/*
		 * Wait for notification.
		 */

		rc = sched_wait_time(conn, SCHED_WAIT_SOCK_READ, poll_sleep);
		if (rc == SCHED_STATUS_CANCEL)
			continue;
		if (rc != SCHED_STATUS_OK)
			break;

	}

	/*
	 * Doomsday!
	 */
	if (conn != NULL)
	{
		slon_log(SLON_INFO,
				 "remoteListenThread_%d: "
				 "disconnecting from '%s'\n",
				 node->no_id, conn_conninfo);
		slon_disconnectdb(conn);
		free(conn_conninfo);
		conn = NULL;
		conn_conninfo = NULL;
	}
	remoteListen_cleanup(&listat_head, &listat_tail);

	rtcfg_lock();
	node->listen_status = SLON_TSTAT_DONE;
	rtcfg_unlock();

	slon_log(SLON_DEBUG1,
			 "remoteListenThread_%d: thread done\n",
			 node->no_id);
	dstring_free(&query1);
	pthread_exit(NULL);
}