On Fri, May 01, 2026 at 04:41:12PM -0500, Nathan Bossart wrote: > On Tue, Jun 24, 2025 at 11:38:41AM -0500, Nathan Bossart wrote: >> Yeah, I'm tempted to even ask how folks feel about removing the toast.* >> reloptions. Maybe there's some simple cases that work well enough, but >> AFAICT any moderately-complicated setup basically doesn't work at all. In >> any case, writing out this patch set has got me on the fix-on-HEAD-only >> bandwagon. > > For the sake of discussion, I wrote a patch for this. I suggested removing > toast.* relopts to some colleagues recently, and nobody was aware of much > (if any) use in the field. So maybe this isn't totally > out-of-the-question...
Apparently I forgot to test it with injection points enabled. Here's a new patch. -- nathan
>From 6f1f1fd8158b2927793e82f207661092f1dfc65f Mon Sep 17 00:00:00 2001 From: Nathan Bossart <[email protected]> Date: Tue, 7 Apr 2026 15:30:53 -0500 Subject: [PATCH v3 1/1] remove toast reloptions --- doc/src/sgml/ref/create_table.sgml | 51 ++++++------- src/backend/access/common/reloptions.c | 49 +++++------- src/backend/catalog/toasting.c | 31 ++++---- src/backend/commands/createas.c | 13 +--- src/backend/commands/repack.c | 15 +--- src/backend/commands/tablecmds.c | 19 +---- src/backend/commands/vacuum.c | 14 +--- src/backend/postmaster/autovacuum.c | 48 +++++------- src/backend/tcop/utility.c | 19 +---- src/bin/pg_upgrade/check.c | 75 +++++++++++++++++++ src/include/access/reloptions.h | 24 +++--- src/include/catalog/toasting.h | 6 +- .../injection_points/expected/vacuum.out | 24 +++--- .../modules/injection_points/sql/vacuum.sql | 20 ++--- src/test/regress/expected/alter_table.out | 9 --- src/test/regress/expected/reloptions.out | 56 ++------------ src/test/regress/expected/vacuum.out | 8 -- src/test/regress/sql/alter_table.sql | 4 - src/test/regress/sql/reloptions.sql | 32 +------- src/test/regress/sql/vacuum.sql | 8 -- 20 files changed, 199 insertions(+), 326 deletions(-) diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index e342585c7f0..b8d2a657d10 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -1589,14 +1589,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM Storage parameters for indexes are documented in <xref linkend="sql-createindex"/>. The storage parameters currently - available for tables are listed below. For many of these parameters, as - shown, there is an additional parameter with the same name prefixed with - <literal>toast.</literal>, which controls the behavior of the + available for tables are listed below. Unless otherwise noted, a table's + parameter value also controls the behavior of the table's secondary <acronym>TOAST</acronym> table, if any (see <xref linkend="storage-toast"/> for more information about TOAST). - If a table parameter value is set and the - equivalent <literal>toast.</literal> parameter is not, the TOAST table - will use the table's parameter value. Specifying these parameters for partitioned tables is not supported, but you may specify them for individual leaf partitions. </para> @@ -1622,7 +1618,8 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM updates</link> more likely. For a table whose entries are never updated, complete packing is the best choice, but in heavily updated tables smaller fillfactors are - appropriate. This parameter cannot be set for TOAST tables. + appropriate. This parameter does not affect the table's secondary TOAST + table. </para> </listitem> </varlistentry> @@ -1648,7 +1645,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM Note that the default setting is often close to optimal, and it is possible that setting this parameter could have negative effects in some cases. - This parameter cannot be set for TOAST tables. + This parameter does not affect the table's secondary TOAST table. </para> </listitem> </varlistentry> @@ -1671,7 +1668,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM </varlistentry> <varlistentry id="reloption-autovacuum-enabled" xreflabel="autovacuum_enabled"> - <term><literal>autovacuum_enabled</literal>, <literal>toast.autovacuum_enabled</literal> (<type>boolean</type>) + <term><literal>autovacuum_enabled</literal> (<type>boolean</type>) <indexterm> <primary><varname>autovacuum_enabled</varname> storage parameter</primary> </indexterm> @@ -1696,7 +1693,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM </varlistentry> <varlistentry id="reloption-vacuum-index-cleanup" xreflabel="vacuum_index_cleanup"> - <term><literal>vacuum_index_cleanup</literal>, <literal>toast.vacuum_index_cleanup</literal> (<type>enum</type>) + <term><literal>vacuum_index_cleanup</literal> (<type>enum</type>) <indexterm> <primary><varname>vacuum_index_cleanup</varname> storage parameter</primary> </indexterm> @@ -1722,7 +1719,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM </varlistentry> <varlistentry id="reloption-vacuum-truncate" xreflabel="vacuum_truncate"> - <term><literal>vacuum_truncate</literal>, <literal>toast.vacuum_truncate</literal> (<type>boolean</type>) + <term><literal>vacuum_truncate</literal> (<type>boolean</type>) <indexterm> <primary><varname>vacuum_truncate</varname></primary> <secondary>storage parameter</secondary> @@ -1755,7 +1752,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM </varlistentry> <varlistentry id="reloption-autovacuum-vacuum-threshold" xreflabel="autovacuum_vacuum_threshold"> - <term><literal>autovacuum_vacuum_threshold</literal>, <literal>toast.autovacuum_vacuum_threshold</literal> (<type>integer</type>) + <term><literal>autovacuum_vacuum_threshold</literal> (<type>integer</type>) <indexterm> <primary><varname>autovacuum_vacuum_threshold</varname></primary> <secondary>storage parameter</secondary> @@ -1770,7 +1767,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM </varlistentry> <varlistentry id="reloption-autovacuum-vacuum-max-threshold" xreflabel="autovacuum_vacuum_max_threshold"> - <term><literal>autovacuum_vacuum_max_threshold</literal>, <literal>toast.autovacuum_vacuum_max_threshold</literal> (<type>integer</type>) + <term><literal>autovacuum_vacuum_max_threshold</literal> (<type>integer</type>) <indexterm> <primary><varname>autovacuum_vacuum_max_threshold</varname></primary> <secondary>storage parameter</secondary> @@ -1785,7 +1782,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM </varlistentry> <varlistentry id="reloption-autovacuum-vacuum-scale-factor" xreflabel="autovacuum_vacuum_scale_factor"> - <term><literal>autovacuum_vacuum_scale_factor</literal>, <literal>toast.autovacuum_vacuum_scale_factor</literal> (<type>floating point</type>) + <term><literal>autovacuum_vacuum_scale_factor</literal> (<type>floating point</type>) <indexterm> <primary><varname>autovacuum_vacuum_scale_factor</varname> </primary> <secondary>storage parameter</secondary> @@ -1800,7 +1797,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM </varlistentry> <varlistentry id="reloption-autovacuum-vacuum-insert-threshold" xreflabel="autovacuum_vacuum_insert_threshold"> - <term><literal>autovacuum_vacuum_insert_threshold</literal>, <literal>toast.autovacuum_vacuum_insert_threshold</literal> (<type>integer</type>) + <term><literal>autovacuum_vacuum_insert_threshold</literal> (<type>integer</type>) <indexterm> <primary><varname>autovacuum_vacuum_insert_threshold</varname></primary> <secondary>storage parameter</secondary> @@ -1815,7 +1812,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM </varlistentry> <varlistentry id="reloption-autovacuum-vacuum-insert-scale-factor" xreflabel="autovacuum_vacuum_insert_scale_factor"> - <term><literal>autovacuum_vacuum_insert_scale_factor</literal>, <literal>toast.autovacuum_vacuum_insert_scale_factor</literal> (<type>floating point</type>) + <term><literal>autovacuum_vacuum_insert_scale_factor</literal> (<type>floating point</type>) <indexterm> <primary><varname>autovacuum_vacuum_insert_scale_factor</varname> </primary> <secondary>storage parameter</secondary> @@ -1860,7 +1857,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM </varlistentry> <varlistentry id="reloption-autovacuum-vacuum-cost-delay" xreflabel="autovacuum_vacuum_cost_delay"> - <term><literal>autovacuum_vacuum_cost_delay</literal>, <literal>toast.autovacuum_vacuum_cost_delay</literal> (<type>floating point</type>) + <term><literal>autovacuum_vacuum_cost_delay</literal> (<type>floating point</type>) <indexterm> <primary><varname>autovacuum_vacuum_cost_delay</varname></primary> <secondary>storage parameter</secondary> @@ -1875,7 +1872,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM </varlistentry> <varlistentry id="reloption-autovacuum-vacuum-cost-limit" xreflabel="autovacuum_vacuum_cost_limit"> - <term><literal>autovacuum_vacuum_cost_limit</literal>, <literal>toast.autovacuum_vacuum_cost_limit</literal> (<type>integer</type>) + <term><literal>autovacuum_vacuum_cost_limit</literal> (<type>integer</type>) <indexterm> <primary><varname>autovacuum_vacuum_cost_limit</varname></primary> <secondary>storage parameter</secondary> @@ -1890,7 +1887,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM </varlistentry> <varlistentry id="reloption-autovacuum-freeze-min-age" xreflabel="autovacuum_freeze_min_age"> - <term><literal>autovacuum_freeze_min_age</literal>, <literal>toast.autovacuum_freeze_min_age</literal> (<type>integer</type>) + <term><literal>autovacuum_freeze_min_age</literal> (<type>integer</type>) <indexterm> <primary><varname>autovacuum_freeze_min_age</varname> storage parameter</primary> </indexterm> @@ -1907,7 +1904,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM </varlistentry> <varlistentry id="reloption-autovacuum-freeze-max-age" xreflabel="autovacuum_freeze_max_age"> - <term><literal>autovacuum_freeze_max_age</literal>, <literal>toast.autovacuum_freeze_max_age</literal> (<type>integer</type>) + <term><literal>autovacuum_freeze_max_age</literal> (<type>integer</type>) <indexterm> <primary><varname>autovacuum_freeze_max_age</varname></primary> <secondary>storage parameter</secondary> @@ -1924,7 +1921,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM </varlistentry> <varlistentry id="reloption-autovacuum-freeze-table-age" xreflabel="autovacuum_freeze_table_age"> - <term><literal>autovacuum_freeze_table_age</literal>, <literal>toast.autovacuum_freeze_table_age</literal> (<type>integer</type>) + <term><literal>autovacuum_freeze_table_age</literal> (<type>integer</type>) <indexterm> <primary><varname>autovacuum_freeze_table_age</varname> storage parameter</primary> </indexterm> @@ -1938,7 +1935,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM </varlistentry> <varlistentry id="reloption-autovacuum-multixact-freeze-min-age" xreflabel="autovacuum_multixact_freeze_min_age"> - <term><literal>autovacuum_multixact_freeze_min_age</literal>, <literal>toast.autovacuum_multixact_freeze_min_age</literal> (<type>integer</type>) + <term><literal>autovacuum_multixact_freeze_min_age</literal> (<type>integer</type>) <indexterm> <primary><varname>autovacuum_multixact_freeze_min_age</varname> storage parameter</primary> </indexterm> @@ -1956,7 +1953,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM </varlistentry> <varlistentry id="reloption-autovacuum-multixact-freeze-max-age" xreflabel="autovacuum_multixact_freeze_max_age"> - <term><literal>autovacuum_multixact_freeze_max_age</literal>, <literal>toast.autovacuum_multixact_freeze_max_age</literal> (<type>integer</type>) + <term><literal>autovacuum_multixact_freeze_max_age</literal> (<type>integer</type>) <indexterm> <primary><varname>autovacuum_multixact_freeze_max_age</varname></primary> <secondary>storage parameter</secondary> @@ -1975,7 +1972,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM </varlistentry> <varlistentry id="reloption-autovacuum-multixact-freeze-table-age" xreflabel="autovacuum_multixact_freeze_table_age"> - <term><literal>autovacuum_multixact_freeze_table_age</literal>, <literal>toast.autovacuum_multixact_freeze_table_age</literal> (<type>integer</type>) + <term><literal>autovacuum_multixact_freeze_table_age</literal> (<type>integer</type>) <indexterm> <primary><varname>autovacuum_multixact_freeze_table_age</varname> storage parameter</primary> </indexterm> @@ -1989,7 +1986,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM </varlistentry> <varlistentry id="reloption-log-autovacuum-min-duration" xreflabel="log_autovacuum_min_duration"> - <term><literal>log_autovacuum_min_duration</literal>, <literal>toast.log_autovacuum_min_duration</literal> (<type>integer</type>) + <term><literal>log_autovacuum_min_duration</literal> (<type>integer</type>) <indexterm> <primary><varname>log_autovacuum_min_duration</varname></primary> <secondary>storage parameter</secondary> @@ -2019,7 +2016,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM </varlistentry> <varlistentry id="reloption-vacuum-max-eager-freeze-failure-rate" xreflabel="vacuum_max_eager_freeze_failure_rate"> - <term><literal>vacuum_max_eager_freeze_failure_rate</literal>, <literal>toast.vacuum_max_eager_freeze_failure_rate</literal> (<type>floating point</type>) + <term><literal>vacuum_max_eager_freeze_failure_rate</literal> (<type>floating point</type>) <indexterm> <primary><varname>vacuum_max_eager_freeze_failure_rate</varname></primary> <secondary>storage parameter</secondary> @@ -2044,7 +2041,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM Declare the table as an additional catalog table for purposes of logical replication. See <xref linkend="logicaldecoding-capabilities"/> for details. - This parameter cannot be set for TOAST tables. + This parameter does not affect the table's secondary TOAST table. </para> </listitem> </varlistentry> diff --git a/src/backend/access/common/reloptions.c b/src/backend/access/common/reloptions.c index 3e832c3797e..50d9805f3d8 100644 --- a/src/backend/access/common/reloptions.c +++ b/src/backend/access/common/reloptions.c @@ -111,7 +111,7 @@ static relopt_bool boolRelOpts[] = { "autovacuum_enabled", "Enables autovacuum in this relation", - RELOPT_KIND_HEAP | RELOPT_KIND_TOAST, + RELOPT_KIND_HEAP, ShareUpdateExclusiveLock }, true @@ -172,7 +172,7 @@ static relopt_ternary ternaryRelOpts[] = { "vacuum_truncate", "Enables vacuum to truncate empty pages at the end of this table", - RELOPT_KIND_HEAP | RELOPT_KIND_TOAST, + RELOPT_KIND_HEAP, ShareUpdateExclusiveLock } }, @@ -249,7 +249,7 @@ static relopt_int intRelOpts[] = { "autovacuum_vacuum_threshold", "Minimum number of tuple updates or deletes prior to vacuum", - RELOPT_KIND_HEAP | RELOPT_KIND_TOAST, + RELOPT_KIND_HEAP, ShareUpdateExclusiveLock }, -1, 0, INT_MAX @@ -258,7 +258,7 @@ static relopt_int intRelOpts[] = { "autovacuum_vacuum_max_threshold", "Maximum number of tuple updates or deletes prior to vacuum", - RELOPT_KIND_HEAP | RELOPT_KIND_TOAST, + RELOPT_KIND_HEAP, ShareUpdateExclusiveLock }, -2, -1, INT_MAX @@ -267,7 +267,7 @@ static relopt_int intRelOpts[] = { "autovacuum_vacuum_insert_threshold", "Minimum number of tuple inserts prior to vacuum, or -1 to disable insert vacuums", - RELOPT_KIND_HEAP | RELOPT_KIND_TOAST, + RELOPT_KIND_HEAP, ShareUpdateExclusiveLock }, -2, -1, INT_MAX @@ -285,7 +285,7 @@ static relopt_int intRelOpts[] = { "autovacuum_vacuum_cost_limit", "Vacuum cost amount available before napping, for autovacuum", - RELOPT_KIND_HEAP | RELOPT_KIND_TOAST, + RELOPT_KIND_HEAP, ShareUpdateExclusiveLock }, -1, 1, 10000 @@ -294,7 +294,7 @@ static relopt_int intRelOpts[] = { "autovacuum_freeze_min_age", "Minimum age at which VACUUM should freeze a table row, for autovacuum", - RELOPT_KIND_HEAP | RELOPT_KIND_TOAST, + RELOPT_KIND_HEAP, ShareUpdateExclusiveLock }, -1, 0, 1000000000 @@ -303,7 +303,7 @@ static relopt_int intRelOpts[] = { "autovacuum_multixact_freeze_min_age", "Minimum multixact age at which VACUUM should freeze a row multixact's, for autovacuum", - RELOPT_KIND_HEAP | RELOPT_KIND_TOAST, + RELOPT_KIND_HEAP, ShareUpdateExclusiveLock }, -1, 0, 1000000000 @@ -312,7 +312,7 @@ static relopt_int intRelOpts[] = { "autovacuum_freeze_max_age", "Age at which to autovacuum a table to prevent transaction ID wraparound", - RELOPT_KIND_HEAP | RELOPT_KIND_TOAST, + RELOPT_KIND_HEAP, ShareUpdateExclusiveLock }, -1, 100000, 2000000000 @@ -321,7 +321,7 @@ static relopt_int intRelOpts[] = { "autovacuum_multixact_freeze_max_age", "Multixact age at which to autovacuum a table to prevent multixact wraparound", - RELOPT_KIND_HEAP | RELOPT_KIND_TOAST, + RELOPT_KIND_HEAP, ShareUpdateExclusiveLock }, -1, 10000, 2000000000 @@ -330,7 +330,7 @@ static relopt_int intRelOpts[] = { "autovacuum_freeze_table_age", "Age at which VACUUM should perform a full table sweep to freeze row versions", - RELOPT_KIND_HEAP | RELOPT_KIND_TOAST, + RELOPT_KIND_HEAP, ShareUpdateExclusiveLock }, -1, 0, 2000000000 }, @@ -338,7 +338,7 @@ static relopt_int intRelOpts[] = { "autovacuum_multixact_freeze_table_age", "Age of multixact at which VACUUM should perform a full table sweep to freeze row versions", - RELOPT_KIND_HEAP | RELOPT_KIND_TOAST, + RELOPT_KIND_HEAP, ShareUpdateExclusiveLock }, -1, 0, 2000000000 }, @@ -346,7 +346,7 @@ static relopt_int intRelOpts[] = { "log_autovacuum_min_duration", "Sets the minimum execution time above which vacuum actions by autovacuum will be logged", - RELOPT_KIND_HEAP | RELOPT_KIND_TOAST, + RELOPT_KIND_HEAP, ShareUpdateExclusiveLock }, -1, -1, INT_MAX @@ -424,7 +424,7 @@ static relopt_real realRelOpts[] = { "autovacuum_vacuum_cost_delay", "Vacuum cost delay in milliseconds, for autovacuum", - RELOPT_KIND_HEAP | RELOPT_KIND_TOAST, + RELOPT_KIND_HEAP, ShareUpdateExclusiveLock }, -1, 0.0, 100.0 @@ -433,7 +433,7 @@ static relopt_real realRelOpts[] = { "autovacuum_vacuum_scale_factor", "Number of tuple updates or deletes prior to vacuum as a fraction of reltuples", - RELOPT_KIND_HEAP | RELOPT_KIND_TOAST, + RELOPT_KIND_HEAP, ShareUpdateExclusiveLock }, -1, 0.0, 100.0 @@ -442,7 +442,7 @@ static relopt_real realRelOpts[] = { "autovacuum_vacuum_insert_scale_factor", "Number of tuple inserts prior to vacuum as a fraction of reltuples", - RELOPT_KIND_HEAP | RELOPT_KIND_TOAST, + RELOPT_KIND_HEAP, ShareUpdateExclusiveLock }, -1, 0.0, 100.0 @@ -460,7 +460,7 @@ static relopt_real realRelOpts[] = { "vacuum_max_eager_freeze_failure_rate", "Fraction of pages in a relation vacuum can scan and fail to freeze before disabling eager scanning.", - RELOPT_KIND_HEAP | RELOPT_KIND_TOAST, + RELOPT_KIND_HEAP, ShareUpdateExclusiveLock }, -1, 0.0, 1.0 @@ -554,7 +554,7 @@ static relopt_enum enumRelOpts[] = { "vacuum_index_cleanup", "Controls index vacuuming and index cleanup", - RELOPT_KIND_HEAP | RELOPT_KIND_TOAST, + RELOPT_KIND_HEAP, ShareUpdateExclusiveLock }, StdRdOptIndexCleanupValues, @@ -2163,21 +2163,8 @@ view_reloptions(Datum reloptions, bool validate) bytea * heap_reloptions(char relkind, Datum reloptions, bool validate) { - StdRdOptions *rdopts; - switch (relkind) { - case RELKIND_TOASTVALUE: - rdopts = (StdRdOptions *) - default_reloptions(reloptions, validate, RELOPT_KIND_TOAST); - if (rdopts != NULL) - { - /* adjust default-only parameters for TOAST relations */ - rdopts->fillfactor = 100; - rdopts->autovacuum.analyze_threshold = -1; - rdopts->autovacuum.analyze_scale_factor = -1; - } - return (bytea *) rdopts; case RELKIND_RELATION: case RELKIND_MATVIEW: return default_reloptions(reloptions, validate, RELOPT_KIND_HEAP); diff --git a/src/backend/catalog/toasting.c b/src/backend/catalog/toasting.c index 4aa52a4bd25..9f1f822702c 100644 --- a/src/backend/catalog/toasting.c +++ b/src/backend/catalog/toasting.c @@ -34,11 +34,11 @@ #include "utils/rel.h" #include "utils/syscache.h" -static void CheckAndCreateToastTable(Oid relOid, Datum reloptions, +static void CheckAndCreateToastTable(Oid relOid, LOCKMODE lockmode, bool check, Oid OIDOldToast); static bool create_toast_table(Relation rel, Oid toastOid, Oid toastIndexOid, - Datum reloptions, LOCKMODE lockmode, bool check, + LOCKMODE lockmode, bool check, Oid OIDOldToast); static bool needs_toast_table(Relation rel); @@ -48,35 +48,30 @@ static bool needs_toast_table(Relation rel); * If the table needs a toast table, and doesn't already have one, * then create a toast table for it. * - * reloptions for the toast table can be passed, too. Pass (Datum) 0 - * for default reloptions. - * * We expect the caller to have verified that the relation is a table and have * already done any necessary permission checks. Callers expect this function * to end with CommandCounterIncrement if it makes any changes. */ void -AlterTableCreateToastTable(Oid relOid, Datum reloptions, LOCKMODE lockmode) +AlterTableCreateToastTable(Oid relOid, LOCKMODE lockmode) { - CheckAndCreateToastTable(relOid, reloptions, lockmode, true, InvalidOid); + CheckAndCreateToastTable(relOid, lockmode, true, InvalidOid); } void -NewHeapCreateToastTable(Oid relOid, Datum reloptions, LOCKMODE lockmode, - Oid OIDOldToast) +NewHeapCreateToastTable(Oid relOid, LOCKMODE lockmode, Oid OIDOldToast) { - CheckAndCreateToastTable(relOid, reloptions, lockmode, false, OIDOldToast); + CheckAndCreateToastTable(relOid, lockmode, false, OIDOldToast); } void -NewRelationCreateToastTable(Oid relOid, Datum reloptions) +NewRelationCreateToastTable(Oid relOid) { - CheckAndCreateToastTable(relOid, reloptions, AccessExclusiveLock, false, - InvalidOid); + CheckAndCreateToastTable(relOid, AccessExclusiveLock, false, InvalidOid); } static void -CheckAndCreateToastTable(Oid relOid, Datum reloptions, LOCKMODE lockmode, +CheckAndCreateToastTable(Oid relOid, LOCKMODE lockmode, bool check, Oid OIDOldToast) { Relation rel; @@ -84,7 +79,7 @@ CheckAndCreateToastTable(Oid relOid, Datum reloptions, LOCKMODE lockmode, rel = table_open(relOid, lockmode); /* create_toast_table does all the work */ - (void) create_toast_table(rel, InvalidOid, InvalidOid, reloptions, lockmode, + (void) create_toast_table(rel, InvalidOid, InvalidOid, lockmode, check, OIDOldToast); table_close(rel, NoLock); @@ -108,7 +103,7 @@ BootstrapToastTable(char *relName, Oid toastOid, Oid toastIndexOid) relName); /* create_toast_table does all the work */ - if (!create_toast_table(rel, toastOid, toastIndexOid, (Datum) 0, + if (!create_toast_table(rel, toastOid, toastIndexOid, AccessExclusiveLock, false, InvalidOid)) elog(ERROR, "\"%s\" does not require a toast table", relName); @@ -126,7 +121,7 @@ BootstrapToastTable(char *relName, Oid toastOid, Oid toastIndexOid) */ static bool create_toast_table(Relation rel, Oid toastOid, Oid toastIndexOid, - Datum reloptions, LOCKMODE lockmode, bool check, + LOCKMODE lockmode, bool check, Oid OIDOldToast) { Oid relOid = RelationGetRelid(rel); @@ -266,7 +261,7 @@ create_toast_table(Relation rel, Oid toastOid, Oid toastIndexOid, shared_relation, mapped_relation, ONCOMMIT_NOOP, - reloptions, + (Datum) 0, false, true, true, diff --git a/src/backend/commands/createas.c b/src/backend/commands/createas.c index 6dbb831ca89..188e167999b 100644 --- a/src/backend/commands/createas.c +++ b/src/backend/commands/createas.c @@ -84,8 +84,6 @@ create_ctas_internal(List *attrList, IntoClause *into) CreateStmt *create = makeNode(CreateStmt); bool is_matview; char relkind; - Datum toast_options; - const char *const validnsps[] = HEAP_RELOPT_NAMESPACES; ObjectAddress intoRelationAddr; /* This code supports both CREATE TABLE AS and CREATE MATERIALIZED VIEW */ @@ -120,16 +118,7 @@ create_ctas_internal(List *attrList, IntoClause *into) */ CommandCounterIncrement(); - /* parse and validate reloptions for the toast table */ - toast_options = transformRelOptions((Datum) 0, - create->options, - "toast", - validnsps, - true, false); - - (void) heap_reloptions(RELKIND_TOASTVALUE, toast_options, true); - - NewRelationCreateToastTable(intoRelationAddr.objectId, toast_options); + NewRelationCreateToastTable(intoRelationAddr.objectId); /* Create the "view" part of a materialized view. */ if (is_matview) diff --git a/src/backend/commands/repack.c b/src/backend/commands/repack.c index 9d162957bc3..5064d8e2349 100644 --- a/src/backend/commands/repack.c +++ b/src/backend/commands/repack.c @@ -1201,20 +1201,7 @@ make_new_heap(Oid OIDOldHeap, Oid NewTableSpace, Oid NewAccessMethod, */ toastid = OldHeap->rd_rel->reltoastrelid; if (OidIsValid(toastid)) - { - /* keep the existing toast table's reloptions, if any */ - tuple = SearchSysCache1(RELOID, ObjectIdGetDatum(toastid)); - if (!HeapTupleIsValid(tuple)) - elog(ERROR, "cache lookup failed for relation %u", toastid); - reloptions = SysCacheGetAttr(RELOID, tuple, Anum_pg_class_reloptions, - &isNull); - if (isNull) - reloptions = (Datum) 0; - - NewHeapCreateToastTable(OIDNewHeap, reloptions, lockmode, toastid); - - ReleaseSysCache(tuple); - } + NewHeapCreateToastTable(OIDNewHeap, lockmode, toastid); table_close(OldHeap, NoLock); diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index d8d7969bf30..a254509cbb5 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -827,7 +827,6 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId, ListCell *listptr; AttrNumber attnum; bool partitioned; - const char *const validnsps[] = HEAP_RELOPT_NAMESPACES; Oid ofTypeId; ObjectAddress address; LOCKMODE parentLockmode; @@ -976,7 +975,7 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId, /* * Parse and validate reloptions, if any. */ - reloptions = transformRelOptions((Datum) 0, stmt->options, NULL, validnsps, + reloptions = transformRelOptions((Datum) 0, stmt->options, NULL, NULL, true, false); switch (relkind) @@ -5433,7 +5432,7 @@ ATRewriteCatalogs(List **wqueue, LOCKMODE lockmode, tab->relkind == RELKIND_PARTITIONED_TABLE) && tab->partition_constraint == NULL) || tab->relkind == RELKIND_MATVIEW) - AlterTableCreateToastTable(tab->relid, (Datum) 0, lockmode); + AlterTableCreateToastTable(tab->relid, lockmode); } } @@ -16927,7 +16926,6 @@ ATExecSetRelOptions(Relation rel, List *defList, AlterTableType operation, Datum repl_val[Natts_pg_class]; bool repl_null[Natts_pg_class]; bool repl_repl[Natts_pg_class]; - const char *const validnsps[] = HEAP_RELOPT_NAMESPACES; if (defList == NIL && operation != AT_ReplaceRelOptions) return; /* nothing to do */ @@ -16960,7 +16958,7 @@ ATExecSetRelOptions(Relation rel, List *defList, AlterTableType operation, } /* Generate new proposed reloptions (text array) */ - newOptions = transformRelOptions(datum, defList, NULL, validnsps, false, + newOptions = transformRelOptions(datum, defList, NULL, NULL, false, operation == AT_ResetRelOptions); /* Validate */ @@ -17083,20 +17081,11 @@ ATExecSetRelOptions(Relation rel, List *defList, AlterTableType operation, datum = (Datum) 0; } - newOptions = transformRelOptions(datum, defList, "toast", validnsps, - false, operation == AT_ResetRelOptions); - - (void) heap_reloptions(RELKIND_TOASTVALUE, newOptions, true); - memset(repl_val, 0, sizeof(repl_val)); memset(repl_null, false, sizeof(repl_null)); memset(repl_repl, false, sizeof(repl_repl)); - if (newOptions != (Datum) 0) - repl_val[Anum_pg_class_reloptions - 1] = newOptions; - else - repl_null[Anum_pg_class_reloptions - 1] = true; - + repl_null[Anum_pg_class_reloptions - 1] = true; repl_repl[Anum_pg_class_reloptions - 1] = true; newtuple = heap_modify_tuple(tuple, RelationGetDescr(pgclass), diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c index 99d0db82ed7..ed670b3e3f1 100644 --- a/src/backend/commands/vacuum.c +++ b/src/backend/commands/vacuum.c @@ -2015,13 +2015,6 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams params, Oid save_userid; int save_sec_context; int save_nestlevel; - VacuumParams toast_vacuum_params; - - /* - * This function scribbles on the parameters, so make a copy early to - * avoid affecting the TOAST table (if we do end up recursing to it). - */ - memcpy(&toast_vacuum_params, ¶ms, sizeof(VacuumParams)); /* Begin a transaction for vacuuming this relation */ StartTransactionCommand(); @@ -2336,11 +2329,10 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams params, * relation. NB: This is only safe to do because we hold a session * lock on the main relation that prevents concurrent deletion. */ - toast_vacuum_params.options |= VACOPT_PROCESS_MAIN; - toast_vacuum_params.toast_parent = relid; + params.options |= VACOPT_PROCESS_MAIN; + params.toast_parent = relid; - vacuum_rel(toast_relid, NULL, toast_vacuum_params, bstrategy, - isTopLevel); + vacuum_rel(toast_relid, NULL, params, bstrategy, isTopLevel); } /* diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c index a5a8db2ff88..c0e7cbf2cb8 100644 --- a/src/backend/postmaster/autovacuum.c +++ b/src/backend/postmaster/autovacuum.c @@ -2139,8 +2139,9 @@ do_autovacuum(void) { Form_pg_class classForm = (Form_pg_class) GETSTRUCT(tuple); Oid relid; - AutoVacOpts *relopts; - bool free_relopts = false; + av_relation *hentry; + bool found; + AutoVacOpts *relopts = NULL; bool dovacuum; bool doanalyze; bool wraparound; @@ -2154,22 +2155,10 @@ do_autovacuum(void) relid = classForm->oid; - /* - * fetch reloptions -- if this toast table does not have them, try the - * main rel - */ - relopts = extract_autovac_opts(tuple, pg_class_desc); - if (relopts) - free_relopts = true; - else - { - av_relation *hentry; - bool found; - - hentry = hash_search(table_toast_map, &relid, HASH_FIND, &found); - if (found && hentry->ar_hasrelopts) - relopts = &hentry->ar_reloptions; - } + /* Use reloptions from main rel. */ + hentry = hash_search(table_toast_map, &relid, HASH_FIND, &found); + if (found && hentry->ar_hasrelopts) + relopts = &hentry->ar_reloptions; relation_needs_vacanalyze(relid, relopts, classForm, effective_multixact_freeze_max_age, @@ -2186,10 +2175,6 @@ do_autovacuum(void) table->score = scores.max; tables_to_process = lappend(tables_to_process, table); } - - /* Release stuff to avoid leakage */ - if (free_relopts) - pfree(relopts); } table_endscan(relScan); @@ -2826,7 +2811,7 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map, bool doanalyze; autovac_table *tab = NULL; bool wraparound; - AutoVacOpts *avopts; + AutoVacOpts *avopts = NULL; bool free_avopts = false; AutoVacuumScores scores; @@ -2838,13 +2823,15 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map, /* * Get the applicable reloptions. If it is a TOAST table, try to get the - * main table reloptions if the toast table itself doesn't have. + * main table reloptions. */ - avopts = extract_autovac_opts(classTup, pg_class_desc); - if (avopts) - free_avopts = true; - else if (classForm->relkind == RELKIND_TOASTVALUE && - table_toast_map != NULL) + if (classForm->relkind != RELKIND_TOASTVALUE) + { + avopts = extract_autovac_opts(classTup, pg_class_desc); + if (avopts) + free_avopts = true; + } + else if (table_toast_map) { av_relation *hentry; bool found; @@ -3127,8 +3114,7 @@ relation_needs_vacanalyze(Oid relid, /* * Determine vacuum/analyze equation parameters. We have two possible - * sources: the passed reloptions (which could be a main table or a toast - * table), or the autovacuum GUC variables. + * sources: the passed reloptions or the autovacuum GUC variables. */ /* -1 in autovac setting means use plain vacuum_scale_factor */ diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c index 73a56f1df1d..d089e02c216 100644 --- a/src/backend/tcop/utility.c +++ b/src/backend/tcop/utility.c @@ -1159,8 +1159,6 @@ ProcessUtilitySlow(ParseState *pstate, if (IsA(stmt, CreateStmt)) { CreateStmt *cstmt = (CreateStmt *) stmt; - Datum toast_options; - const char *const validnsps[] = HEAP_RELOPT_NAMESPACES; /* Remember transformed RangeVar for LIKE */ table_rv = cstmt->relation; @@ -1180,22 +1178,7 @@ ProcessUtilitySlow(ParseState *pstate, */ CommandCounterIncrement(); - /* - * parse and validate reloptions for the toast - * table - */ - toast_options = transformRelOptions((Datum) 0, - cstmt->options, - "toast", - validnsps, - true, - false); - (void) heap_reloptions(RELKIND_TOASTVALUE, - toast_options, - true); - - NewRelationCreateToastTable(address.objectId, - toast_options); + NewRelationCreateToastTable(address.objectId); } else if (IsA(stmt, CreateForeignTableStmt)) { diff --git a/src/bin/pg_upgrade/check.c b/src/bin/pg_upgrade/check.c index 5a7afe62eab..f0d15f56525 100644 --- a/src/bin/pg_upgrade/check.c +++ b/src/bin/pg_upgrade/check.c @@ -37,6 +37,7 @@ static void check_new_cluster_subscription_configuration(void); static void check_old_cluster_for_valid_slots(void); static void check_old_cluster_subscription_state(void); static void check_old_cluster_global_names(ClusterInfo *cluster); +static void check_for_toast_reloptions(ClusterInfo *cluster); /* * DataTypesUsageChecks - definitions of data type checks for the old cluster @@ -634,6 +635,9 @@ check_and_dump_old_cluster(void) check_for_prepared_transactions(&old_cluster); check_for_isn_and_int8_passing_mismatch(&old_cluster); + if (GET_MAJOR_VERSION(old_cluster.major_version) < 2000) + check_for_toast_reloptions(&old_cluster); + if (GET_MAJOR_VERSION(old_cluster.major_version) >= 1700) { /* @@ -2644,3 +2648,74 @@ check_old_cluster_global_names(ClusterInfo *cluster) else check_ok(); } + +/* + * Callback function for processing results of query for + * check_for_toast_reloptions()'s UpgradeTask. If the query returned any rows + * (i.e., the check failed), write the details to the report file. + */ +static void +process_toast_relopts_check(DbInfo *dbinfo, PGresult *res, void *arg) +{ + UpgradeTaskReport *report = (UpgradeTaskReport *) arg; + int ntups = PQntuples(res); + int i_nspname = PQfnumber(res, "nspname"); + int i_relname = PQfnumber(res, "relname"); + + if (ntups == 0) + return; + + if (report->file == NULL && + (report->file = fopen_priv(report->path, "w")) == NULL) + pg_fatal("could not open file \"%s\": %m", report->path); + + fprintf(report->file, "In database: %s\n", dbinfo->db_name); + + for (int rowno = 0; rowno < ntups; rowno++) + fprintf(report->file, " %s.%s\n", + PQgetvalue(res, rowno, i_nspname), + PQgetvalue(res, rowno, i_relname)); +} + +/* + * Verify that no storage parameters (a.k.a. reloptions) are defined for TOAST + * tables. + */ +static void +check_for_toast_reloptions(ClusterInfo *cluster) +{ + UpgradeTaskReport report; + UpgradeTask *task = upgrade_task_create(); + const char *query = "SELECT n.nspname, c.relname " + "FROM pg_catalog.pg_class c, " + " pg_catalog.pg_class tc, " + " pg_catalog.pg_namespace n " + "WHERE c.reltoastrelid = tc.oid AND " + " c.relnamespace = c.oid AND " + " tc.reloptions IS NOT NULL"; + + prep_status("Check for tables with TOAST storage parameters"); + + report.file = NULL; + snprintf(report.path, sizeof(report.path), "%s/%s", + log_opts.basedir, + "tables_with_toast_storage_parameters.txt"); + + upgrade_task_add_step(task, query, process_toast_relopts_check, + true, &report); + upgrade_task_run(task, cluster); + upgrade_task_free(task); + + if (report.file) + { + fclose(report.file); + pg_log(PG_REPORT, "fatal"); + pg_fatal("Your installation contains tables with TOAST storage parameters set, which is\n" + "not supported anymore. Consider remove the TOAST storage parameters using\n" + " ALTER TABLE ... RESET ( ... );\n" + "A list of tables with the problem is in the file:\n" + " %s", report.path); + } + else + check_ok(); +} diff --git a/src/include/access/reloptions.h b/src/include/access/reloptions.h index e8cb7f7a627..4415c1ca9b5 100644 --- a/src/include/access/reloptions.h +++ b/src/include/access/reloptions.h @@ -40,26 +40,22 @@ typedef enum relopt_kind { RELOPT_KIND_LOCAL = 0, RELOPT_KIND_HEAP = (1 << 0), - RELOPT_KIND_TOAST = (1 << 1), - RELOPT_KIND_BTREE = (1 << 2), - RELOPT_KIND_HASH = (1 << 3), - RELOPT_KIND_GIN = (1 << 4), - RELOPT_KIND_GIST = (1 << 5), - RELOPT_KIND_ATTRIBUTE = (1 << 6), - RELOPT_KIND_TABLESPACE = (1 << 7), - RELOPT_KIND_SPGIST = (1 << 8), - RELOPT_KIND_VIEW = (1 << 9), - RELOPT_KIND_BRIN = (1 << 10), - RELOPT_KIND_PARTITIONED = (1 << 11), + RELOPT_KIND_BTREE = (1 << 1), + RELOPT_KIND_HASH = (1 << 2), + RELOPT_KIND_GIN = (1 << 3), + RELOPT_KIND_GIST = (1 << 4), + RELOPT_KIND_ATTRIBUTE = (1 << 5), + RELOPT_KIND_TABLESPACE = (1 << 6), + RELOPT_KIND_SPGIST = (1 << 7), + RELOPT_KIND_VIEW = (1 << 8), + RELOPT_KIND_BRIN = (1 << 9), + RELOPT_KIND_PARTITIONED = (1 << 10), /* if you add a new kind, make sure you update "last_default" too */ RELOPT_KIND_LAST_DEFAULT = RELOPT_KIND_PARTITIONED, /* some compilers treat enums as signed ints, so we can't use 1 << 31 */ RELOPT_KIND_MAX = (1 << 30) } relopt_kind; -/* reloption namespaces allowed for heaps -- currently only TOAST */ -#define HEAP_RELOPT_NAMESPACES { "toast", NULL } - /* generic struct to hold shared data */ typedef struct relopt_gen { diff --git a/src/include/catalog/toasting.h b/src/include/catalog/toasting.h index 0bc61a8fee9..13e5a8affcb 100644 --- a/src/include/catalog/toasting.h +++ b/src/include/catalog/toasting.h @@ -19,10 +19,10 @@ /* * toasting.c prototypes */ -extern void NewRelationCreateToastTable(Oid relOid, Datum reloptions); -extern void NewHeapCreateToastTable(Oid relOid, Datum reloptions, +extern void NewRelationCreateToastTable(Oid relOid); +extern void NewHeapCreateToastTable(Oid relOid, LOCKMODE lockmode, Oid OIDOldToast); -extern void AlterTableCreateToastTable(Oid relOid, Datum reloptions, +extern void AlterTableCreateToastTable(Oid relOid, LOCKMODE lockmode); extern void BootstrapToastTable(char *relName, Oid toastOid, Oid toastIndexOid); diff --git a/src/test/modules/injection_points/expected/vacuum.out b/src/test/modules/injection_points/expected/vacuum.out index 58df59fa927..dcfa729d05e 100644 --- a/src/test/modules/injection_points/expected/vacuum.out +++ b/src/test/modules/injection_points/expected/vacuum.out @@ -43,29 +43,29 @@ SELECT injection_points_attach('vacuum-truncate-enabled', 'notice'); (1 row) -- Check state of index_cleanup and truncate in VACUUM. -CREATE TABLE vac_tab_on_toast_off(i int, j text) WITH +CREATE TABLE vac_tab_on_toast(i int, j text) WITH (autovacuum_enabled=false, - vacuum_index_cleanup=true, toast.vacuum_index_cleanup=false, - vacuum_truncate=true, toast.vacuum_truncate=false); -CREATE TABLE vac_tab_off_toast_on(i int, j text) WITH + vacuum_index_cleanup=true, + vacuum_truncate=true); +CREATE TABLE vac_tab_off_toast(i int, j text) WITH (autovacuum_enabled=false, - vacuum_index_cleanup=false, toast.vacuum_index_cleanup=true, - vacuum_truncate=false, toast.vacuum_truncate=true); + vacuum_index_cleanup=false, + vacuum_truncate=false); -- Multiple relations should use their options in isolation. -VACUUM vac_tab_on_toast_off, vac_tab_off_toast_on; +VACUUM vac_tab_on_toast, vac_tab_off_toast; +NOTICE: notice triggered for injection point vacuum-index-cleanup-enabled +NOTICE: notice triggered for injection point vacuum-truncate-enabled NOTICE: notice triggered for injection point vacuum-index-cleanup-enabled NOTICE: notice triggered for injection point vacuum-truncate-enabled NOTICE: notice triggered for injection point vacuum-index-cleanup-disabled NOTICE: notice triggered for injection point vacuum-truncate-disabled NOTICE: notice triggered for injection point vacuum-index-cleanup-disabled NOTICE: notice triggered for injection point vacuum-truncate-disabled -NOTICE: notice triggered for injection point vacuum-index-cleanup-enabled -NOTICE: notice triggered for injection point vacuum-truncate-enabled -- Check "auto" case of index_cleanup and "truncate" controlled by -- its GUC. CREATE TABLE vac_tab_auto(i int, j text) WITH (autovacuum_enabled=false, - vacuum_index_cleanup=auto, toast.vacuum_index_cleanup=auto); + vacuum_index_cleanup=auto); SET vacuum_truncate = false; VACUUM vac_tab_auto; NOTICE: notice triggered for injection point vacuum-index-cleanup-auto @@ -80,8 +80,8 @@ NOTICE: notice triggered for injection point vacuum-index-cleanup-auto NOTICE: notice triggered for injection point vacuum-truncate-enabled RESET vacuum_truncate; DROP TABLE vac_tab_auto; -DROP TABLE vac_tab_on_toast_off; -DROP TABLE vac_tab_off_toast_on; +DROP TABLE vac_tab_on_toast; +DROP TABLE vac_tab_off_toast; -- Cleanup SELECT injection_points_detach('vacuum-index-cleanup-auto'); injection_points_detach diff --git a/src/test/modules/injection_points/sql/vacuum.sql b/src/test/modules/injection_points/sql/vacuum.sql index 23760dd0f38..ee7a2e0566e 100644 --- a/src/test/modules/injection_points/sql/vacuum.sql +++ b/src/test/modules/injection_points/sql/vacuum.sql @@ -11,22 +11,22 @@ SELECT injection_points_attach('vacuum-truncate-disabled', 'notice'); SELECT injection_points_attach('vacuum-truncate-enabled', 'notice'); -- Check state of index_cleanup and truncate in VACUUM. -CREATE TABLE vac_tab_on_toast_off(i int, j text) WITH +CREATE TABLE vac_tab_on_toast(i int, j text) WITH (autovacuum_enabled=false, - vacuum_index_cleanup=true, toast.vacuum_index_cleanup=false, - vacuum_truncate=true, toast.vacuum_truncate=false); -CREATE TABLE vac_tab_off_toast_on(i int, j text) WITH + vacuum_index_cleanup=true, + vacuum_truncate=true); +CREATE TABLE vac_tab_off_toast(i int, j text) WITH (autovacuum_enabled=false, - vacuum_index_cleanup=false, toast.vacuum_index_cleanup=true, - vacuum_truncate=false, toast.vacuum_truncate=true); + vacuum_index_cleanup=false, + vacuum_truncate=false); -- Multiple relations should use their options in isolation. -VACUUM vac_tab_on_toast_off, vac_tab_off_toast_on; +VACUUM vac_tab_on_toast, vac_tab_off_toast; -- Check "auto" case of index_cleanup and "truncate" controlled by -- its GUC. CREATE TABLE vac_tab_auto(i int, j text) WITH (autovacuum_enabled=false, - vacuum_index_cleanup=auto, toast.vacuum_index_cleanup=auto); + vacuum_index_cleanup=auto); SET vacuum_truncate = false; VACUUM vac_tab_auto; SET vacuum_truncate = true; @@ -34,8 +34,8 @@ VACUUM vac_tab_auto; RESET vacuum_truncate; DROP TABLE vac_tab_auto; -DROP TABLE vac_tab_on_toast_off; -DROP TABLE vac_tab_off_toast_on; +DROP TABLE vac_tab_on_toast; +DROP TABLE vac_tab_off_toast; -- Cleanup SELECT injection_points_detach('vacuum-index-cleanup-auto'); diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out index 6dd22be0e8d..435e6beea0f 100644 --- a/src/test/regress/expected/alter_table.out +++ b/src/test/regress/expected/alter_table.out @@ -2860,15 +2860,6 @@ select * from my_locks order by 1; pg_toast | ShareUpdateExclusiveLock (2 rows) -commit; -begin; alter table alterlock set (toast.autovacuum_enabled = off); -select * from my_locks order by 1; - relname | max_lockmode ------------+-------------------------- - alterlock | ShareUpdateExclusiveLock - pg_toast | ShareUpdateExclusiveLock -(2 rows) - commit; begin; alter table alterlock set (autovacuum_enabled = off); select * from my_locks order by 1; diff --git a/src/test/regress/expected/reloptions.out b/src/test/regress/expected/reloptions.out index e3a974f2611..eff5fbece60 100644 --- a/src/test/regress/expected/reloptions.out +++ b/src/test/regress/expected/reloptions.out @@ -120,7 +120,6 @@ SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass; DROP TABLE reloptions_test; CREATE TEMP TABLE reloptions_test(i INT NOT NULL, j text) WITH (vacuum_truncate=false, - toast.vacuum_truncate=false, autovacuum_enabled=false); SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass; reloptions @@ -142,9 +141,9 @@ SELECT pg_relation_size('reloptions_test') > 0; SELECT reloptions FROM pg_class WHERE oid = (SELECT reltoastrelid FROM pg_class WHERE oid = 'reloptions_test'::regclass); - reloptions -------------------------- - {vacuum_truncate=false} + reloptions +------------ + (1 row) ALTER TABLE reloptions_test RESET (vacuum_truncate); @@ -165,56 +164,11 @@ SELECT pg_relation_size('reloptions_test') = 0; t (1 row) --- Test toast.* options -DROP TABLE reloptions_test; -CREATE TABLE reloptions_test (s VARCHAR) - WITH (toast.autovacuum_vacuum_cost_delay = 23); -SELECT reltoastrelid as toast_oid - FROM pg_class WHERE oid = 'reloptions_test'::regclass \gset -SELECT reloptions FROM pg_class WHERE oid = :toast_oid; - reloptions ------------------------------------ - {autovacuum_vacuum_cost_delay=23} -(1 row) - -ALTER TABLE reloptions_test SET (toast.autovacuum_vacuum_cost_delay = 24); -SELECT reloptions FROM pg_class WHERE oid = :toast_oid; - reloptions ------------------------------------ - {autovacuum_vacuum_cost_delay=24} -(1 row) - -ALTER TABLE reloptions_test RESET (toast.autovacuum_vacuum_cost_delay); -SELECT reloptions FROM pg_class WHERE oid = :toast_oid; - reloptions ------------- - -(1 row) - --- Fail on non-existent options in toast namespace -CREATE TABLE reloptions_test2 (i int) WITH (toast.not_existing_option = 42); -ERROR: unrecognized parameter "not_existing_option" --- Mix TOAST & heap -DROP TABLE reloptions_test; -CREATE TABLE reloptions_test (s VARCHAR) WITH - (toast.autovacuum_vacuum_cost_delay = 23, - autovacuum_vacuum_cost_delay = 24, fillfactor = 40); -SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass; - reloptions -------------------------------------------------- - {autovacuum_vacuum_cost_delay=24,fillfactor=40} -(1 row) - -SELECT reloptions FROM pg_class WHERE oid = ( - SELECT reltoastrelid FROM pg_class WHERE oid = 'reloptions_test'::regclass); - reloptions ------------------------------------ - {autovacuum_vacuum_cost_delay=23} -(1 row) - -- -- CREATE INDEX, ALTER INDEX for btrees -- +DROP TABLE reloptions_test; +CREATE TABLE reloptions_test (s VARCHAR); CREATE INDEX reloptions_test_idx ON reloptions_test (s) WITH (fillfactor=30); SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test_idx'::regclass; reloptions diff --git a/src/test/regress/expected/vacuum.out b/src/test/regress/expected/vacuum.out index d4696bc3325..d551ef57d50 100644 --- a/src/test/regress/expected/vacuum.out +++ b/src/test/regress/expected/vacuum.out @@ -209,14 +209,6 @@ VACUUM no_index_cleanup; INSERT INTO no_index_cleanup(i, t) VALUES (generate_series(31,60), repeat('1234567890',269)); DELETE FROM no_index_cleanup WHERE i < 45; --- Only toast index is cleaned up. -ALTER TABLE no_index_cleanup SET (vacuum_index_cleanup = off, - toast.vacuum_index_cleanup = yes); -VACUUM no_index_cleanup; --- Only parent is cleaned up. -ALTER TABLE no_index_cleanup SET (vacuum_index_cleanup = true, - toast.vacuum_index_cleanup = false); -VACUUM no_index_cleanup; -- Test some extra relations. VACUUM (INDEX_CLEANUP FALSE) vaccluster; VACUUM (INDEX_CLEANUP AUTO) vactst; -- index cleanup option is ignored if no indexes diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql index f5f13bbd3e7..410465dc022 100644 --- a/src/test/regress/sql/alter_table.sql +++ b/src/test/regress/sql/alter_table.sql @@ -1784,10 +1784,6 @@ begin; alter table alterlock reset (fillfactor); select * from my_locks order by 1; commit; -begin; alter table alterlock set (toast.autovacuum_enabled = off); -select * from my_locks order by 1; -commit; - begin; alter table alterlock set (autovacuum_enabled = off); select * from my_locks order by 1; commit; diff --git a/src/test/regress/sql/reloptions.sql b/src/test/regress/sql/reloptions.sql index 680c8bf8614..0809a2ae0e4 100644 --- a/src/test/regress/sql/reloptions.sql +++ b/src/test/regress/sql/reloptions.sql @@ -75,7 +75,6 @@ DROP TABLE reloptions_test; CREATE TEMP TABLE reloptions_test(i INT NOT NULL, j text) WITH (vacuum_truncate=false, - toast.vacuum_truncate=false, autovacuum_enabled=false); SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass; INSERT INTO reloptions_test VALUES (1, NULL), (NULL, NULL); @@ -94,39 +93,12 @@ INSERT INTO reloptions_test VALUES (1, NULL), (NULL, NULL); VACUUM (FREEZE, DISABLE_PAGE_SKIPPING) reloptions_test; SELECT pg_relation_size('reloptions_test') = 0; --- Test toast.* options -DROP TABLE reloptions_test; - -CREATE TABLE reloptions_test (s VARCHAR) - WITH (toast.autovacuum_vacuum_cost_delay = 23); -SELECT reltoastrelid as toast_oid - FROM pg_class WHERE oid = 'reloptions_test'::regclass \gset -SELECT reloptions FROM pg_class WHERE oid = :toast_oid; - -ALTER TABLE reloptions_test SET (toast.autovacuum_vacuum_cost_delay = 24); -SELECT reloptions FROM pg_class WHERE oid = :toast_oid; - -ALTER TABLE reloptions_test RESET (toast.autovacuum_vacuum_cost_delay); -SELECT reloptions FROM pg_class WHERE oid = :toast_oid; - --- Fail on non-existent options in toast namespace -CREATE TABLE reloptions_test2 (i int) WITH (toast.not_existing_option = 42); - --- Mix TOAST & heap -DROP TABLE reloptions_test; - -CREATE TABLE reloptions_test (s VARCHAR) WITH - (toast.autovacuum_vacuum_cost_delay = 23, - autovacuum_vacuum_cost_delay = 24, fillfactor = 40); - -SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass; -SELECT reloptions FROM pg_class WHERE oid = ( - SELECT reltoastrelid FROM pg_class WHERE oid = 'reloptions_test'::regclass); - -- -- CREATE INDEX, ALTER INDEX for btrees -- +DROP TABLE reloptions_test; +CREATE TABLE reloptions_test (s VARCHAR); CREATE INDEX reloptions_test_idx ON reloptions_test (s) WITH (fillfactor=30); SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test_idx'::regclass; diff --git a/src/test/regress/sql/vacuum.sql b/src/test/regress/sql/vacuum.sql index 247b8e23b23..37a84fd9145 100644 --- a/src/test/regress/sql/vacuum.sql +++ b/src/test/regress/sql/vacuum.sql @@ -175,14 +175,6 @@ VACUUM no_index_cleanup; INSERT INTO no_index_cleanup(i, t) VALUES (generate_series(31,60), repeat('1234567890',269)); DELETE FROM no_index_cleanup WHERE i < 45; --- Only toast index is cleaned up. -ALTER TABLE no_index_cleanup SET (vacuum_index_cleanup = off, - toast.vacuum_index_cleanup = yes); -VACUUM no_index_cleanup; --- Only parent is cleaned up. -ALTER TABLE no_index_cleanup SET (vacuum_index_cleanup = true, - toast.vacuum_index_cleanup = false); -VACUUM no_index_cleanup; -- Test some extra relations. VACUUM (INDEX_CLEANUP FALSE) vaccluster; VACUUM (INDEX_CLEANUP AUTO) vactst; -- index cleanup option is ignored if no indexes -- 2.50.1 (Apple Git-155)
