Skip to content

Commit 5fe3f03

Browse files
ItGackyItGacky
committed
version 1.0.5.
- Disable autovacuum for working tables and update logs. - Do ANALYZE automatically after reorg unless -Z, --no-analyze option is specified.
1 parent 9a8f2e9 commit 5fe3f03

File tree

9 files changed

+229
-98
lines changed

9 files changed

+229
-98
lines changed

‎bin/pg_reorg.c

Lines changed: 53 additions & 35 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
* @brief Client Modules
99
*/
1010

11-
const char *PROGRAM_VERSION = "1.0.4";
11+
const char *PROGRAM_VERSION = "1.0.5";
1212
const char *PROGRAM_URL = "http://reorg.projects.postgresql.org/";
1313
const char *PROGRAM_EMAIL = "reorg-general@lists.pgfoundry.org";
1414

@@ -92,9 +92,9 @@ static bool sqlstate_equals(PGresult *res, const char *state)
9292
return strcmp(PQresultErrorField(res, PG_DIAG_SQLSTATE), state) == 0;
9393
}
9494

95-
static bool echo = false;
9695
static bool verbose = false;
9796
static bool quiet = false;
97+
static bool analyze = true;
9898

9999
/*
100100
* The table begin re-organized. If not null, we need to cleanup temp
@@ -110,16 +110,14 @@ utoa(unsigned int value, char *buffer)
110110
return buffer;
111111
}
112112

113-
const char *pgut_optstring = "eqvat:no:";
114-
115-
const struct option pgut_longopts[] = {
116-
{"echo", no_argument, NULL, 'e'},
113+
const struct option pgut_options[] = {
117114
{"quiet", no_argument, NULL, 'q'},
118115
{"verbose", no_argument, NULL, 'v'},
119116
{"all", no_argument, NULL, 'a'},
120117
{"table", required_argument, NULL, 't'},
121118
{"no-order", no_argument, NULL, 'n'},
122119
{"order-by", required_argument, NULL, 'o'},
120+
{"no-analyze", no_argument, NULL, 'Z'},
123121
{NULL, 0, NULL, 0}
124122
};
125123

@@ -132,9 +130,6 @@ pgut_argument(int c, const char *arg)
132130
{
133131
switch (c)
134132
{
135-
case 'e':
136-
echo = true;
137-
break;
138133
case 'q':
139134
quiet = true;
140135
break;
@@ -153,6 +148,9 @@ pgut_argument(int c, const char *arg)
153148
case 'o':
154149
assign_option(&orderby, c, arg);
155150
break;
151+
case 'Z':
152+
analyze = false;
153+
break;
156154
default:
157155
return false;
158156
}
@@ -249,9 +247,9 @@ reorg_one_database(const char *orderby, const char *table)
249247
PGresult *res;
250248
int i;
251249
int num;
252-
PQExpBufferData sql;
250+
StringInfoData sql;
253251

254-
initPQExpBuffer(&sql);
252+
initStringInfo(&sql);
255253

256254
reconnect();
257255

@@ -262,18 +260,18 @@ reorg_one_database(const char *orderby, const char *table)
262260
command("SET client_min_messages = warning", 0, NULL);
263261

264262
/* acquire target tables */
265-
appendPQExpBufferStr(&sql, "SELECT * FROM reorg.tables WHERE ");
263+
appendStringInfoString(&sql, "SELECT * FROM reorg.tables WHERE ");
266264
if (table)
267265
{
268-
appendPQExpBufferStr(&sql, "relid = $1::regclass");
269-
res = execute_nothrow(sql.data, 1, &table);
266+
appendStringInfoString(&sql, "relid = $1::regclass");
267+
res = execute_elevel(sql.data, 1, &table, LOG);
270268
}
271269
else
272270
{
273-
appendPQExpBufferStr(&sql, "pkid IS NOT NULL");
271+
appendStringInfoString(&sql, "pkid IS NOT NULL");
274272
if (!orderby)
275-
appendPQExpBufferStr(&sql, " AND ckid IS NOT NULL");
276-
res = execute_nothrow(sql.data, 0, NULL);
273+
appendStringInfoString(&sql, " AND ckid IS NOT NULL");
274+
res = execute_elevel(sql.data, 0, NULL, LOG);
277275
}
278276

279277
if (PQresultStatus(res) != PGRES_TUPLES_OK)
@@ -321,13 +319,13 @@ reorg_one_database(const char *orderby, const char *table)
321319
table.lock_table = getstr(res, i, c++);
322320
ckey = getstr(res, i, c++);
323321

324-
resetPQExpBuffer(&sql);
322+
resetStringInfo(&sql);
325323
if (!orderby)
326324
{
327325
/* CLUSTER mode */
328326
if (ckey == NULL)
329327
elog(ERROR, "relation \"%s\" has no cluster key", table.target_name);
330-
appendPQExpBuffer(&sql, "%s ORDER BY %s", create_table, ckey);
328+
appendStringInfo(&sql, "%s ORDER BY %s", create_table, ckey);
331329
table.create_table = sql.data;
332330
}
333331
else if (!orderby[0])
@@ -338,7 +336,7 @@ reorg_one_database(const char *orderby, const char *table)
338336
else
339337
{
340338
/* User specified ORDER BY */
341-
appendPQExpBuffer(&sql, "%s ORDER BY %s", create_table, orderby);
339+
appendStringInfo(&sql, "%s ORDER BY %s", create_table, orderby);
342340
table.create_table = sql.data;
343341
}
344342

@@ -354,7 +352,7 @@ reorg_one_database(const char *orderby, const char *table)
354352
cleanup:
355353
PQclear(res);
356354
disconnect();
357-
termPQExpBuffer(&sql);
355+
termStringInfo(&sql);
358356
return ret;
359357
}
360358

@@ -387,12 +385,15 @@ apply_log(const reorg_table *table, int count)
387385
static void
388386
reorg_one_table(const reorg_table *table, const char *orderby)
389387
{
390-
PGresult *res;
391-
const char *params[1];
392-
int num;
393-
int i;
394-
char *vxid;
395-
char buffer[12];
388+
PGresult *res;
389+
const char *params[1];
390+
int num;
391+
int i;
392+
char *vxid;
393+
char buffer[12];
394+
StringInfoData sql;
395+
396+
initStringInfo(&sql);
396397

397398
if (verbose)
398399
{
@@ -430,16 +431,16 @@ reorg_one_table(const reorg_table *table, const char *orderby)
430431
*/
431432
params[0] = utoa(table->target_oid, buffer);
432433

433-
res = execute(
434-
"SELECT 1 FROM pg_trigger"
435-
" WHERE tgrelid = $1 AND tgname >= 'z_reorg_trigger' LIMIT 1",
436-
1, params);
434+
res = execute("SELECT reorg.conflicted_triggers($1)", 1, params);
437435
if (PQntuples(res) > 0)
438-
elog(ERROR, "trigger conflicted for %s", table->target_name);
436+
elog(ERROR, "trigger %s conflicted for %s",
437+
PQgetvalue(res, 0, 0), table->target_name);
439438

440439
command(table->create_pktype, 0, NULL);
441440
command(table->create_log, 0, NULL);
442441
command(table->create_trigger, 0, NULL);
442+
printfStringInfo(&sql, "SELECT reorg.disable_autovacuum('reorg.log_%u')", table->target_oid);
443+
command(sql.data, 0, NULL);
443444
command("COMMIT", 0, NULL);
444445

445446
/*
@@ -465,6 +466,8 @@ reorg_one_table(const reorg_table *table, const char *orderby)
465466
PQclear(res);
466467
command(table->delete_log, 0, NULL);
467468
command(table->create_table, 0, NULL);
469+
printfStringInfo(&sql, "SELECT reorg.disable_autovacuum('reorg.table_%u')", table->target_oid);
470+
command(sql.data, 0, NULL);
468471
command("COMMIT", 0, NULL);
469472

470473
/*
@@ -537,7 +540,7 @@ reorg_one_table(const reorg_table *table, const char *orderby)
537540
for (;;)
538541
{
539542
command("BEGIN ISOLATION LEVEL READ COMMITTED", 0, NULL);
540-
res = execute_nothrow(table->lock_table, 0, NULL);
543+
res = execute_elevel(table->lock_table, 0, NULL, NOTICE);
541544
if (PQresultStatus(res) == PGRES_COMMAND_OK)
542545
{
543546
PQclear(res);
@@ -577,8 +580,23 @@ reorg_one_table(const reorg_table *table, const char *orderby)
577580
command("COMMIT", 0, NULL);
578581

579582
current_table = NULL;
580-
581583
free(vxid);
584+
585+
/*
586+
* 7. Analyze.
587+
* Note that current_table is already set to NULL here because analyze
588+
* is an unimportant operation; No clean up even if failed.
589+
*/
590+
if (verbose)
591+
fprintf(stderr, "---- analyze ----\n");
592+
593+
command("BEGIN ISOLATION LEVEL READ COMMITTED", 0, NULL);
594+
printfStringInfo(&sql, "ANALYZE %s%s",
595+
(verbose ? "VERBOSE " : ""), table->target_name);
596+
command(sql.data, 0, NULL);
597+
command("COMMIT", 0, NULL);
598+
599+
termStringInfo(&sql);
582600
}
583601

584602
void
@@ -624,7 +642,7 @@ pgut_help(void)
624642
" -t, --table=TABLE reorg specific table only\n"
625643
" -n, --no-order do vacuum full instead of cluster\n"
626644
" -o, --order-by=columns order by columns instead of cluster keys\n"
627-
" -e, --echo show the commands being sent to the server\n"
645+
" -Z, --no-analyze don't analyze at end\n"
628646
" -q, --quiet don't write any messages\n"
629647
" -v, --verbose display detailed information during processing\n",
630648
PROGRAM_NAME, PROGRAM_NAME);

0 commit comments

Comments
 (0)