Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commite0c2933

Browse files
committed
Use catalog query to discover tables to process in vacuumdb
vacuumdb would use a catalog query only when the command caller does notdefine a list of tables. Switching to a catalog table represents twoadvantages:- Relation existence check can happen before running any VACUUM orANALYZE query. Before this change, if multiple relations are definedusing --table, the utility would fail only after processing thefirstly-defined ones, which may be a long some depending on the size ofthe relation. This adds checks for the relation names, and doesnothing, at least yet, for the attribute names.- More filtering options can become available for the utility user.These options, which may be introduced later on, are based on therelation size or the relation age, and need to be made available even ifthe user does not list any specific table with --table.Author: Nathan BossartReviewed-by: Michael Paquier, Masahiko SawadaDiscussion:https://postgr.es/m/FFE5373C-E26A-495B-B5C8-911EC4A41C5E@amazon.com
1 parentda05eb5 commite0c2933

File tree

3 files changed

+147
-74
lines changed

3 files changed

+147
-74
lines changed

‎src/bin/scripts/common.c‎

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -265,9 +265,9 @@ executeMaintenanceCommand(PGconn *conn, const char *query, bool echo)
265265
* finish using them, pg_free(*table). *columns is a pointer into "spec",
266266
* possibly to its NUL terminator.
267267
*/
268-
staticvoid
269-
split_table_columns_spec(constchar*spec,intencoding,
270-
char**table,constchar**columns)
268+
void
269+
splitTableColumnsSpec(constchar*spec,intencoding,
270+
char**table,constchar**columns)
271271
{
272272
boolinquotes= false;
273273
constchar*cp=spec;
@@ -318,7 +318,7 @@ appendQualifiedRelation(PQExpBuffer buf, const char *spec,
318318
return;
319319
}
320320

321-
split_table_columns_spec(spec,PQclientEncoding(conn),&table,&columns);
321+
splitTableColumnsSpec(spec,PQclientEncoding(conn),&table,&columns);
322322

323323
/*
324324
* Query must remain ABSOLUTELY devoid of unqualified names. This would

‎src/bin/scripts/common.h‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -48,6 +48,9 @@ extern void executeCommand(PGconn *conn, const char *query,
4848
externboolexecuteMaintenanceCommand(PGconn*conn,constchar*query,
4949
boolecho);
5050

51+
externvoidsplitTableColumnsSpec(constchar*spec,intencoding,
52+
char**table,constchar**columns);
53+
5154
externvoidappendQualifiedRelation(PQExpBufferbuf,constchar*name,
5255
PGconn*conn,constchar*progname,boolecho);
5356

‎src/bin/scripts/vacuumdb.c‎

Lines changed: 140 additions & 70 deletions
Original file line numberDiff line numberDiff line change
@@ -19,6 +19,7 @@
1919
#include"catalog/pg_class_d.h"
2020

2121
#include"common.h"
22+
#include"fe_utils/connect.h"
2223
#include"fe_utils/simple_list.h"
2324
#include"fe_utils/string_utils.h"
2425

@@ -61,10 +62,8 @@ static void vacuum_all_databases(vacuumingOptions *vacopts,
6162
intconcurrentCons,
6263
constchar*progname,boolecho,boolquiet);
6364

64-
staticvoidprepare_vacuum_command(PQExpBuffersql,PGconn*conn,
65-
vacuumingOptions*vacopts,constchar*table,
66-
booltable_pre_qualified,
67-
constchar*progname,boolecho);
65+
staticvoidprepare_vacuum_command(PQExpBuffersql,intserverVersion,
66+
vacuumingOptions*vacopts,constchar*table);
6867

6968
staticvoidrun_vacuum_command(PGconn*conn,constchar*sql,boolecho,
7069
constchar*table,constchar*progname,boolasync);
@@ -359,13 +358,18 @@ vacuum_one_database(const char *dbname, vacuumingOptions *vacopts,
359358
constchar*progname,boolecho,boolquiet)
360359
{
361360
PQExpBufferDatasql;
361+
PQExpBufferDatabuf;
362+
PQExpBufferDatacatalog_query;
363+
PGresult*res;
362364
PGconn*conn;
363365
SimpleStringListCell*cell;
364366
ParallelSlot*slots;
365367
SimpleStringListdbtables= {NULL,NULL};
366368
inti;
369+
intntups;
367370
boolfailed= false;
368371
boolparallel=concurrentCons>1;
372+
booltables_listed= false;
369373
constchar*stage_commands[]= {
370374
"SET default_statistics_target=1; SET vacuum_cost_delay=0;",
371375
"SET default_statistics_target=10; RESET vacuum_cost_delay;",
@@ -410,53 +414,132 @@ vacuum_one_database(const char *dbname, vacuumingOptions *vacopts,
410414
fflush(stdout);
411415
}
412416

413-
initPQExpBuffer(&sql);
414-
415417
/*
416-
* If a table list is not provided and we're using multiple connections,
417-
* prepare the list of tables by querying the catalogs.
418+
* Prepare the list of tables to process by querying the catalogs.
419+
*
420+
* Since we execute the constructed query with the default search_path
421+
* (which could be unsafe), everything in this query MUST be fully
422+
* qualified.
423+
*
424+
* First, build a WITH clause for the catalog query if any tables were
425+
* specified, with a set of values made of relation names and their
426+
* optional set of columns. This is used to match any provided column
427+
* lists with the generated qualified identifiers and to filter for the
428+
* tables provided via --table. If a listed table does not exist, the
429+
* catalog query will fail.
418430
*/
419-
if (parallel&& (!tables|| !tables->head))
431+
initPQExpBuffer(&catalog_query);
432+
for (cell=tables ?tables->head :NULL;cell;cell=cell->next)
420433
{
421-
PQExpBufferDatabuf;
422-
PGresult*res;
423-
intntups;
424-
425-
initPQExpBuffer(&buf);
426-
427-
res=executeQuery(conn,
428-
"SELECT c.relname, ns.nspname"
429-
" FROM pg_class c, pg_namespace ns\n"
430-
" WHERE relkind IN ("
431-
CppAsString2(RELKIND_RELATION)", "
432-
CppAsString2(RELKIND_MATVIEW)")"
433-
" AND c.relnamespace = ns.oid\n"
434-
" ORDER BY c.relpages DESC;",
435-
progname,echo);
436-
437-
ntups=PQntuples(res);
438-
for (i=0;i<ntups;i++)
439-
{
440-
appendPQExpBufferStr(&buf,
441-
fmtQualifiedId(PQgetvalue(res,i,1),
442-
PQgetvalue(res,i,0)));
434+
char*just_table;
435+
constchar*just_columns;
443436

444-
simple_string_list_append(&dbtables,buf.data);
445-
resetPQExpBuffer(&buf);
437+
/*
438+
* Split relation and column names given by the user, this is used to
439+
* feed the CTE with values on which are performed pre-run validity
440+
* checks as well. For now these happen only on the relation name.
441+
*/
442+
splitTableColumnsSpec(cell->val,PQclientEncoding(conn),
443+
&just_table,&just_columns);
444+
445+
if (!tables_listed)
446+
{
447+
appendPQExpBuffer(&catalog_query,
448+
"WITH listed_tables (table_oid, column_list) "
449+
"AS (\n VALUES (");
450+
tables_listed= true;
446451
}
452+
else
453+
appendPQExpBuffer(&catalog_query,",\n (");
447454

448-
termPQExpBuffer(&buf);
449-
tables=&dbtables;
455+
appendStringLiteralConn(&catalog_query,just_table,conn);
456+
appendPQExpBuffer(&catalog_query,"::pg_catalog.regclass, ");
450457

451-
/*
452-
* If there are more connections than vacuumable relations, we don't
453-
* need to use them all.
454-
*/
458+
if (just_columns&&just_columns[0]!='\0')
459+
appendStringLiteralConn(&catalog_query,just_columns,conn);
460+
else
461+
appendPQExpBufferStr(&catalog_query,"NULL");
462+
463+
appendPQExpBufferStr(&catalog_query,"::pg_catalog.text)");
464+
465+
pg_free(just_table);
466+
}
467+
468+
/* Finish formatting the CTE */
469+
if (tables_listed)
470+
appendPQExpBuffer(&catalog_query,"\n)\n");
471+
472+
appendPQExpBuffer(&catalog_query,"SELECT c.relname, ns.nspname");
473+
474+
if (tables_listed)
475+
appendPQExpBuffer(&catalog_query,", listed_tables.column_list");
476+
477+
appendPQExpBuffer(&catalog_query,
478+
" FROM pg_catalog.pg_class c\n"
479+
" JOIN pg_catalog.pg_namespace ns"
480+
" ON c.relnamespace OPERATOR(pg_catalog.=) ns.oid\n");
481+
482+
/* Used to match the tables listed by the user */
483+
if (tables_listed)
484+
appendPQExpBuffer(&catalog_query," JOIN listed_tables"
485+
" ON listed_tables.table_oid OPERATOR(pg_catalog.=) c.oid\n");
486+
487+
appendPQExpBuffer(&catalog_query," WHERE c.relkind OPERATOR(pg_catalog.=) ANY (array["
488+
CppAsString2(RELKIND_RELATION)", "
489+
CppAsString2(RELKIND_MATVIEW)"])\n");
490+
491+
/*
492+
* Execute the catalog query. We use the default search_path for this
493+
* query for consistency with table lookups done elsewhere by the user.
494+
*/
495+
appendPQExpBuffer(&catalog_query," ORDER BY c.relpages DESC;");
496+
executeCommand(conn,"RESET search_path;",progname,echo);
497+
res=executeQuery(conn,catalog_query.data,progname,echo);
498+
termPQExpBuffer(&catalog_query);
499+
PQclear(executeQuery(conn,ALWAYS_SECURE_SEARCH_PATH_SQL,
500+
progname,echo));
501+
502+
/*
503+
* If no rows are returned, there are no matching tables, so we are done.
504+
*/
505+
ntups=PQntuples(res);
506+
if (ntups==0)
507+
{
508+
PQclear(res);
509+
PQfinish(conn);
510+
return;
511+
}
512+
513+
/*
514+
* Build qualified identifiers for each table, including the column list
515+
* if given.
516+
*/
517+
initPQExpBuffer(&buf);
518+
for (i=0;i<ntups;i++)
519+
{
520+
appendPQExpBufferStr(&buf,
521+
fmtQualifiedId(PQgetvalue(res,i,1),
522+
PQgetvalue(res,i,0)));
523+
524+
if (tables_listed&& !PQgetisnull(res,i,2))
525+
appendPQExpBufferStr(&buf,PQgetvalue(res,i,2));
526+
527+
simple_string_list_append(&dbtables,buf.data);
528+
resetPQExpBuffer(&buf);
529+
}
530+
termPQExpBuffer(&buf);
531+
PQclear(res);
532+
533+
/*
534+
* If there are more connections than vacuumable relations, we don't need
535+
* to use them all.
536+
*/
537+
if (parallel)
538+
{
455539
if (concurrentCons>ntups)
456540
concurrentCons=ntups;
457541
if (concurrentCons <=1)
458542
parallel= false;
459-
PQclear(res);
460543
}
461544

462545
/*
@@ -493,10 +576,12 @@ vacuum_one_database(const char *dbname, vacuumingOptions *vacopts,
493576
stage_commands[stage],progname,echo);
494577
}
495578

496-
cell=tables ?tables->head :NULL;
579+
initPQExpBuffer(&sql);
580+
581+
cell=dbtables.head;
497582
do
498583
{
499-
constchar*tabname=cell ?cell->val :NULL;
584+
constchar*tabname=cell->val;
500585
ParallelSlot*free_slot;
501586

502587
if (CancelRequested)
@@ -529,12 +614,8 @@ vacuum_one_database(const char *dbname, vacuumingOptions *vacopts,
529614
else
530615
free_slot=slots;
531616

532-
/*
533-
* Prepare the vacuum command. Note that in some cases this requires
534-
* query execution, so be sure to use the free connection.
535-
*/
536-
prepare_vacuum_command(&sql,free_slot->connection,vacopts,tabname,
537-
tables==&dbtables,progname,echo);
617+
prepare_vacuum_command(&sql,PQserverVersion(free_slot->connection),
618+
vacopts,tabname);
538619

539620
/*
540621
* Execute the vacuum. If not in parallel mode, this terminates the
@@ -544,8 +625,7 @@ vacuum_one_database(const char *dbname, vacuumingOptions *vacopts,
544625
run_vacuum_command(free_slot->connection,sql.data,
545626
echo,tabname,progname,parallel);
546627

547-
if (cell)
548-
cell=cell->next;
628+
cell=cell->next;
549629
}while (cell!=NULL);
550630

551631
if (parallel)
@@ -653,14 +733,12 @@ vacuum_all_databases(vacuumingOptions *vacopts,
653733
* Construct a vacuum/analyze command to run based on the given options, in the
654734
* given string buffer, which may contain previous garbage.
655735
*
656-
*An optionaltable namecanbepassed; this must be already be properly
657-
*quoted. The command is semicolon-terminated.
736+
*Thetable nameused mustbealready properly quoted. The command generated
737+
*depends on the server version involved and it is semicolon-terminated.
658738
*/
659739
staticvoid
660-
prepare_vacuum_command(PQExpBuffersql,PGconn*conn,
661-
vacuumingOptions*vacopts,constchar*table,
662-
booltable_pre_qualified,
663-
constchar*progname,boolecho)
740+
prepare_vacuum_command(PQExpBuffersql,intserverVersion,
741+
vacuumingOptions*vacopts,constchar*table)
664742
{
665743
constchar*paren=" (";
666744
constchar*comma=", ";
@@ -673,12 +751,12 @@ prepare_vacuum_command(PQExpBuffer sql, PGconn *conn,
673751
appendPQExpBufferStr(sql,"ANALYZE");
674752

675753
/* parenthesized grammar of ANALYZE is supported since v11 */
676-
if (PQserverVersion(conn) >=110000)
754+
if (serverVersion >=110000)
677755
{
678756
if (vacopts->skip_locked)
679757
{
680758
/* SKIP_LOCKED is supported since v12 */
681-
Assert(PQserverVersion(conn) >=120000);
759+
Assert(serverVersion >=120000);
682760
appendPQExpBuffer(sql,"%sSKIP_LOCKED",sep);
683761
sep=comma;
684762
}
@@ -701,19 +779,19 @@ prepare_vacuum_command(PQExpBuffer sql, PGconn *conn,
701779
appendPQExpBufferStr(sql,"VACUUM");
702780

703781
/* parenthesized grammar of VACUUM is supported since v9.0 */
704-
if (PQserverVersion(conn) >=90000)
782+
if (serverVersion >=90000)
705783
{
706784
if (vacopts->disable_page_skipping)
707785
{
708786
/* DISABLE_PAGE_SKIPPING is supported since v9.6 */
709-
Assert(PQserverVersion(conn) >=90600);
787+
Assert(serverVersion >=90600);
710788
appendPQExpBuffer(sql,"%sDISABLE_PAGE_SKIPPING",sep);
711789
sep=comma;
712790
}
713791
if (vacopts->skip_locked)
714792
{
715793
/* SKIP_LOCKED is supported since v12 */
716-
Assert(PQserverVersion(conn) >=120000);
794+
Assert(serverVersion >=120000);
717795
appendPQExpBuffer(sql,"%sSKIP_LOCKED",sep);
718796
sep=comma;
719797
}
@@ -753,15 +831,7 @@ prepare_vacuum_command(PQExpBuffer sql, PGconn *conn,
753831
}
754832
}
755833

756-
if (table)
757-
{
758-
appendPQExpBufferChar(sql,' ');
759-
if (table_pre_qualified)
760-
appendPQExpBufferStr(sql,table);
761-
else
762-
appendQualifiedRelation(sql,table,conn,progname,echo);
763-
}
764-
appendPQExpBufferChar(sql,';');
834+
appendPQExpBuffer(sql," %s;",table);
765835
}
766836

767837
/*

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp