From b5faba1284c4e5108c6fbe577daa33f933e7a4e0 Mon Sep 17 00:00:00 2001
From: Itagaki Takahiro <itagaki.takahiro@gmail.com>
Date: Mon, 7 Jun 2010 02:59:02 +0000
Subject: [PATCH] Ensure default-only storage parameters for TOAST relations to
 be initialized with proper values. Affected parameters are fillfactor,
 analyze_threshold, and analyze_scale_factor.

Especially uninitialized fillfactor caused inefficient page usage
because we built a StdRdOptions struct in which fillfactor is zero
if any reloption is set for the toast table.

In addition, we disallow toast.autovacuum_analyze_threshold and
toast.autovacuum_analyze_scale_factor because we didn't actually
support them; they are always ignored.

Report by Rumko on pgsql-bugs on 12 May 2010.
Analysis by Tom Lane and Alvaro Herrera. Patch by me.

Backpatch to 8.4.
---
 doc/src/sgml/ref/create_table.sgml     |  6 +++---
 src/backend/access/common/reloptions.c | 19 +++++++++++++++----
 src/bin/psql/tab-complete.c            |  4 +---
 3 files changed, 19 insertions(+), 10 deletions(-)

diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index d195c5de68..0c02d0cb91 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -1,5 +1,5 @@
 <!--
-$PostgreSQL: pgsql/doc/src/sgml/ref/create_table.sgml,v 1.127 2010/05/13 18:54:18 tgl Exp $
+$PostgreSQL: pgsql/doc/src/sgml/ref/create_table.sgml,v 1.128 2010/06/07 02:59:02 itagaki Exp $
 PostgreSQL documentation
 -->
 
@@ -880,7 +880,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE <replaceable class="PAR
    </varlistentry>
 
    <varlistentry>
-    <term><literal>autovacuum_analyze_threshold</>, <literal>toast.autovacuum_analyze_threshold</literal> (<type>integer</>)</term>
+    <term><literal>autovacuum_analyze_threshold</> (<type>integer</>)</term>
     <listitem>
      <para>
      Minimum number of inserted, updated, or deleted tuples before initiate an
@@ -890,7 +890,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE <replaceable class="PAR
    </varlistentry>
 
    <varlistentry>
-    <term><literal>autovacuum_analyze_scale_factor</>, <literal>toast.autovacuum_analyze_scale_factor</literal> (<type>float4</>)</term>
+    <term><literal>autovacuum_analyze_scale_factor</> (<type>float4</>)</term>
     <listitem>
      <para>
      Multiplier for <structfield>reltuples</> to add to
diff --git a/src/backend/access/common/reloptions.c b/src/backend/access/common/reloptions.c
index 2b099e806c..cd4f59005a 100644
--- a/src/backend/access/common/reloptions.c
+++ b/src/backend/access/common/reloptions.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/access/common/reloptions.c,v 1.34 2010/03/11 21:47:19 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/access/common/reloptions.c,v 1.35 2010/06/07 02:59:02 itagaki Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -116,7 +116,7 @@ static relopt_int intRelOpts[] =
 		{
 			"autovacuum_analyze_threshold",
 			"Minimum number of tuple inserts, updates or deletes prior to analyze",
-			RELOPT_KIND_HEAP | RELOPT_KIND_TOAST
+			RELOPT_KIND_HEAP
 		},
 		-1, 0, INT_MAX
 	},
@@ -177,7 +177,7 @@ static relopt_real realRelOpts[] =
 		{
 			"autovacuum_analyze_scale_factor",
 			"Number of tuple inserts, updates or deletes prior to analyze as a fraction of reltuples",
-			RELOPT_KIND_HEAP | RELOPT_KIND_TOAST
+			RELOPT_KIND_HEAP
 		},
 		-1, 0.0, 100.0
 	},
@@ -1156,10 +1156,21 @@ default_reloptions(Datum reloptions, bool validate, relopt_kind kind)
 bytea *
 heap_reloptions(char relkind, Datum reloptions, bool validate)
 {
+	StdRdOptions *rdopts;
+
 	switch (relkind)
 	{
 		case RELKIND_TOASTVALUE:
-			return default_reloptions(reloptions, validate, RELOPT_KIND_TOAST);
+			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:
 			return default_reloptions(reloptions, validate, RELOPT_KIND_HEAP);
 		default:
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 7ac38069a6..7b6abd3873 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -3,7 +3,7 @@
  *
  * Copyright (c) 2000-2010, PostgreSQL Global Development Group
  *
- * $PostgreSQL: pgsql/src/bin/psql/tab-complete.c,v 1.198 2010/04/07 03:51:19 itagaki Exp $
+ * $PostgreSQL: pgsql/src/bin/psql/tab-complete.c,v 1.199 2010/06/07 02:59:02 itagaki Exp $
  */
 
 /*----------------------------------------------------------------------
@@ -1172,8 +1172,6 @@ psql_completion(char *text, int start, int end)
 			"autovacuum_vacuum_scale_factor",
 			"autovacuum_vacuum_threshold",
 			"fillfactor",
-			"toast.autovacuum_analyze_scale_factor",
-			"toast.autovacuum_analyze_threshold",
 			"toast.autovacuum_enabled",
 			"toast.autovacuum_freeze_max_age",
 			"toast.autovacuum_freeze_min_age",
-- 
2.40.0