/*
* the result is also available with the global variable 'connection'.
*/
void
reconnect(int elevel)
{
StringInfoData buf;
char *new_password;
disconnect();
initStringInfo(&buf);
if (dbname && dbname[0])
appendStringInfo(&buf, "dbname=%s ", dbname);
if (host && host[0])
appendStringInfo(&buf, "host=%s ", host);
if (port && port[0])
appendStringInfo(&buf, "port=%s ", port);
if (username && username[0])
appendStringInfo(&buf, "user=%s ", username);
if (password && password[0])
appendStringInfo(&buf, "password=%s ", password);
connection = pgut_connect(buf.data, prompt_password, elevel);
/* update password */
if (connection)
{
new_password = PQpass(connection);
if (new_password && new_password[0] &&
(password == NULL || strcmp(new_password, password) != 0))
{
free(password);
password = pgut_strdup(new_password);
}
}
termStringInfo(&buf);
}
int
main(int argc, char *argv[])
{
PGconn *conn;
StringInfoData conn_info;
int num_options;
num_options = pgut_getopt(argc, argv, options);
/* command-line arguments is not necessary */
if (num_options != argc)
ereport(ERROR,
(errcode(EINVAL),
errmsg("too many argumetns")));
/* can't specified the mode two or more */
if ((mode_list && (mode_size || mode_report || mode_snapshot || mode_delete)) ||
(mode_size && (mode_report || mode_snapshot || mode_delete)) ||
(mode_report && (mode_snapshot || mode_delete)) ||
(mode_snapshot && mode_delete))
ereport(ERROR,
(errcode(EINVAL),
errmsg("can't specify two or more mode")));
/* connect to database */
initStringInfo(&conn_info);
if (dbname && dbname[0])
appendStringInfo(&conn_info, "dbname=%s ", dbname);
if (host && host[0])
appendStringInfo(&conn_info, "host=%s ", host);
if (port && port[0])
appendStringInfo(&conn_info, "port=%s ", port);
if (username && username[0])
appendStringInfo(&conn_info, "user=%s ", username);
conn = pgut_connect(conn_info.data, prompt_password, ERROR);
termStringInfo(&conn_info);
/* execute a specified operation */
if (mode_list)
do_list(conn, instid);
else if (mode_size)
do_size(conn);
else if (mode_report)
do_report(conn, mode_report,
instid, beginid, endid, begindate, enddate, output);
else if (mode_snapshot)
do_snapshot(conn, mode_snapshot);
else if (mode_delete)
do_delete(conn, mode_delete);
else
ereport(ERROR,
(errcode(EINVAL),
errmsg("please specify operation option (-l, -s, -r, -S, -D)")));
pgut_disconnect(conn);
return 0;
}
void
execute_with_format_args(int expected, const char *format, int nargs, Oid argtypes[], Datum values[], const bool nulls[], ...)
{
va_list ap;
StringInfoData sql;
initStringInfo(&sql);
va_start(ap, nulls);
appendStringInfoVA_s(&sql, format, ap);
va_end(ap);
execute_with_args(expected, sql.data, nargs, argtypes, values, nulls);
termStringInfo(&sql);
}
/* execute sql with format */
void
execute_with_format(int expected, const char *format, ...)
{
va_list ap;
StringInfoData sql;
int ret;
initStringInfo(&sql);
va_start(ap, format);
appendStringInfoVA_s(&sql, format, ap);
va_end(ap);
if (sql.len == 0)
elog(WARNING, "execute_with_format(%s)", format);
ret = SPI_exec(sql.data, 0);
if EXEC_FAILED(ret, expected)
elog(ERROR, "query failed: (sql=%s, code=%d, expected=%d)", sql.data, ret, expected);
termStringInfo(&sql);
}
/*
* Re-organize one table.
*/
static void
reorg_one_table(const reorg_table *table, const char *orderby)
{
PGresult *res;
const char *params[1];
int num;
int i;
int num_waiting = 0;
char *vxid;
char buffer[12];
StringInfoData sql;
initStringInfo(&sql);
elog(DEBUG2, "---- reorg_one_table ----");
elog(DEBUG2, "target_name : %s", table->target_name);
elog(DEBUG2, "target_oid : %u", table->target_oid);
elog(DEBUG2, "target_toast : %u", table->target_toast);
elog(DEBUG2, "target_tidx : %u", table->target_tidx);
elog(DEBUG2, "pkid : %u", table->pkid);
elog(DEBUG2, "ckid : %u", table->ckid);
elog(DEBUG2, "create_pktype : %s", table->create_pktype);
elog(DEBUG2, "create_log : %s", table->create_log);
elog(DEBUG2, "create_trigger : %s", table->create_trigger);
elog(DEBUG2, "alter_table : %s", table->alter_table);
elog(DEBUG2, "create_table : %s", table->create_table);
elog(DEBUG2, "drop_columns : %s", table->drop_columns ? table->drop_columns : "(skipped)");
elog(DEBUG2, "delete_log : %s", table->delete_log);
elog(DEBUG2, "lock_table : %s", table->lock_table);
elog(DEBUG2, "sql_peek : %s", table->sql_peek);
elog(DEBUG2, "sql_insert : %s", table->sql_insert);
elog(DEBUG2, "sql_delete : %s", table->sql_delete);
elog(DEBUG2, "sql_update : %s", table->sql_update);
elog(DEBUG2, "sql_pop : %s", table->sql_pop);
/*
* 1. Setup workspaces and a trigger.
*/
elog(DEBUG2, "---- setup ----");
lock_exclusive(utoa(table->target_oid, buffer), table->lock_table);
/*
* Check z_reorg_trigger is the trigger executed at last so that
* other before triggers cannot modify triggered tuples.
*/
params[0] = utoa(table->target_oid, buffer);
res = execute("SELECT reorg.conflicted_triggers($1)", 1, params);
if (PQntuples(res) > 0)
ereport(ERROR,
(errcode(E_PG_COMMAND),
errmsg("trigger %s conflicted for %s",
PQgetvalue(res, 0, 0), table->target_name)));
PQclear(res);
command(table->create_pktype, 0, NULL);
command(table->create_log, 0, NULL);
command(table->create_trigger, 0, NULL);
command(table->alter_table, 0, NULL);
printfStringInfo(&sql, "SELECT reorg.disable_autovacuum('reorg.log_%u')", table->target_oid);
command(sql.data, 0, NULL);
command("COMMIT", 0, NULL);
/*
* Register the table to be dropped on error. We use pktype as
* an advisory lock. The registration should be done after
* the first command succeeds.
*/
pgut_atexit_push(&reorg_cleanup, (void *) table);
/*
* 2. Copy tuples into temp table.
*/
elog(DEBUG2, "---- copy tuples ----");
command("BEGIN ISOLATION LEVEL SERIALIZABLE", 0, NULL);
/* SET work_mem = maintenance_work_mem */
command("SELECT set_config('work_mem', current_setting('maintenance_work_mem'), true)", 0, NULL);
if (orderby && !orderby[0])
command("SET LOCAL synchronize_seqscans = off", 0, NULL);
res = execute(SQL_XID_SNAPSHOT, 0, NULL);
vxid = strdup(PQgetvalue(res, 0, 0));
PQclear(res);
command(table->delete_log, 0, NULL);
command(table->create_table, 0, NULL);
printfStringInfo(&sql, "SELECT reorg.disable_autovacuum('reorg.table_%u')", table->target_oid);
if (table->drop_columns)
command(table->drop_columns, 0, NULL);
command(sql.data, 0, NULL);
command("COMMIT", 0, NULL);
/*
* 3. Create indexes on temp table.
*/
elog(DEBUG2, "---- create indexes ----");
params[0] = utoa(table->target_oid, buffer);
res = execute("SELECT indexrelid,"
" reorg.reorg_indexdef(indexrelid, indrelid),"
" indisvalid,"
" pg_get_indexdef(indexrelid)"
" FROM pg_index WHERE indrelid = $1", 1, params);
num = PQntuples(res);
for (i = 0; i < num; i++)
{
reorg_index index;
int c = 0;
const char *isvalid;
const char *indexdef;
index.target_oid = getoid(res, i, c++);
index.create_index = getstr(res, i, c++);
isvalid = getstr(res, i, c++);
indexdef = getstr(res, i, c++);
if (isvalid && isvalid[0] == 'f') {
elog(WARNING, "skipping invalid index: %s", indexdef);
continue;
}
elog(DEBUG2, "[%d]", i);
elog(DEBUG2, "target_oid : %u", index.target_oid);
elog(DEBUG2, "create_index : %s", index.create_index);
/*
* NOTE: If we want to create multiple indexes in parallel,
* we need to call create_index in multiple connections.
*/
command(index.create_index, 0, NULL);
}
PQclear(res);
/*
* 4. Apply log to temp table until no tuples are left in the log
* and all of the old transactions are finished.
*/
for (;;)
{
num = apply_log(table, APPLY_COUNT);
if (num > 0)
continue; /* there might be still some tuples, repeat. */
/* old transactions still alive ? */
params[0] = vxid;
res = execute(SQL_XID_ALIVE, 1, params);
num = PQntuples(res);
if (num > 0)
{
/* Wait for old transactions.
* Only display the message below when the number of
* transactions we are waiting on changes (presumably,
* num_waiting should only go down), so as not to
* be too noisy.
*/
if (num != num_waiting)
{
elog(NOTICE, "Waiting for %d transactions to finish. First PID: %s", num, PQgetvalue(res, 0, 0));
num_waiting = num;
}
PQclear(res);
sleep(1);
continue;
}
else
{
/* All old transactions are finished;
* go to next step. */
PQclear(res);
break;
}
}
/*
* 5. Swap.
*/
elog(DEBUG2, "---- swap ----");
lock_exclusive(utoa(table->target_oid, buffer), table->lock_table);
apply_log(table, 0);
params[0] = utoa(table->target_oid, buffer);
command("SELECT reorg.reorg_swap($1)", 1, params);
command("COMMIT", 0, NULL);
/*
* 6. Drop.
*/
elog(DEBUG2, "---- drop ----");
command("BEGIN ISOLATION LEVEL READ COMMITTED", 0, NULL);
params[0] = utoa(table->target_oid, buffer);
command("SELECT reorg.reorg_drop($1)", 1, params);
command("COMMIT", 0, NULL);
pgut_atexit_pop(&reorg_cleanup, (void *) table);
free(vxid);
/*
* 7. Analyze.
* Note that cleanup hook has been already uninstalled here because analyze
* is not an important operation; No clean up even if failed.
*/
if (analyze)
{
elog(DEBUG2, "---- analyze ----");
command("BEGIN ISOLATION LEVEL READ COMMITTED", 0, NULL);
printfStringInfo(&sql, "ANALYZE %s", table->target_name);
command(sql.data, 0, NULL);
command("COMMIT", 0, NULL);
}
termStringInfo(&sql);
}
/*
* Call reorg_one_table for the target table or each table in a database.
*/
static bool
reorg_one_database(const char *orderby, const char *table)
{
bool ret = true;
PGresult *res;
int i;
int num;
StringInfoData sql;
initStringInfo(&sql);
reconnect(ERROR);
/* Disable statement timeout. */
command("SET statement_timeout = 0", 0, NULL);
/* Restrict search_path to system catalog. */
command("SET search_path = pg_catalog, pg_temp, public", 0, NULL);
/* To avoid annoying "create implicit ..." messages. */
command("SET client_min_messages = warning", 0, NULL);
/* acquire target tables */
appendStringInfoString(&sql, "SELECT * FROM reorg.tables WHERE ");
if (table)
{
appendStringInfoString(&sql, "relid = $1::regclass");
res = execute_elevel(sql.data, 1, &table, DEBUG2);
}
else
{
appendStringInfoString(&sql, "pkid IS NOT NULL");
if (!orderby)
appendStringInfoString(&sql, " AND ckid IS NOT NULL");
res = execute_elevel(sql.data, 0, NULL, DEBUG2);
}
if (PQresultStatus(res) != PGRES_TUPLES_OK)
{
if (sqlstate_equals(res, SQLSTATE_INVALID_SCHEMA_NAME))
{
/* Schema reorg does not exist. Skip the database. */
ret = false;
goto cleanup;
}
else
{
/* exit otherwise */
printf("%s", PQerrorMessage(connection));
PQclear(res);
exit(1);
}
}
num = PQntuples(res);
for (i = 0; i < num; i++)
{
reorg_table table;
const char *create_table;
const char *ckey;
int c = 0;
table.target_name = getstr(res, i, c++);
table.target_oid = getoid(res, i, c++);
table.target_toast = getoid(res, i, c++);
table.target_tidx = getoid(res, i, c++);
table.pkid = getoid(res, i, c++);
table.ckid = getoid(res, i, c++);
if (table.pkid == 0)
ereport(ERROR,
(errcode(E_PG_COMMAND),
errmsg("relation \"%s\" must have a primary key or not-null unique keys", table.target_name)));
table.create_pktype = getstr(res, i, c++);
table.create_log = getstr(res, i, c++);
table.create_trigger = getstr(res, i, c++);
table.alter_table = getstr(res, i, c++);
create_table = getstr(res, i, c++);
table.drop_columns = getstr(res, i, c++);
table.delete_log = getstr(res, i, c++);
table.lock_table = getstr(res, i, c++);
ckey = getstr(res, i, c++);
resetStringInfo(&sql);
if (!orderby)
{
/* CLUSTER mode */
if (ckey == NULL)
ereport(ERROR,
(errcode(E_PG_COMMAND),
errmsg("relation \"%s\" has no cluster key", table.target_name)));
appendStringInfo(&sql, "%s ORDER BY %s", create_table, ckey);
table.create_table = sql.data;
}
else if (!orderby[0])
{
/* VACUUM FULL mode */
table.create_table = create_table;
}
else
{
/* User specified ORDER BY */
appendStringInfo(&sql, "%s ORDER BY %s", create_table, orderby);
table.create_table = sql.data;
}
table.sql_peek = getstr(res, i, c++);
table.sql_insert = getstr(res, i, c++);
table.sql_delete = getstr(res, i, c++);
table.sql_update = getstr(res, i, c++);
table.sql_pop = getstr(res, i, c++);
reorg_one_table(&table, orderby);
}
cleanup:
PQclear(res);
disconnect();
termStringInfo(&sql);
return ret;
}
/**
* @brief Performs data loading.
*
* Invokes pg_bulkload() user-defined function with given parameters
* in single transaction.
*
* @return exitcode (always 0).
*/
static int
LoaderLoadMain(List *options)
{
PGresult *res;
const char *params[1];
StringInfoData buf;
int encoding;
int errors;
ListCell *cell;
if (options == NIL)
ereport(ERROR,
(errcode(EXIT_FAILURE),
errmsg("requires control file or command line options")));
initStringInfo(&buf);
reconnect(ERROR);
encoding = PQclientEncoding(connection);
elog(NOTICE, "BULK LOAD START");
/* form options as text[] */
appendStringInfoString(&buf, "{\"");
foreach (cell, options)
{
const char *item = lfirst(cell);
if (buf.len > 2)
appendStringInfoString(&buf, "\",\"");
/* escape " and \ */
while (*item)
{
if (*item == '"' || *item == '\\')
{
appendStringInfoChar(&buf, '\\');
appendStringInfoChar(&buf, *item);
item++;
}
else if (!IS_HIGHBIT_SET(*item))
{
appendStringInfoChar(&buf, *item);
item++;
}
else
{
int n = PQmblen(item, encoding);
appendBinaryStringInfo(&buf, item, n);
item += n;
}
}
}
appendStringInfoString(&buf, "\"}");
command("BEGIN", 0, NULL);
params[0] = buf.data;
res = execute("SELECT * FROM pg_bulkload($1)", 1, params);
if (PQresultStatus(res) == PGRES_COPY_IN)
{
PQclear(res);
res = RemoteLoad(connection, stdin, type_binary);
if (PQresultStatus(res) != PGRES_TUPLES_OK)
elog(ERROR, "copy failed: %s", PQerrorMessage(connection));
}
command("COMMIT", 0, NULL);
errors = atoi(PQgetvalue(res, 0, 2)) + /* parse errors */
atoi(PQgetvalue(res, 0, 3)); /* duplicate errors */
elog(NOTICE, "BULK LOAD END\n"
"\t%s Rows skipped.\n"
"\t%s Rows successfully loaded.\n"
"\t%s Rows not loaded due to parse errors.\n"
"\t%s Rows not loaded due to duplicate errors.\n"
"\t%s Rows replaced with new rows.",
PQgetvalue(res, 0, 0), PQgetvalue(res, 0, 1),
PQgetvalue(res, 0, 2), PQgetvalue(res, 0, 3),
PQgetvalue(res, 0, 4));
PQclear(res);
disconnect();
termStringInfo(&buf);
if (errors > 0)
{
elog(WARNING, "some rows were not loaded due to errors.");
return E_PG_USER;
}
else
return 0; /* succeeded without errors */
}
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;
}
}
