/* establish connection with database. */
PGconn *
sql_conn(struct options * my_opts)
{
PGconn *conn;
char *password = NULL;
bool new_pass;
/*
* Start the connection. Loop until we have a password if requested by
* backend.
*/
do
{
new_pass = false;
conn = PQsetdbLogin(my_opts->hostname,
my_opts->port,
NULL, /* options */
NULL, /* tty */
my_opts->dbname,
my_opts->username,
password);
if (!conn)
{
fprintf(stderr, "%s: could not connect to database %s\n",
"oid2name", my_opts->dbname);
exit(1);
}
if (PQstatus(conn) == CONNECTION_BAD &&
PQconnectionNeedsPassword(conn) &&
password == NULL)
{
PQfinish(conn);
password = simple_prompt("Password: "******"%s: could not connect to database %s: %s",
"oid2name", my_opts->dbname, PQerrorMessage(conn));
PQfinish(conn);
exit(1);
}
/* return the conn if good */
return conn;
}
/*
* Make a database connection with the given parameters. An
* interactive password prompt is automatically issued if required.
*/
PGconn *
connectDatabase(const char *dbname, const char *pghost, const char *pgport,
const char *pguser, enum trivalue prompt_password,
const char *progname)
{
PGconn *conn;
char *password = NULL;
bool new_pass;
if (prompt_password == TRI_YES)
password = simple_prompt("Password: "******"%s: could not connect to database %s\n"),
progname, dbname);
exit(1);
}
if (PQstatus(conn) == CONNECTION_BAD &&
PQconnectionNeedsPassword(conn) &&
password == NULL &&
prompt_password != TRI_NO)
{
PQfinish(conn);
password = simple_prompt("Password: "******"%s: could not connect to database %s: %s"),
progname, dbname, PQerrorMessage(conn));
exit(1);
}
return conn;
}
static PGconn *
loginDatabase(char *host, int port, char *user, char *password, char *dbname, const char *progname, char *encoding, char *password_prompt)
{
bool new_pass = false;
PGconn *coord_conn;
char port_s[32];
#define PARAMS_ARRAY_SIZE 8
const char *keywords[PARAMS_ARRAY_SIZE];
const char *values[PARAMS_ARRAY_SIZE];
sprintf(port_s, "%d", port);
keywords[0] = "host";
values[0] = host;
keywords[1] = "port";
values[1] = port_s;
keywords[2] = "user";
values[2] = user;
keywords[3] = "password";
keywords[4] = "dbname";
values[4] = dbname;
keywords[5] = "fallback_application_name";
values[5] = progname;
keywords[6] = "client_encoding";
values[6] = encoding;
keywords[7] = NULL;
values[7] = NULL;
/* Loop until we have a password if requested by backend */
do
{
values[3] = password;
new_pass = false;
coord_conn = PQconnectdbParams(keywords, values, true);
if (PQstatus(coord_conn) == CONNECTION_BAD &&
PQconnectionNeedsPassword(coord_conn) &&
password == NULL &&
try_password_opt != TRI_NO)
{
PQfinish(coord_conn);
password = simple_prompt(password_prompt, 100, false);
new_pass = true;
}
} while (new_pass);
return(coord_conn);
}
PGconn *
pgut_connect(void)
{
PGconn *conn;
if (interrupted && !in_cleanup)
ereport(FATAL,
(errcode(ERROR_INTERRUPTED),
errmsg("interrupted")));
#ifndef PGUT_NO_PROMPT
if (prompt_password == YES)
prompt_for_password(username);
#endif
/* Start the connection. Loop until we have a password if requested by backend. */
for (;;)
{
conn = PQsetdbLogin(host, port, NULL, NULL, dbname, username, password);
if (PQstatus(conn) == CONNECTION_OK)
return conn;
#ifndef PGUT_NO_PROMPT
if (conn && PQconnectionNeedsPassword(conn) && prompt_password != NO)
{
PQfinish(conn);
prompt_for_password(username);
continue;
}
#endif
ereport(ERROR,
(errcode(ERROR_PG_CONNECT),
errmsg("could not connect to database %s: %s", dbname, PQerrorMessage(conn))));
PQfinish(conn);
return NULL;
}
}
/*
* Make a database connection with the given parameters. An
* interactive password prompt is automatically issued if required.
*/
PGconn *
connectDatabase(const char *dbname, const char *pghost, const char *pgport,
const char *pguser, enum trivalue prompt_password,
const char *progname)
{
PGconn *conn;
char *password = NULL;
bool new_pass;
if (prompt_password == TRI_YES)
password = simple_prompt("Password: "******"%s: out of memory\n"), progname);
exit(1);
}
keywords[0] = "host";
values[0] = pghost;
keywords[1] = "port";
values[1] = pgport;
keywords[2] = "user";
values[2] = pguser;
keywords[3] = "password";
values[3] = password;
keywords[4] = "dbname";
values[4] = dbname;
keywords[5] = "fallback_application_name";
values[5] = progname;
keywords[6] = NULL;
values[6] = NULL;
new_pass = false;
conn = PQconnectdbParams(keywords, values, true);
free(keywords);
free(values);
if (!conn)
{
fprintf(stderr, _("%s: could not connect to database %s\n"),
progname, dbname);
exit(1);
}
if (PQstatus(conn) == CONNECTION_BAD &&
PQconnectionNeedsPassword(conn) &&
password == NULL &&
prompt_password != TRI_NO)
{
PQfinish(conn);
password = simple_prompt("Password: "******"%s: could not connect to database %s: %s"),
progname, dbname, PQerrorMessage(conn));
exit(1);
}
return conn;
}
/*
* do_connect -- handler for \connect
*
* Connects to a database with given parameters. If there exists an
* established connection, NULL values will be replaced with the ones
* in the current connection. Otherwise NULL will be passed for that
* parameter to PQconnectdbParams(), so the libpq defaults will be used.
*
* In interactive mode, if connection fails with the given parameters,
* the old connection will be kept.
*/
static bool
do_connect(char *dbname, char *user, char *host, char *port)
{
PGconn *o_conn = pset.db,
*n_conn;
char *password = NULL;
if (!dbname)
dbname = PQdb(o_conn);
if (!user)
user = PQuser(o_conn);
if (!host)
host = PQhost(o_conn);
if (!port)
port = PQport(o_conn);
/*
* If the user asked to be prompted for a password, ask for one now. If
* not, use the password from the old connection, provided the username
* has not changed. Otherwise, try to connect without a password first,
* and then ask for a password if needed.
*
* XXX: this behavior leads to spurious connection attempts recorded in
* the postmaster's log. But libpq offers no API that would let us obtain
* a password and then continue with the first connection attempt.
*/
if (pset.getPassword == TRI_YES)
{
password = prompt_for_password(user);
}
else if (o_conn && user && strcmp(PQuser(o_conn), user) == 0)
{
password = strdup(PQpass(o_conn));
}
while (true)
{
#define PARAMS_ARRAY_SIZE 7
const char **keywords = pg_malloc(PARAMS_ARRAY_SIZE * sizeof(*keywords));
const char **values = pg_malloc(PARAMS_ARRAY_SIZE * sizeof(*values));
keywords[0] = "host";
values[0] = host;
keywords[1] = "port";
values[1] = port;
keywords[2] = "user";
values[2] = user;
keywords[3] = "password";
values[3] = password;
keywords[4] = "dbname";
values[4] = dbname;
keywords[5] = "fallback_application_name";
values[5] = pset.progname;
keywords[6] = NULL;
values[6] = NULL;
n_conn = PQconnectdbParams(keywords, values, true);
free(keywords);
free(values);
/* We can immediately discard the password -- no longer needed */
if (password)
free(password);
if (PQstatus(n_conn) == CONNECTION_OK)
break;
/*
* Connection attempt failed; either retry the connection attempt with
* a new password, or give up.
*/
if (!password && PQconnectionNeedsPassword(n_conn) && pset.getPassword != TRI_NO)
{
PQfinish(n_conn);
password = prompt_for_password(user);
continue;
}
/*
* Failed to connect to the database. In interactive mode, keep the
* previous connection to the DB; in scripting mode, close our
* previous connection as well.
*/
if (pset.cur_cmd_interactive)
{
psql_error("%s", PQerrorMessage(n_conn));
/* pset.db is left unmodified */
if (o_conn)
fputs(_("Previous connection kept\n"), stderr);
}
else
{
psql_error("\\connect: %s", PQerrorMessage(n_conn));
if (o_conn)
{
PQfinish(o_conn);
pset.db = NULL;
}
}
PQfinish(n_conn);
return false;
}
/*
* Replace the old connection with the new one, and update
* connection-dependent variables.
*/
PQsetNoticeProcessor(n_conn, NoticeProcessor, NULL);
pset.db = n_conn;
SyncVariables();
connection_warnings(false); /* Must be after SyncVariables */
/* Tell the user about the new connection */
if (!pset.quiet)
{
if (param_is_newly_set(PQhost(o_conn), PQhost(pset.db)) ||
param_is_newly_set(PQport(o_conn), PQport(pset.db)))
{
char *host = PQhost(pset.db);
if (host == NULL)
host = DEFAULT_PGSOCKET_DIR;
/* If the host is an absolute path, the connection is via socket */
if (is_absolute_path(host))
printf(_("You are now connected to database \"%s\" as user \"%s\" via socket in \"%s\" at port \"%s\".\n"),
PQdb(pset.db), PQuser(pset.db), host, PQport(pset.db));
else
printf(_("You are now connected to database \"%s\" as user \"%s\" on host \"%s\" at port \"%s\".\n"),
PQdb(pset.db), PQuser(pset.db), host, PQport(pset.db));
}
else
printf(_("You are now connected to database \"%s\" as user \"%s\".\n"),
PQdb(pset.db), PQuser(pset.db));
}
if (o_conn)
PQfinish(o_conn);
return true;
}
/*
* Connect to the db again.
*
* Note: it's not really all that sensible to use a single-entry password
* cache if the username keeps changing. In current usage, however, the
* username never does change, so one savedPassword is sufficient. We do
* update the cache on the off chance that the password has changed since the
* start of the run.
*/
static PGconn *
_connectDB(ArchiveHandle *AH, const char *reqdb, const char *requser)
{
PGconn *newConn;
const char *newdb;
const char *newuser;
char *password;
bool new_pass;
if (!reqdb)
newdb = PQdb(AH->connection);
else
newdb = reqdb;
if (!requser || strlen(requser) == 0)
newuser = PQuser(AH->connection);
else
newuser = requser;
ahlog(AH, 1, "connecting to database \"%s\" as user \"%s\"\n",
newdb, newuser);
password = AH->savedPassword ? pg_strdup(AH->savedPassword) : NULL;
if (AH->promptPassword == TRI_YES && password == NULL)
{
password = simple_prompt("Password: "******"out of memory\n");
}
do
{
const char *keywords[7];
const char *values[7];
keywords[0] = "host";
values[0] = PQhost(AH->connection);
keywords[1] = "port";
values[1] = PQport(AH->connection);
keywords[2] = "user";
values[2] = newuser;
keywords[3] = "password";
values[3] = password;
keywords[4] = "dbname";
values[4] = newdb;
keywords[5] = "fallback_application_name";
values[5] = progname;
keywords[6] = NULL;
values[6] = NULL;
new_pass = false;
newConn = PQconnectdbParams(keywords, values, true);
if (!newConn)
exit_horribly(modulename, "failed to reconnect to database\n");
if (PQstatus(newConn) == CONNECTION_BAD)
{
if (!PQconnectionNeedsPassword(newConn))
exit_horribly(modulename, "could not reconnect to database: %s",
PQerrorMessage(newConn));
PQfinish(newConn);
if (password)
fprintf(stderr, "Password incorrect\n");
fprintf(stderr, "Connecting to %s as %s\n",
newdb, newuser);
if (password)
free(password);
if (AH->promptPassword != TRI_NO)
password = simple_prompt("Password: "******"connection needs password\n");
if (password == NULL)
exit_horribly(modulename, "out of memory\n");
new_pass = true;
}
} while (new_pass);
/*
* We want to remember connection's actual password, whether or not we got
* it by prompting. So we don't just store the password variable.
*/
if (PQconnectionUsedPassword(newConn))
{
if (AH->savedPassword)
free(AH->savedPassword);
AH->savedPassword = pg_strdup(PQpass(newConn));
}
if (password)
free(password);
/* check for version mismatch */
_check_database_version(AH);
PQsetNoticeProcessor(newConn, notice_processor, NULL);
return newConn;
}
/*
* Make a database connection with the given parameters. The
* connection handle is returned, the parameters are stored in AHX.
* An interactive password prompt is automatically issued if required.
*
* Note: it's not really all that sensible to use a single-entry password
* cache if the username keeps changing. In current usage, however, the
* username never does change, so one savedPassword is sufficient.
*/
void
ConnectDatabase(Archive *AHX,
const char *dbname,
const char *pghost,
const char *pgport,
const char *username,
trivalue prompt_password)
{
ArchiveHandle *AH = (ArchiveHandle *) AHX;
char *password;
bool new_pass;
if (AH->connection)
exit_horribly(modulename, "already connected to a database\n");
password = AH->savedPassword ? pg_strdup(AH->savedPassword) : NULL;
if (prompt_password == TRI_YES && password == NULL)
{
password = simple_prompt("Password: "******"out of memory\n");
}
AH->promptPassword = prompt_password;
/*
* Start the connection. Loop until we have a password if requested by
* backend.
*/
do
{
const char *keywords[7];
const char *values[7];
keywords[0] = "host";
values[0] = pghost;
keywords[1] = "port";
values[1] = pgport;
keywords[2] = "user";
values[2] = username;
keywords[3] = "password";
values[3] = password;
keywords[4] = "dbname";
values[4] = dbname;
keywords[5] = "fallback_application_name";
values[5] = progname;
keywords[6] = NULL;
values[6] = NULL;
new_pass = false;
AH->connection = PQconnectdbParams(keywords, values, true);
if (!AH->connection)
exit_horribly(modulename, "failed to connect to database\n");
if (PQstatus(AH->connection) == CONNECTION_BAD &&
PQconnectionNeedsPassword(AH->connection) &&
password == NULL &&
prompt_password != TRI_NO)
{
PQfinish(AH->connection);
password = simple_prompt("Password: "******"out of memory\n");
new_pass = true;
}
} while (new_pass);
/* check to see that the backend connection was successfully made */
if (PQstatus(AH->connection) == CONNECTION_BAD)
exit_horribly(modulename, "connection to database \"%s\" failed: %s",
PQdb(AH->connection) ? PQdb(AH->connection) : "",
PQerrorMessage(AH->connection));
/*
* We want to remember connection's actual password, whether or not we got
* it by prompting. So we don't just store the password variable.
*/
if (PQconnectionUsedPassword(AH->connection))
{
if (AH->savedPassword)
free(AH->savedPassword);
AH->savedPassword = pg_strdup(PQpass(AH->connection));
}
if (password)
free(password);
/* check for version mismatch */
_check_database_version(AH);
PQsetNoticeProcessor(AH->connection, notice_processor, NULL);
}
/*
* parse the given command line options and try to connect to the db.
*
* On success, the db conn is returned inside options->db
*/
int handle_options(int argc, char** argv, struct adhoc_opts * options)
{
char *password = NULL;
char *password_prompt = NULL;
bool new_pass = true;
parse_psql_options(argc, argv, options);
if (!options->action_string)
{
fprintf(stderr, "Error: Must specify an sql command\n\n");
usage();
exit(1);
}
if (options->username == NULL)
password_prompt = pg_strdup(_("Password: "******"Password for user %s: "),
options->username);
if (pset.getPassword == TRI_YES)
password = simple_prompt(password_prompt, 100, false);
do
{
#define PARAMS_ARRAY_SIZE 8
const char **keywords =
pg_malloc(PARAMS_ARRAY_SIZE * sizeof(*keywords));
const char **values = pg_malloc(PARAMS_ARRAY_SIZE * sizeof(*values));
keywords[0] = "host";
values[0] = options->host;
keywords[1] = "port";
values[1] = options->port;
keywords[2] = "user";
values[2] = options->username;
keywords[3] = "password";
values[3] = password;
keywords[4] = "dbname";
values[4] = options->dbname;
keywords[5] = "fallback_application_name";
values[5] = pset.progname;
keywords[6] = "client_encoding";
values[6] = (pset.notty || getenv("PGCLIENTENCODING")) ? NULL : "auto";
keywords[7] = NULL;
values[7] = NULL;
new_pass = false;
pset.db = PQconnectdbParams(keywords, values, true);
free(keywords);
free(values);
if (PQstatus(pset.db) == CONNECTION_BAD &&
PQconnectionNeedsPassword(pset.db) &&
password == NULL &&
pset.getPassword != TRI_NO)
{
PQfinish(pset.db);
password = simple_prompt(password_prompt, 100, false);
new_pass = true;
}
} while (new_pass);
options->db = pset.db;
return 0;
}
/*
* Find the pgport and try a connection
* Note that the checkpoint parameter enables a Windows service control
* manager checkpoint, it's got nothing to do with database checkpoints!!
*/
static bool
test_postmaster_connection(bool do_checkpoint __attribute__((unused)))
{
PGconn *conn;
bool success = false;
int i;
char portstr[32];
char *p;
char *q;
char connstr[128]; /* Should be way more than enough! */
static const char *backend_options = "'-c gp_session_role=utility'";
*portstr = '\0';
/*
* Look in post_opts for a -p switch.
*
* This parsing code is not amazingly bright; it could for instance get
* fooled if ' -p' occurs within a quoted argument value. Given that few
* people pass complicated settings in post_opts, it's probably good
* enough.
*/
for (p = post_opts; *p;)
{
/* advance past whitespace */
while (isspace((unsigned char) *p))
p++;
if (strncmp(p, "-p", 2) == 0)
{
p += 2;
/* advance past any whitespace/quoting */
while (isspace((unsigned char) *p) || *p == '\'' || *p == '"')
p++;
/* find end of value (not including any ending quote!) */
q = p;
while (*q &&
!(isspace((unsigned char) *q) || *q == '\'' || *q == '"'))
q++;
/* and save the argument value */
strlcpy(portstr, p, Min((q - p) + 1, sizeof(portstr)));
/* keep looking, maybe there is another -p */
p = q;
}
/* Advance to next whitespace */
while (*p && !isspace((unsigned char) *p))
p++;
}
/*
* Search config file for a 'port' option.
*
* This parsing code isn't amazingly bright either, but it should be okay
* for valid port settings.
*/
if (!*portstr)
{
char **optlines;
optlines = readfile(conf_file);
if (optlines != NULL)
{
for (; *optlines != NULL; optlines++)
{
p = *optlines;
while (isspace((unsigned char) *p))
p++;
if (strncmp(p, "port", 4) != 0)
continue;
p += 4;
while (isspace((unsigned char) *p))
p++;
if (*p != '=')
continue;
p++;
/* advance past any whitespace/quoting */
while (isspace((unsigned char) *p) || *p == '\'' || *p == '"')
p++;
/* find end of value (not including any ending quote/comment!) */
q = p;
while (*q &&
!(isspace((unsigned char) *q) ||
*q == '\'' || *q == '"' || *q == '#'))
q++;
/* and save the argument value */
strlcpy(portstr, p, Min((q - p) + 1, sizeof(portstr)));
/* keep looking, maybe there is another */
}
}
}
/* Check environment */
if (!*portstr && getenv("PGPORT") != NULL)
strlcpy(portstr, getenv("PGPORT"), sizeof(portstr));
/* Else use compiled-in default */
if (!*portstr)
snprintf(portstr, sizeof(portstr), "%d", DEF_PGPORT);
/*
* We need to set a connect timeout otherwise on Windows the SCM will
* probably timeout first
*/
snprintf(connstr, sizeof(connstr),
"dbname=template1 port=%s connect_timeout=5 options=%s", portstr, backend_options);
for (i = 0; i < wait_seconds; i++)
{
if ((conn = PQconnectdb(connstr)) != NULL &&
(PQstatus(conn) == CONNECTION_OK ||
PQconnectionNeedsPassword(conn)))
{
PQfinish(conn);
success = true;
break;
}
else
{
PQfinish(conn);
#if defined(WIN32)
if (do_checkpoint)
{
/*
* Increment the wait hint by 6 secs (connection timeout +
* sleep) We must do this to indicate to the SCM that our
* startup time is changing, otherwise it'll usually send a
* stop signal after 20 seconds, despite incrementing the
* checkpoint counter.
*/
status.dwWaitHint += 6000;
status.dwCheckPoint++;
SetServiceStatus(hStatus, (LPSERVICE_STATUS) &status);
}
else
#endif
print_msg(".");
/*
* The postmaster should create postmaster.pid very soon after being
* started. If it's not there after we've waited 30 or more seconds,
* assume startup failed and give up waiting. (Note this covers both
* cases where the pidfile was never created, and where it was created
* and then removed during postmaster exit.)
*/
if (i >= (wait_seconds / 2))
{
pgpid_t pid = get_pgpid();
if (pid == 0) /* no pid file */
{
write_stderr(_("%s: PID file \"%s\" does not exist\n"), progname, pid_file);
break;
}
if (! postmaster_is_alive((pid_t) pid))
{
write_stderr(_("%s: postmaster pid %ld not running\n"), progname, pid);
break;
}
}
pg_usleep(1000000); /* 1 sec */
}
}
return success;
}
/*
* 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);
}
/*
* Connect to the server. Returns a valid PGconn pointer if connected,
* or NULL on non-permanent error. On permanent error, the function will
* call exit(1) directly.
*/
PGconn *
GetConnection(void)
{
PGconn *tmpconn;
int argcount = 7; /* dbname, replication, fallback_app_name,
* host, user, port, password */
int i;
const char **keywords;
const char **values;
const char *tmpparam;
bool need_password;
PQconninfoOption *conn_opts = NULL;
PQconninfoOption *conn_opt;
char *err_msg = NULL;
/*
* Merge the connection info inputs given in form of connection string,
* options and default values (dbname=replication, replication=true, etc.)
*/
i = 0;
if (connection_string)
{
conn_opts = PQconninfoParse(connection_string, &err_msg);
if (conn_opts == NULL)
{
fprintf(stderr, "%s: %s", progname, err_msg);
exit(1);
}
for (conn_opt = conn_opts; conn_opt->keyword != NULL; conn_opt++)
{
if (conn_opt->val != NULL && conn_opt->val[0] != '\0')
argcount++;
}
keywords = pg_malloc0((argcount + 1) * sizeof(*keywords));
values = pg_malloc0((argcount + 1) * sizeof(*values));
for (conn_opt = conn_opts; conn_opt->keyword != NULL; conn_opt++)
{
if (conn_opt->val != NULL && conn_opt->val[0] != '\0')
{
keywords[i] = conn_opt->keyword;
values[i] = conn_opt->val;
i++;
}
}
}
else
{
keywords = pg_malloc0((argcount + 1) * sizeof(*keywords));
values = pg_malloc0((argcount + 1) * sizeof(*values));
}
keywords[i] = "dbname";
values[i] = "replication";
i++;
keywords[i] = "replication";
values[i] = "true";
i++;
keywords[i] = "fallback_application_name";
values[i] = progname;
i++;
if (dbhost)
{
keywords[i] = "host";
values[i] = dbhost;
i++;
}
if (dbuser)
{
keywords[i] = "user";
values[i] = dbuser;
i++;
}
if (dbport)
{
keywords[i] = "port";
values[i] = dbport;
i++;
}
/* If -W was given, force prompt for password, but only the first time */
need_password = (dbgetpassword == 1 && dbpassword == NULL);
while (true)
{
/* Get a new password if appropriate */
if (need_password)
{
if (dbpassword)
free(dbpassword);
dbpassword = simple_prompt(_("Password: "******"password";
values[i] = dbpassword;
}
else
{
keywords[i] = NULL;
values[i] = NULL;
}
tmpconn = PQconnectdbParams(keywords, values, true);
/*
* If there is too little memory even to allocate the PGconn object
* and PQconnectdbParams returns NULL, we call exit(1) directly.
*/
if (!tmpconn)
{
fprintf(stderr, _("%s: could not connect to server\n"),
progname);
exit(1);
}
/* If we need a password and -w wasn't given, loop back and get one */
if (PQstatus(tmpconn) == CONNECTION_BAD &&
PQconnectionNeedsPassword(tmpconn) &&
dbgetpassword != -1)
{
PQfinish(tmpconn);
need_password = true;
}
else
break;
}
if (PQstatus(tmpconn) != CONNECTION_OK)
{
fprintf(stderr, _("%s: could not connect to server: %s\n"),
progname, PQerrorMessage(tmpconn));
PQfinish(tmpconn);
free(values);
free(keywords);
if (conn_opts)
PQconninfoFree(conn_opts);
return NULL;
}
/* Connection ok! */
free(values);
free(keywords);
if (conn_opts)
PQconninfoFree(conn_opts);
/*
* Ensure we have the same value of integer timestamps as the server we
* are connecting to.
*/
tmpparam = PQparameterStatus(tmpconn, "integer_datetimes");
if (!tmpparam)
{
fprintf(stderr,
_("%s: could not determine server setting for integer_datetimes\n"),
progname);
PQfinish(tmpconn);
exit(1);
}
#ifdef HAVE_INT64_TIMESTAMP
if (strcmp(tmpparam, "on") != 0)
#else
if (strcmp(tmpparam, "off") != 0)
#endif
{
fprintf(stderr,
_("%s: integer_datetimes compile flag does not match server\n"),
progname);
PQfinish(tmpconn);
exit(1);
}
return tmpconn;
}
/* establish connection with database. */
PGconn *
sql_conn(struct options * my_opts)
{
PGconn *conn;
char *password = NULL;
bool new_pass;
/*
* Start the connection. Loop until we have a password if requested by
* backend.
*/
do
{
#define PARAMS_ARRAY_SIZE 7
const char *keywords[PARAMS_ARRAY_SIZE];
const char *values[PARAMS_ARRAY_SIZE];
keywords[0] = "host";
values[0] = my_opts->hostname;
keywords[1] = "port";
values[1] = my_opts->port;
keywords[2] = "user";
values[2] = my_opts->username;
keywords[3] = "password";
values[3] = password;
keywords[4] = "dbname";
values[4] = my_opts->dbname;
keywords[5] = "fallback_application_name";
values[5] = my_opts->progname;
keywords[6] = NULL;
values[6] = NULL;
new_pass = false;
conn = PQconnectdbParams(keywords, values, true);
if (!conn)
{
fprintf(stderr, "%s: could not connect to database %s\n",
"oid2name", my_opts->dbname);
exit(1);
}
if (PQstatus(conn) == CONNECTION_BAD &&
PQconnectionNeedsPassword(conn) &&
password == NULL)
{
PQfinish(conn);
password = simple_prompt("Password: "******"%s: could not connect to database %s: %s",
"oid2name", my_opts->dbname, PQerrorMessage(conn));
PQfinish(conn);
exit(1);
}
/* return the conn if good */
return conn;
}
int
main (int argc, char **argv)
{
int opt = 0; /* Option Identifier */
int optindex = 0; /* Option Index */
bool isProfiling = false; /* Are we profiling the cache? */
bool isPriming = false; /* Are we priming the cache? */
long numCPUs = 0; /* The number of online CPUs */
struct dirent *dp = NULL; /* Directory Entry Pointer */
DIR *dfd = NULL; /* Directory Stream */
double loadAverages[3] = { 0.00 }; /* System Load Averages */
PGconn *pgh = NULL; /* Postgres Connection Handle */
bool isPWDRequired = false; /* Is Postgres Password Reqd? */
struct option long_options[] = /* Options for getopt() */
{
{"connect-string", required_argument, NULL, 'c'},
{"profile", no_argument, NULL, 'p'},
{"prime", no_argument, NULL, 'w'},
{"data-dir", required_argument, NULL, 'D'},
{"postgres-only", no_argument, NULL, 'o'},
{"sqlite-db", required_argument, NULL, 's'},
{"help", no_argument, NULL, 'h'},
{"debug", no_argument, NULL, 'd'},
{NULL, 0, NULL, 0}
};
/* Go for the glory! */
fprintf(stderr, "\n%s: Release %s - %s\n", PACKAGE_NAME,
PACKAGE_VERSION, APP_RELEASE);
fprintf(stderr, "\n%s\n\n", APP_COPYRIGHT);
fflush(stdout);
/* Process command-line options */
while ((opt = getopt_long(argc, argv, "c:s:D:awhdp",
long_options, &optindex)) != -1)
{
switch (opt)
{
case 'h':
usage();
exit(EXIT_SUCCESS);
break;
case 'p':
if (isPriming == false)
isProfiling = true;
else
{
fprintf(stderr,
"Profiling and warming are mutually exlusive!\n");
exit(EXIT_FAILURE);
}
break;
case 'w':
if (isProfiling == false)
isPriming = true;
else
{
fprintf(stderr,
"Profiling and warming are mutually exlusive!\n");
exit(EXIT_FAILURE);
}
break;
case 'd':
is_debug = true;
break;
case 's':
dbFileName = xstrdup(optarg);
break;
case 'c':
pgConnectString = xstrdup(optarg);
break;
case 'D':
pgDataDir = optarg;
break;
default:
usage();
exit(EXIT_FAILURE);
}
}
/* Make sure user requested profile OR prime */
if (isProfiling == false && isPriming == false)
{
fprintf(stderr, "Expected either -p or -w\n");
usage();
exit(EXIT_FAILURE);
}
/* Make sure the database name is set */
/* Get the PG log file name from the end of the command line */
if (optind < (argc - 1))
{
fprintf(stderr, "too many command-line arguments (first is \"%s\")\n",
argv[optind + 1]);
usage();
exit(EXIT_FAILURE);
}
/* Perform a Postgres connection test & get password (if required) */
do
{
isPWDRequired = false;
pgh = PQsetdbLogin(NULL, NULL, NULL, NULL,
pgConnectString == NULL ? "postgres"
: pgConnectString,
NULL, pgPassword);
if (PQstatus(pgh) == CONNECTION_BAD)
{
if (PQconnectionNeedsPassword(pgh) && pgPassword == NULL)
{
printf("\nTesting Postgres Connection\n");
PQfinish(pgh);
pgPassword = simple_prompt("Password: "******"%s", "Connection Test Failed\n");
ERROR_PRINT("SQLERRMC: %s\n", PQerrorMessage(pgh));
PQfinish(pgh);
exit(EXIT_FAILURE);
}
}
} while (isPWDRequired);
PQfinish(pgh);
/* Get the number of available CPUs */
numCPUs = sysconf(_SC_NPROCESSORS_ONLN);
if (numCPUs < 1)
numCPUs = 1;
/*
* Choose the number of CPUs to use in the thread pool based on load
* average. It only makes sense to do this if we have more than one CPU
* to play with.
*/
if ((numCPUs > 1)
&& (getloadavg(loadAverages, 3) == 3))
{
long idleCPUs = 0; /* The number of idle CPUs */
/* Show what we got */
printf("load averages.... %3.2f %3.2f %3.2f\n",
loadAverages[0], loadAverages[1], loadAverages[2]);
/*
* We're going to base the number of usable CPUs by subtracting
* the sum of 1 (to account for OS and I/O overhead) plus the 1 minute
* load average from the number of available CPUs.
*/
idleCPUs = numCPUs - (1 + (int)(loadAverages[0] + 0.5));
/* Assign # of available CPUs with some sanity checking */
if (idleCPUs < numCPUs)
numCPUs = idleCPUs;
if (numCPUs < 1)
numCPUs = 1;
}
/* Inform user of # of CPUs that will be used */
printf("usable CPUs...... %d\n", numCPUs);
/* If we have more than one CPU, multi-thread our operations */
if (numCPUs > 1)
{
/* Initialize the thread pool */
thp = tpool_init(numCPUs, 1024, true);
}
if (isProfiling)
BuildCacheProfile();
else /* isPriming */
PrimeCache();
/* If we have more than one CPU, multi-thread our operations */
if (POINTER_IS_VALID(thp))
{
/* Destroy the thread pool */
tpool_destroy(thp, 1);
}
/* Cleanup */
free(dbFileName);
return EXIT_SUCCESS;
} /* main() */
PGconn *
pgut_connect(const char *info, YesNo prompt, int elevel)
{
char *passwd;
StringInfoData add_pass;
if (prompt == YES)
{
passwd = prompt_for_password();
initStringInfo(&add_pass);
appendStringInfoString(&add_pass, info);
appendStringInfo(&add_pass, " password=%s ", passwd);
}
else
{
passwd = NULL;
add_pass.data = NULL;
}
/* Start the connection. Loop until we have a password if requested by backend. */
for (;;)
{
PGconn *conn;
CHECK_FOR_INTERRUPTS();
if (!passwd)
conn = PQconnectdb(info);
else
conn = PQconnectdb(add_pass.data);
if (PQstatus(conn) == CONNECTION_OK)
{
pgutConn *c;
c = pgut_new(pgutConn);
c->conn = conn;
c->cancel = NULL;
pgut_conn_lock();
c->next = pgut_connections;
pgut_connections = c;
pgut_conn_unlock();
if (add_pass.data != NULL)
termStringInfo(&add_pass);
free(passwd);
return conn;
}
if (conn && PQconnectionNeedsPassword(conn) && prompt != NO)
{
PQfinish(conn);
free(passwd);
passwd = prompt_for_password();
if (add_pass.data != NULL)
resetStringInfo(&add_pass);
else
initStringInfo(&add_pass);
appendStringInfoString(&add_pass, info);
appendStringInfo(&add_pass, " password=%s ", passwd);
continue;
}
if (add_pass.data != NULL)
termStringInfo(&add_pass);
free(passwd);
ereport(elevel,
(errcode(E_PG_CONNECT),
errmsg("could not connect to database with \"%s\": %s",
info, PQerrorMessage(conn))));
PQfinish(conn);
return NULL;
}
}
/*
* This vacuums LOs of one database. It returns 0 on success, -1 on failure.
*/
int
vacuumlo(char *database, struct _param * param)
{
PGconn *conn;
PGresult *res,
*res2;
char buf[BUFSIZE];
int matched;
int deleted;
int i;
static char *password = NULL;
bool new_pass;
if (param->pg_prompt == TRI_YES && password == NULL)
password = simple_prompt("Password: "******"Connection to database \"%s\" failed\n",
database);
return -1;
}
if (PQstatus(conn) == CONNECTION_BAD &&
PQconnectionNeedsPassword(conn) &&
password == NULL &&
param->pg_prompt != TRI_NO)
{
PQfinish(conn);
password = simple_prompt("Password: "******"Connection to database \"%s\" failed:\n%s",
database, PQerrorMessage(conn));
PQfinish(conn);
return -1;
}
if (param->verbose)
{
fprintf(stdout, "Connected to %s\n", database);
if (param->dry_run)
fprintf(stdout, "Test run: no large objects will be removed!\n");
}
/*
* Don't get fooled by any non-system catalogs
*/
res = PQexec(conn, "SET search_path = pg_catalog");
if (PQresultStatus(res) != PGRES_COMMAND_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 = 'r'");
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);
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);
return -1;
}
PQclear(res2);
}
PQclear(res);
/*
* Run the actual deletes in a single transaction. Note that this would
* be a bad idea in pre-7.1 Postgres releases (since rolling back a table
* delete used to cause problems), but it should be safe now.
*/
res = PQexec(conn, "begin");
PQclear(res);
/*
* Finally, those entries remaining in vacuum_l are orphans.
*/
buf[0] = '\0';
strcat(buf, "SELECT lo ");
strcat(buf, "FROM vacuum_l");
res = PQexec(conn, buf);
if (PQresultStatus(res) != PGRES_TUPLES_OK)
{
fprintf(stderr, "Failed to read temp table:\n");
fprintf(stderr, "%s", PQerrorMessage(conn));
PQclear(res);
PQfinish(conn);
return -1;
}
matched = PQntuples(res);
deleted = 0;
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));
}
else
deleted++;
}
else
deleted++;
}
PQclear(res);
/*
* That's all folks!
*/
res = PQexec(conn, "end");
PQclear(res);
PQfinish(conn);
if (param->verbose)
fprintf(stdout, "\r%s %d large objects from %s.\n",
(param->dry_run ? "Would remove" : "Removed"), deleted, database);
return 0;
}
/*
* Find the pgport and try a connection
* Note that the checkpoint parameter enables a Windows service control
* manager checkpoint, it's got nothing to do with database checkpoints!!
*/
static bool
test_postmaster_connection(bool do_checkpoint)
{
PGconn *conn;
bool success = false;
int i;
char portstr[32];
char *p;
char *q;
char connstr[128]; /* Should be way more than enough! */
*portstr = '\0';
/*
* Look in post_opts for a -p switch.
*
* This parsing code is not amazingly bright; it could for instance get
* fooled if ' -p' occurs within a quoted argument value. Given that few
* people pass complicated settings in post_opts, it's probably good
* enough.
*/
for (p = post_opts; *p;)
{
/* advance past whitespace */
while (isspace((unsigned char) *p))
p++;
if (strncmp(p, "-p", 2) == 0)
{
p += 2;
/* advance past any whitespace/quoting */
while (isspace((unsigned char) *p) || *p == '\'' || *p == '"')
p++;
/* find end of value (not including any ending quote!) */
q = p;
while (*q &&
!(isspace((unsigned char) *q) || *q == '\'' || *q == '"'))
q++;
/* and save the argument value */
strlcpy(portstr, p, Min((q - p) + 1, sizeof(portstr)));
/* keep looking, maybe there is another -p */
p = q;
}
/* Advance to next whitespace */
while (*p && !isspace((unsigned char) *p))
p++;
}
/*
* Search config file for a 'port' option.
*
* This parsing code isn't amazingly bright either, but it should be okay
* for valid port settings.
*/
if (!*portstr)
{
char **optlines;
optlines = readfile(conf_file);
if (optlines != NULL)
{
for (; *optlines != NULL; optlines++)
{
p = *optlines;
while (isspace((unsigned char) *p))
p++;
if (strncmp(p, "port", 4) != 0)
continue;
p += 4;
while (isspace((unsigned char) *p))
p++;
if (*p != '=')
continue;
p++;
/* advance past any whitespace/quoting */
while (isspace((unsigned char) *p) || *p == '\'' || *p == '"')
p++;
/* find end of value (not including any ending quote/comment!) */
q = p;
while (*q &&
!(isspace((unsigned char) *q) ||
*q == '\'' || *q == '"' || *q == '#'))
q++;
/* and save the argument value */
strlcpy(portstr, p, Min((q - p) + 1, sizeof(portstr)));
/* keep looking, maybe there is another */
}
}
}
/* Check environment */
if (!*portstr && getenv("PGPORT") != NULL)
strlcpy(portstr, getenv("PGPORT"), sizeof(portstr));
/* Else use compiled-in default */
if (!*portstr)
snprintf(portstr, sizeof(portstr), "%d", DEF_PGPORT);
/*
* We need to set a connect timeout otherwise on Windows the SCM will
* probably timeout first
*/
snprintf(connstr, sizeof(connstr),
"dbname=postgres port=%s connect_timeout=5", portstr);
for (i = 0; i < wait_seconds; i++)
{
if ((conn = PQconnectdb(connstr)) != NULL &&
(PQstatus(conn) == CONNECTION_OK ||
PQconnectionNeedsPassword(conn)))
{
PQfinish(conn);
success = true;
break;
}
else
{
PQfinish(conn);
#if defined(WIN32)
if (do_checkpoint)
{
/*
* Increment the wait hint by 6 secs (connection timeout +
* sleep) We must do this to indicate to the SCM that our
* startup time is changing, otherwise it'll usually send a
* stop signal after 20 seconds, despite incrementing the
* checkpoint counter.
*/
status.dwWaitHint += 6000;
status.dwCheckPoint++;
SetServiceStatus(hStatus, (LPSERVICE_STATUS) &status);
}
else
#endif
print_msg(".");
pg_usleep(1000000); /* 1 sec */
}
}
return success;
}
/*
* Connect to the server. Returns a valid PGconn pointer if connected,
* or NULL on non-permanent error. On permanent error, the function will
* call exit(1) directly.
*/
PGconn *
GetConnection(void)
{
PGconn *tmpconn;
int argcount = 4; /* dbname, replication, fallback_app_name,
* password */
int i;
const char **keywords;
const char **values;
char *password = NULL;
const char *tmpparam;
if (dbhost)
argcount++;
if (dbuser)
argcount++;
if (dbport)
argcount++;
keywords = pg_malloc0((argcount + 1) * sizeof(*keywords));
values = pg_malloc0((argcount + 1) * sizeof(*values));
keywords[0] = "dbname";
values[0] = "replication";
keywords[1] = "replication";
values[1] = "true";
keywords[2] = "fallback_application_name";
values[2] = progname;
i = 3;
if (dbhost)
{
keywords[i] = "host";
values[i] = dbhost;
i++;
}
if (dbuser)
{
keywords[i] = "user";
values[i] = dbuser;
i++;
}
if (dbport)
{
keywords[i] = "port";
values[i] = dbport;
i++;
}
while (true)
{
if (password)
free(password);
if (dbpassword)
{
/*
* We've saved a password when a previous connection succeeded,
* meaning this is the call for a second session to the same
* database, so just forcibly reuse that password.
*/
keywords[argcount - 1] = "password";
values[argcount - 1] = dbpassword;
dbgetpassword = -1; /* Don't try again if this fails */
}
else if (dbgetpassword == 1)
{
password = simple_prompt(_("Password: "******"password";
values[argcount - 1] = password;
}
tmpconn = PQconnectdbParams(keywords, values, true);
/*
* If there is too little memory even to allocate the PGconn object
* and PQconnectdbParams returns NULL, we call exit(1) directly.
*/
if (!tmpconn)
{
fprintf(stderr, _("%s: could not connect to server\n"),
progname);
exit(1);
}
if (PQstatus(tmpconn) == CONNECTION_BAD &&
PQconnectionNeedsPassword(tmpconn) &&
dbgetpassword != -1)
{
dbgetpassword = 1; /* ask for password next time */
PQfinish(tmpconn);
continue;
}
if (PQstatus(tmpconn) != CONNECTION_OK)
{
fprintf(stderr, _("%s: could not connect to server: %s\n"),
progname, PQerrorMessage(tmpconn));
PQfinish(tmpconn);
free(values);
free(keywords);
return NULL;
}
/* Connection ok! */
free(values);
free(keywords);
/*
* Ensure we have the same value of integer timestamps as the server
* we are connecting to.
*/
tmpparam = PQparameterStatus(tmpconn, "integer_datetimes");
if (!tmpparam)
{
fprintf(stderr,
_("%s: could not determine server setting for integer_datetimes\n"),
progname);
PQfinish(tmpconn);
exit(1);
}
#ifdef HAVE_INT64_TIMESTAMP
if (strcmp(tmpparam, "on") != 0)
#else
if (strcmp(tmpparam, "off") != 0)
#endif
{
fprintf(stderr,
_("%s: integer_datetimes compile flag does not match server\n"),
progname);
PQfinish(tmpconn);
exit(1);
}
/* Store the password for next run */
if (password)
dbpassword = password;
return tmpconn;
}
}
/*
* Make a database connection with the given parameters. An
* interactive password prompt is automatically issued if required.
*
* If fail_on_error is false, we return NULL without printing any message
* on failure, but preserve any prompted password for the next try.
*/
static PGconn *
connectDatabase(const char *dbname, const char *pghost, const char *pgport,
const char *pguser, enum trivalue prompt_password, bool fail_on_error)
{
PGconn *conn;
bool new_pass;
const char *remoteversion_str;
int my_version;
static char *password = NULL;
if (prompt_password == TRI_YES && !password)
password = simple_prompt("Password: "******"%s: out of memory\n"), progname);
exit(1);
}
keywords[0] = "host";
values[0] = pghost;
keywords[1] = "port";
values[1] = pgport;
keywords[2] = "user";
values[2] = pguser;
keywords[3] = "password";
values[3] = password;
keywords[4] = "dbname";
values[4] = dbname;
keywords[5] = "fallback_application_name";
values[5] = progname;
keywords[6] = "options";
values[6] = "-c gp_session_role=utility";
keywords[7] = NULL;
values[7] = NULL;
new_pass = false;
conn = PQconnectdbParams(keywords, values, true);
free(keywords);
free(values);
if (!conn)
{
fprintf(stderr, _("%s: could not connect to database \"%s\"\n"),
progname, dbname);
exit(1);
}
if (PQstatus(conn) == CONNECTION_BAD &&
PQconnectionNeedsPassword(conn) &&
password == NULL &&
prompt_password != TRI_NO)
{
PQfinish(conn);
password = simple_prompt("Password: "******"%s: could not connect to database \"%s\": %s\n"),
progname, dbname, PQerrorMessage(conn));
exit(1);
}
else
{
PQfinish(conn);
return NULL;
}
}
remoteversion_str = PQparameterStatus(conn, "server_version");
if (!remoteversion_str)
{
fprintf(stderr, _("%s: could not get server version\n"), progname);
exit(1);
}
server_version = parse_version(remoteversion_str);
if (server_version < 0)
{
fprintf(stderr, _("%s: could not parse server version \"%s\"\n"),
progname, remoteversion_str);
exit(1);
}
my_version = parse_version(PG_VERSION);
if (my_version < 0)
{
fprintf(stderr, _("%s: could not parse version \"%s\"\n"),
progname, PG_VERSION);
exit(1);
}
if (my_version != server_version
&& (server_version < 80200 /* we can handle back to 8.2 */
|| server_version > my_version))
{
fprintf(stderr, _("server version: %s; %s version: %s\n"),
remoteversion_str, progname, PG_VERSION);
if (ignoreVersion)
fprintf(stderr, _("proceeding despite version mismatch\n"));
else
{
fprintf(stderr, _("aborting because of version mismatch (Use the -i option to proceed anyway.)\n"));
exit(1);
}
}
/*
* On 7.3 and later, make sure we are not fooled by non-system schemas in
* the search path.
*/
executeCommand(conn, "SET search_path = pg_catalog");
return conn;
}
/*
*
* main
*
*/
int
main(int argc, char *argv[])
{
struct adhoc_opts options;
int successResult;
char *password = NULL;
char *password_prompt = NULL;
bool new_pass;
set_pglocale_pgservice(argv[0], PG_TEXTDOMAIN("psql"));
if (argc > 1)
{
if ((strcmp(argv[1], "-?") == 0) || (argc == 2 && (strcmp(argv[1], "--help") == 0)))
{
usage(NOPAGER);
exit(EXIT_SUCCESS);
}
if (strcmp(argv[1], "--version") == 0 || strcmp(argv[1], "-V") == 0)
{
showVersion();
exit(EXIT_SUCCESS);
}
}
#ifdef WIN32
setvbuf(stderr, NULL, _IONBF, 0);
#endif
pset.progname = get_progname(argv[0]);
pset.db = NULL;
setDecimalLocale();
pset.encoding = PQenv2encoding();
pset.queryFout = stdout;
pset.queryFoutPipe = false;
pset.copyStream = NULL;
pset.cur_cmd_source = stdin;
pset.cur_cmd_interactive = false;
/* We rely on unmentioned fields of pset.popt to start out 0/false/NULL */
pset.popt.topt.format = PRINT_ALIGNED;
pset.popt.topt.border = 1;
pset.popt.topt.pager = 1;
pset.popt.topt.pager_min_lines = 0;
pset.popt.topt.start_table = true;
pset.popt.topt.stop_table = true;
pset.popt.topt.default_footer = true;
pset.popt.topt.unicode_border_linestyle = UNICODE_LINESTYLE_SINGLE;
pset.popt.topt.unicode_column_linestyle = UNICODE_LINESTYLE_SINGLE;
pset.popt.topt.unicode_header_linestyle = UNICODE_LINESTYLE_SINGLE;
refresh_utf8format(&(pset.popt.topt));
/* We must get COLUMNS here before readline() sets it */
pset.popt.topt.env_columns = getenv("COLUMNS") ? atoi(getenv("COLUMNS")) : 0;
pset.notty = (!isatty(fileno(stdin)) || !isatty(fileno(stdout)));
pset.getPassword = TRI_DEFAULT;
EstablishVariableSpace();
SetVariable(pset.vars, "VERSION", PG_VERSION_STR);
/* Default values for variables */
SetVariableBool(pset.vars, "AUTOCOMMIT");
SetVariable(pset.vars, "VERBOSITY", "default");
SetVariable(pset.vars, "SHOW_CONTEXT", "errors");
SetVariable(pset.vars, "PROMPT1", DEFAULT_PROMPT1);
SetVariable(pset.vars, "PROMPT2", DEFAULT_PROMPT2);
SetVariable(pset.vars, "PROMPT3", DEFAULT_PROMPT3);
parse_psql_options(argc, argv, &options);
/*
* If no action was specified and we're in non-interactive mode, treat it
* as if the user had specified "-f -". This lets single-transaction mode
* work in this case.
*/
if (options.action == ACT_NOTHING && pset.notty)
{
options.action = ACT_FILE;
options.action_string = NULL;
}
/* Bail out if -1 was specified but will be ignored. */
if (options.single_txn && options.action != ACT_FILE && options.action == ACT_NOTHING)
{
fprintf(stderr, _("%s: -1 can only be used in non-interactive mode\n"), pset.progname);
exit(EXIT_FAILURE);
}
if (!pset.popt.topt.fieldSep.separator &&
!pset.popt.topt.fieldSep.separator_zero)
{
pset.popt.topt.fieldSep.separator = pg_strdup(DEFAULT_FIELD_SEP);
pset.popt.topt.fieldSep.separator_zero = false;
}
if (!pset.popt.topt.recordSep.separator &&
!pset.popt.topt.recordSep.separator_zero)
{
pset.popt.topt.recordSep.separator = pg_strdup(DEFAULT_RECORD_SEP);
pset.popt.topt.recordSep.separator_zero = false;
}
if (options.username == NULL)
password_prompt = pg_strdup(_("Password: "******"Password for user %s: "),
options.username);
if (pset.getPassword == TRI_YES)
password = simple_prompt(password_prompt, 100, false);
/* loop until we have a password if requested by backend */
do
{
#define PARAMS_ARRAY_SIZE 8
const char **keywords = pg_malloc(PARAMS_ARRAY_SIZE * sizeof(*keywords));
const char **values = pg_malloc(PARAMS_ARRAY_SIZE * sizeof(*values));
keywords[0] = "host";
values[0] = options.host;
keywords[1] = "port";
values[1] = options.port;
keywords[2] = "user";
values[2] = options.username;
keywords[3] = "password";
values[3] = password;
keywords[4] = "dbname";
values[4] = (options.action == ACT_LIST_DB &&
options.dbname == NULL) ?
"postgres" : options.dbname;
keywords[5] = "fallback_application_name";
values[5] = pset.progname;
keywords[6] = "client_encoding";
values[6] = (pset.notty || getenv("PGCLIENTENCODING")) ? NULL : "auto";
keywords[7] = NULL;
values[7] = NULL;
new_pass = false;
pset.db = PQconnectdbParams(keywords, values, true);
free(keywords);
free(values);
if (PQstatus(pset.db) == CONNECTION_BAD &&
PQconnectionNeedsPassword(pset.db) &&
password == NULL &&
pset.getPassword != TRI_NO)
{
PQfinish(pset.db);
password = simple_prompt(password_prompt, 100, false);
new_pass = true;
}
} while (new_pass);
free(password);
free(password_prompt);
if (PQstatus(pset.db) == CONNECTION_BAD)
{
fprintf(stderr, "%s: %s", pset.progname, PQerrorMessage(pset.db));
PQfinish(pset.db);
exit(EXIT_BADCONN);
}
setup_cancel_handler();
PQsetNoticeProcessor(pset.db, NoticeProcessor, NULL);
SyncVariables();
if (options.action == ACT_LIST_DB)
{
int success;
if (!options.no_psqlrc)
process_psqlrc(argv[0]);
success = listAllDbs(NULL, false);
PQfinish(pset.db);
exit(success ? EXIT_SUCCESS : EXIT_FAILURE);
}
if (options.logfilename)
{
pset.logfile = fopen(options.logfilename, "a");
if (!pset.logfile)
fprintf(stderr, _("%s: could not open log file \"%s\": %s\n"),
pset.progname, options.logfilename, strerror(errno));
}
/*
* Now find something to do
*/
/*
* process file given by -f
*/
if (options.action == ACT_FILE)
{
if (!options.no_psqlrc)
process_psqlrc(argv[0]);
successResult = process_file(options.action_string, options.single_txn, false);
}
/*
* process slash command if one was given to -c
*/
else if (options.action == ACT_SINGLE_SLASH)
{
PsqlScanState scan_state;
if (pset.echo == PSQL_ECHO_ALL)
puts(options.action_string);
scan_state = psql_scan_create();
psql_scan_setup(scan_state,
options.action_string,
strlen(options.action_string));
successResult = HandleSlashCmds(scan_state, NULL) != PSQL_CMD_ERROR
? EXIT_SUCCESS : EXIT_FAILURE;
psql_scan_destroy(scan_state);
}
/*
* If the query given to -c was a normal one, send it
*/
else if (options.action == ACT_SINGLE_QUERY)
{
if (pset.echo == PSQL_ECHO_ALL)
puts(options.action_string);
successResult = SendQuery(options.action_string)
? EXIT_SUCCESS : EXIT_FAILURE;
}
/*
* or otherwise enter interactive main loop
*/
else
{
if (!options.no_psqlrc)
process_psqlrc(argv[0]);
connection_warnings(true);
if (!pset.quiet)
printf(_("Type \"help\" for help.\n\n"));
initializeInput(options.no_readline ? 0 : 1);
successResult = MainLoop(stdin);
}
/* clean up */
if (pset.logfile)
fclose(pset.logfile);
PQfinish(pset.db);
setQFout(NULL);
return successResult;
}
/*
*
* main
*
*/
int
main(int argc, char *argv[])
{
struct adhoc_opts options;
int successResult;
char *password = NULL;
char *password_prompt = NULL;
bool new_pass;
set_pglocale_pgservice(argv[0], PG_TEXTDOMAIN("psql"));
if (argc > 1)
{
if (strcmp(argv[1], "--help") == 0 || strcmp(argv[1], "-?") == 0)
{
usage();
exit(EXIT_SUCCESS);
}
if (strcmp(argv[1], "--version") == 0 || strcmp(argv[1], "-V") == 0)
{
showVersion();
exit(EXIT_SUCCESS);
}
}
#ifdef WIN32
setvbuf(stderr, NULL, _IONBF, 0);
#endif
setup_cancel_handler();
pset.progname = get_progname(argv[0]);
pset.db = NULL;
setDecimalLocale();
pset.encoding = PQenv2encoding();
pset.queryFout = stdout;
pset.queryFoutPipe = false;
pset.cur_cmd_source = stdin;
pset.cur_cmd_interactive = false;
/* We rely on unmentioned fields of pset.popt to start out 0/false/NULL */
pset.popt.topt.format = PRINT_ALIGNED;
pset.popt.topt.border = 1;
pset.popt.topt.pager = 1;
pset.popt.topt.start_table = true;
pset.popt.topt.stop_table = true;
pset.popt.default_footer = true;
/* We must get COLUMNS here before readline() sets it */
pset.popt.topt.env_columns = getenv("COLUMNS") ? atoi(getenv("COLUMNS")) : 0;
pset.notty = (!isatty(fileno(stdin)) || !isatty(fileno(stdout)));
pset.getPassword = TRI_DEFAULT;
EstablishVariableSpace();
SetVariable(pset.vars, "VERSION", PG_VERSION_STR);
/* Default values for variables */
SetVariableBool(pset.vars, "AUTOCOMMIT");
SetVariable(pset.vars, "VERBOSITY", "default");
SetVariable(pset.vars, "PROMPT1", DEFAULT_PROMPT1);
SetVariable(pset.vars, "PROMPT2", DEFAULT_PROMPT2);
SetVariable(pset.vars, "PROMPT3", DEFAULT_PROMPT3);
parse_psql_options(argc, argv, &options);
if (!pset.popt.topt.fieldSep)
pset.popt.topt.fieldSep = pg_strdup(DEFAULT_FIELD_SEP);
if (!pset.popt.topt.recordSep)
pset.popt.topt.recordSep = pg_strdup(DEFAULT_RECORD_SEP);
if (options.username == NULL)
password_prompt = pg_strdup(_("Password: "******"Password for user %s: ")) - 2 +
strlen(options.username) + 1);
sprintf(password_prompt, _("Password for user %s: "),
options.username);
}
if (pset.getPassword == TRI_YES)
password = simple_prompt(password_prompt, 100, false);
/* loop until we have a password if requested by backend */
do
{
new_pass = false;
pset.db = PQsetdbLogin(options.host, options.port, NULL, NULL,
options.action == ACT_LIST_DB && options.dbname == NULL ?
"postgres" : options.dbname,
options.username, password);
if (PQstatus(pset.db) == CONNECTION_BAD &&
PQconnectionNeedsPassword(pset.db) &&
password == NULL &&
pset.getPassword != TRI_NO)
{
PQfinish(pset.db);
password = simple_prompt(password_prompt, 100, false);
new_pass = true;
}
} while (new_pass);
free(password);
free(password_prompt);
if (PQstatus(pset.db) == CONNECTION_BAD)
{
fprintf(stderr, "%s: %s", pset.progname, PQerrorMessage(pset.db));
PQfinish(pset.db);
exit(EXIT_BADCONN);
}
PQsetNoticeProcessor(pset.db, NoticeProcessor, NULL);
SyncVariables();
if (options.action == ACT_LIST_DB)
{
int success = listAllDbs(false);
PQfinish(pset.db);
exit(success ? EXIT_SUCCESS : EXIT_FAILURE);
}
if (options.logfilename)
{
pset.logfile = fopen(options.logfilename, "a");
if (!pset.logfile)
fprintf(stderr, _("%s: could not open log file \"%s\": %s\n"),
pset.progname, options.logfilename, strerror(errno));
}
/*
* Now find something to do
*/
/*
* process file given by -f
*/
if (options.action == ACT_FILE && strcmp(options.action_string, "-") != 0)
{
if (!options.no_psqlrc)
process_psqlrc(argv[0]);
successResult = process_file(options.action_string, options.single_txn);
}
/*
* process slash command if one was given to -c
*/
else if (options.action == ACT_SINGLE_SLASH)
{
PsqlScanState scan_state;
if (pset.echo == PSQL_ECHO_ALL)
puts(options.action_string);
scan_state = psql_scan_create();
psql_scan_setup(scan_state,
options.action_string,
strlen(options.action_string));
successResult = HandleSlashCmds(scan_state, NULL) != PSQL_CMD_ERROR
? EXIT_SUCCESS : EXIT_FAILURE;
psql_scan_destroy(scan_state);
}
/*
* If the query given to -c was a normal one, send it
*/
else if (options.action == ACT_SINGLE_QUERY)
{
if (pset.echo == PSQL_ECHO_ALL)
puts(options.action_string);
successResult = SendQuery(options.action_string)
? EXIT_SUCCESS : EXIT_FAILURE;
}
/*
* or otherwise enter interactive main loop
*/
else
{
if (!options.no_psqlrc)
process_psqlrc(argv[0]);
connection_warnings();
if (!pset.quiet && !pset.notty)
printf(_("Type \"help\" for help.\n\n"));
if (!pset.notty)
initializeInput(options.no_readline ? 0 : 1);
if (options.action_string) /* -f - was used */
pset.inputfile = "<stdin>";
successResult = MainLoop(stdin);
}
/* clean up */
if (pset.logfile)
fclose(pset.logfile);
PQfinish(pset.db);
setQFout(NULL);
return successResult;
}
