diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml index c582021d29..6f7860316c 100644 --- a/doc/src/sgml/ref/vacuum.sgml +++ b/doc/src/sgml/ref/vacuum.sgml @@ -366,7 +366,13 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ options & VACOPT_VACUUM) + if (params->options & VACOPT_VACUUM && !single_table) { PreventInTransactionBlock(isTopLevel, stmttype); in_outer_xact = false; @@ -401,9 +407,8 @@ vacuum(List *relations, VacuumParams *params, * Decide whether we need to start/commit our own transactions. * * For VACUUM (with or without ANALYZE): always do so, so that we can - * release locks as soon as possible. (We could possibly use the outer - * transaction for a one-table VACUUM, but handling TOAST tables would be - * problematic.) + * release locks as soon as possible, except for a single table VACUUM + * when it is executed inside a transaction block. * * For ANALYZE (no VACUUM): if inside a transaction block, we cannot * start/commit our own transactions. Also, there's no need to do so if @@ -412,7 +417,7 @@ vacuum(List *relations, VacuumParams *params, * transactions so we can release locks sooner. */ if (params->options & VACOPT_VACUUM) - use_own_xacts = true; + use_own_xacts = !in_outer_xact; else { Assert(params->options & VACOPT_ANALYZE); @@ -427,6 +432,7 @@ vacuum(List *relations, VacuumParams *params, } /* + * Tell vacuum_rel whether it will need to manage its own transaction. If so, * vacuum_rel expects to be entered with no transaction active; it will * start and commit its own transaction. But we are called by an SQL * command, and so we are executing inside a transaction already. We @@ -437,6 +443,9 @@ vacuum(List *relations, VacuumParams *params, if (use_own_xacts) { Assert(!in_outer_xact); + Assert(!IsInTransactionBlock(isTopLevel)); + + params->use_own_xact = true; /* ActiveSnapshot is not set by autovacuum */ if (ActiveSnapshotSet()) @@ -445,6 +454,8 @@ vacuum(List *relations, VacuumParams *params, /* matches the StartTransaction in PostgresMain() */ CommitTransactionCommand(); } + else + params->use_own_xact = false; /* Turn vacuum cost accounting on or off, and set/clear in_vacuum */ PG_TRY(); @@ -528,11 +539,13 @@ vacuum(List *relations, VacuumParams *params, StartTransactionCommand(); } - if ((params->options & VACOPT_VACUUM) && !IsAutoVacuumWorkerProcess()) + if ((params->options & VACOPT_VACUUM) && !IsAutoVacuumWorkerProcess() && use_own_xacts) { /* * Update pg_database.datfrozenxid, and truncate pg_xact if possible. - * (autovacuum.c does this for itself.) + * (autovacuum.c does this for itself.) unless we are in a transaction + * block, since this might take a while and we're not sure whether it + * is safe to allow this. */ vac_update_datfrozenxid(); } @@ -1837,9 +1850,10 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params) Assert(params != NULL); /* Begin a transaction for vacuuming this relation */ - StartTransactionCommand(); + if (params->use_own_xact) + StartTransactionCommand(); - if (!(params->options & VACOPT_FULL)) + if (!(params->options & VACOPT_FULL) && (params->use_own_xact)) { /* * In lazy vacuum, we can set the PROC_IN_VACUUM flag, which lets @@ -1852,6 +1866,11 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params) * contents of other tables is arguably broken, but we won't break it * here by violating transaction semantics.) * + * Don't set PROC_IN_VACUUM if running in a transaction block, since it + * would be very bad for other users to ignore our xact in that case. + * Note that setting the flag is an optional performance tweak, not + * required for correct operation of VACUUM. + * * We also set the VACUUM_FOR_WRAPAROUND flag, which is passed down by * autovacuum; it's used to avoid canceling a vacuum that was invoked * in an emergency. @@ -1876,7 +1895,8 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params) * cutoff xids in local memory wrapping around, and to have updated xmin * horizons. */ - PushActiveSnapshot(GetTransactionSnapshot()); + if (params->use_own_xact) + PushActiveSnapshot(GetTransactionSnapshot()); /* * Check for user-requested abort. Note we want this to be inside a @@ -1899,8 +1919,11 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params) /* leave if relation could not be opened or locked */ if (!rel) { - PopActiveSnapshot(); - CommitTransactionCommand(); + if (params->use_own_xact) + { + PopActiveSnapshot(); + CommitTransactionCommand(); + } return false; } @@ -1917,8 +1940,11 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params) params->options & VACOPT_VACUUM)) { relation_close(rel, lmode); - PopActiveSnapshot(); - CommitTransactionCommand(); + if (params->use_own_xact) + { + PopActiveSnapshot(); + CommitTransactionCommand(); + } return false; } @@ -1934,8 +1960,11 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params) (errmsg("skipping \"%s\" --- cannot vacuum non-tables or special system tables", RelationGetRelationName(rel)))); relation_close(rel, lmode); - PopActiveSnapshot(); - CommitTransactionCommand(); + if (params->use_own_xact) + { + PopActiveSnapshot(); + CommitTransactionCommand(); + } return false; } @@ -1949,8 +1978,11 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params) if (RELATION_IS_OTHER_TEMP(rel)) { relation_close(rel, lmode); - PopActiveSnapshot(); - CommitTransactionCommand(); + if (params->use_own_xact) + { + PopActiveSnapshot(); + CommitTransactionCommand(); + } return false; } @@ -1962,8 +1994,11 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params) if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE) { relation_close(rel, lmode); - PopActiveSnapshot(); - CommitTransactionCommand(); + if (params->use_own_xact) + { + PopActiveSnapshot(); + CommitTransactionCommand(); + } /* It's OK to proceed with ANALYZE on this table */ return true; } @@ -1977,9 +2012,13 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params) * NOTE: this cannot block, even if someone else is waiting for access, * because the lock manager knows that both lock requests are from the * same process. + * + * If we are in a transaction block, vacuum both main table and toast + * table within the existing transaction, so no session lock required. */ lockrelid = rel->rd_lockInfo.lockRelId; - LockRelationIdForSession(&lockrelid, lmode); + if (params->use_own_xact) + LockRelationIdForSession(&lockrelid, lmode); /* * Set index_cleanup option based on index_cleanup reloption if it wasn't @@ -2075,8 +2114,11 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params) /* * Complete the transaction and free all temporary memory used. */ - PopActiveSnapshot(); - CommitTransactionCommand(); + if (params->use_own_xact) + { + PopActiveSnapshot(); + CommitTransactionCommand(); + } /* * If the relation has a secondary toast rel, vacuum that too while we @@ -2091,7 +2133,8 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params) /* * Now release the session-level lock on the main table. */ - UnlockRelationIdForSession(&lockrelid, lmode); + if (params->use_own_xact) + UnlockRelationIdForSession(&lockrelid, lmode); /* Report that we really did it. */ return true; diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h index 5d816ba7f4..35310c588b 100644 --- a/src/include/commands/vacuum.h +++ b/src/include/commands/vacuum.h @@ -227,6 +227,8 @@ typedef struct VacuumParams VacOptValue index_cleanup; /* Do index vacuum and cleanup */ VacOptValue truncate; /* Truncate empty pages at the end */ + bool use_own_xact; /* Use own xact in vacuum_rel? */ + /* * The number of parallel vacuum workers. 0 by default which means choose * based on the number of indexes. -1 indicates parallel vacuum is diff --git a/src/test/regress/expected/vacuum.out b/src/test/regress/expected/vacuum.out index c63a157e5f..94cfe7755b 100644 --- a/src/test/regress/expected/vacuum.out +++ b/src/test/regress/expected/vacuum.out @@ -282,6 +282,16 @@ ALTER TABLE vactst ALTER COLUMN t SET STORAGE EXTERNAL; VACUUM (PROCESS_TOAST FALSE) vactst; VACUUM (PROCESS_TOAST FALSE, FULL) vactst; ERROR: PROCESS_TOAST required with VACUUM FULL +-- Single table inside transaction block +BEGIN; +VACUUM (PROCESS_TOAST FALSE) vactst; +COMMIT; +BEGIN; +VACUUM vactst; +COMMIT; +BEGIN; +VACUUM (ANALYZE) vactst; +COMMIT; DROP TABLE vaccluster; DROP TABLE vactst; DROP TABLE vacparted; diff --git a/src/test/regress/sql/vacuum.sql b/src/test/regress/sql/vacuum.sql index 9faa8a34a6..05dea783df 100644 --- a/src/test/regress/sql/vacuum.sql +++ b/src/test/regress/sql/vacuum.sql @@ -237,6 +237,17 @@ ALTER TABLE vactst ALTER COLUMN t SET STORAGE EXTERNAL; VACUUM (PROCESS_TOAST FALSE) vactst; VACUUM (PROCESS_TOAST FALSE, FULL) vactst; +-- Single table inside transaction block +BEGIN; +VACUUM (PROCESS_TOAST FALSE) vactst; +COMMIT; +BEGIN; +VACUUM vactst; +COMMIT; +BEGIN; +VACUUM (ANALYZE) vactst; +COMMIT; + DROP TABLE vaccluster; DROP TABLE vactst; DROP TABLE vacparted;