On 7/12/18, Peter Eisentraut <peter.eisentr...@2ndquadrant.com> wrote:
> To get things rolling, I have committed the regression test addition.
>
> I'll look through the other stuff soon.
Hi Peter,
I hope you don't mind, but since it might be tedious to piece together
the addenda I left behind in this thread, I thought it might be useful
to update Joe's patch. The attached was rebased over the new
regression test, passes the pg_upgrade test, and has a draft commit
message.
-John Naylor
From e414effdcf03b3005390ab1671f4425c9e3036f2 Mon Sep 17 00:00:00 2001
From: John Naylor <jcnay...@gmail.com>
Date: Sat, 14 Jul 2018 15:09:44 +0700
Subject: [PATCH] Add toast tables to most system catalogs
It has been project policy to create toast tables only for those catalogs
that might reasonably need one. Since this judgment call can change over
time, just create one for every catalog.
To prevent circular dependencies and to avoid adding complexity to VACUUM
FULL logic, exclude pg_class, pg_attribute, and pg_index. Also, to prevent
pg_upgrade from seeing a non-empty new cluster, exclude pg_largeobject
and pg_largeobject_metadata, which will never have reason for a toast
table anyway.
---
src/backend/catalog/catalog.c | 18 +++++--
src/include/catalog/toasting.h | 40 ++++++++++++++--
src/test/regress/expected/misc_sanity.out | 79 ++++++++-----------------------
src/test/regress/sql/misc_sanity.sql | 11 +++--
4 files changed, 80 insertions(+), 68 deletions(-)
diff --git a/src/backend/catalog/catalog.c b/src/backend/catalog/catalog.c
index a42155e..6061428 100644
--- a/src/backend/catalog/catalog.c
+++ b/src/backend/catalog/catalog.c
@@ -253,12 +253,24 @@ IsSharedRelation(Oid relationId)
relationId == SubscriptionNameIndexId)
return true;
/* These are their toast tables and toast indexes (see toasting.h) */
- if (relationId == PgShdescriptionToastTable ||
- relationId == PgShdescriptionToastIndex ||
+ if (relationId == PgAuthidToastTable ||
+ relationId == PgAuthidToastIndex ||
+ relationId == PgDatabaseToastTable ||
+ relationId == PgDatabaseToastIndex ||
relationId == PgDbRoleSettingToastTable ||
relationId == PgDbRoleSettingToastIndex ||
+ relationId == PgPlTemplateToastTable ||
+ relationId == PgPlTemplateToastIndex ||
+ relationId == PgReplicationOriginToastTable ||
+ relationId == PgReplicationOriginToastIndex ||
+ relationId == PgShdescriptionToastTable ||
+ relationId == PgShdescriptionToastIndex ||
relationId == PgShseclabelToastTable ||
- relationId == PgShseclabelToastIndex)
+ relationId == PgShseclabelToastIndex ||
+ relationId == PgSubscriptionToastTable ||
+ relationId == PgSubscriptionToastIndex ||
+ relationId == PgTablespaceToastTable ||
+ relationId == PgTablespaceToastIndex)
return true;
return false;
}
diff --git a/src/include/catalog/toasting.h b/src/include/catalog/toasting.h
index 3db39b8..f259890 100644
--- a/src/include/catalog/toasting.h
+++ b/src/include/catalog/toasting.h
@@ -46,25 +46,59 @@ extern void BootstrapToastTable(char *relName,
*/
/* normal catalogs */
+DECLARE_TOAST(pg_aggregate, 4159, 4160);
DECLARE_TOAST(pg_attrdef, 2830, 2831);
+DECLARE_TOAST(pg_collation, 4161, 4162);
DECLARE_TOAST(pg_constraint, 2832, 2833);
+DECLARE_TOAST(pg_default_acl, 4143, 4144);
DECLARE_TOAST(pg_description, 2834, 2835);
+DECLARE_TOAST(pg_event_trigger, 4145, 4146);
+DECLARE_TOAST(pg_extension, 4147, 4148);
+DECLARE_TOAST(pg_foreign_data_wrapper, 4149, 4150);
+DECLARE_TOAST(pg_foreign_server, 4151, 4152);
+DECLARE_TOAST(pg_foreign_table, 4153, 4154);
+DECLARE_TOAST(pg_init_privs, 4155, 4156);
+DECLARE_TOAST(pg_language, 4157, 4158);
+DECLARE_TOAST(pg_namespace, 4163, 4164);
+DECLARE_TOAST(pg_partitioned_table, 4165, 4166);
+DECLARE_TOAST(pg_policy, 4167, 4168);
DECLARE_TOAST(pg_proc, 2836, 2837);
DECLARE_TOAST(pg_rewrite, 2838, 2839);
DECLARE_TOAST(pg_seclabel, 3598, 3599);
DECLARE_TOAST(pg_statistic, 2840, 2841);
DECLARE_TOAST(pg_statistic_ext, 3439, 3440);
DECLARE_TOAST(pg_trigger, 2336, 2337);
+DECLARE_TOAST(pg_ts_dict, 4169, 4170);
+DECLARE_TOAST(pg_type, 4171, 4172);
+DECLARE_TOAST(pg_user_mapping, 4173, 4174);
/* shared catalogs */
-DECLARE_TOAST(pg_shdescription, 2846, 2847);
-#define PgShdescriptionToastTable 2846
-#define PgShdescriptionToastIndex 2847
+DECLARE_TOAST(pg_authid, 4175, 4176);
+#define PgAuthidToastTable 4175
+#define PgAuthidToastIndex 4176
+DECLARE_TOAST(pg_database, 4177, 4178);
+#define PgDatabaseToastTable 4177
+#define PgDatabaseToastIndex 4178
DECLARE_TOAST(pg_db_role_setting, 2966, 2967);
#define PgDbRoleSettingToastTable 2966
#define PgDbRoleSettingToastIndex 2967
+DECLARE_TOAST(pg_pltemplate, 4179, 4180);
+#define PgPlTemplateToastTable 4179
+#define PgPlTemplateToastIndex 4180
+DECLARE_TOAST(pg_replication_origin, 4181, 4182);
+#define PgReplicationOriginToastTable 4181
+#define PgReplicationOriginToastIndex 4182
+DECLARE_TOAST(pg_shdescription, 2846, 2847);
+#define PgShdescriptionToastTable 2846
+#define PgShdescriptionToastIndex 2847
DECLARE_TOAST(pg_shseclabel, 4060, 4061);
#define PgShseclabelToastTable 4060
#define PgShseclabelToastIndex 4061
+DECLARE_TOAST(pg_subscription, 4183, 4184);
+#define PgSubscriptionToastTable 4183
+#define PgSubscriptionToastIndex 4184
+DECLARE_TOAST(pg_tablespace, 4185, 4186);
+#define PgTablespaceToastTable 4185
+#define PgTablespaceToastIndex 4186
#endif /* TOASTING_H */
diff --git a/src/test/regress/expected/misc_sanity.out b/src/test/regress/expected/misc_sanity.out
index baf3029..06382b00 100644
--- a/src/test/regress/expected/misc_sanity.out
+++ b/src/test/regress/expected/misc_sanity.out
@@ -77,10 +77,13 @@ NOTICE: pg_extension contains unpinned initdb-created object(s)
NOTICE: pg_rewrite contains unpinned initdb-created object(s)
NOTICE: pg_tablespace contains unpinned initdb-created object(s)
-- **************** pg_class ****************
--- Look for system tables with varlena columns but no toast table. At
--- the moment, the result just records the status quo so that changes
--- are deliberate. Which system tables have toast tables is a bit
--- arbitrary at the moment.
+-- Look for system tables with varlena columns but no toast table.
+-- All system tables with toastable columns should have toast
+-- tables, with the following exceptions:
+-- 1. pg_class, pg_attribute, and pg_index, due to fear of
+-- recursive dependencies
+-- 2. pg_largeobject and pg_largeobject_metadata, to avoid problems
+-- with pg_upgrade
SELECT relname, attname, atttypid::regtype
FROM pg_class c JOIN pg_attribute a ON c.oid = attrelid
WHERE c.oid < 16384 AND
@@ -88,58 +91,18 @@ WHERE c.oid < 16384 AND
relkind = 'r' AND
attstorage != 'p'
ORDER BY 1, 2;
- relname | attname | atttypid
--------------------------+-----------------+--------------
- pg_aggregate | agginitval | text
- pg_aggregate | aggminitval | text
- pg_attribute | attacl | aclitem[]
- pg_attribute | attfdwoptions | text[]
- pg_attribute | attmissingval | anyarray
- pg_attribute | attoptions | text[]
- pg_authid | rolpassword | text
- pg_class | relacl | aclitem[]
- pg_class | reloptions | text[]
- pg_class | relpartbound | pg_node_tree
- pg_collation | collversion | text
- pg_database | datacl | aclitem[]
- pg_default_acl | defaclacl | aclitem[]
- pg_event_trigger | evttags | text[]
- pg_extension | extcondition | text[]
- pg_extension | extconfig | oid[]
- pg_extension | extversion | text
- pg_foreign_data_wrapper | fdwacl | aclitem[]
- pg_foreign_data_wrapper | fdwoptions | text[]
- pg_foreign_server | srvacl | aclitem[]
- pg_foreign_server | srvoptions | text[]
- pg_foreign_server | srvtype | text
- pg_foreign_server | srvversion | text
- pg_foreign_table | ftoptions | text[]
- pg_index | indexprs | pg_node_tree
- pg_index | indpred | pg_node_tree
- pg_init_privs | initprivs | aclitem[]
- pg_language | lanacl | aclitem[]
- pg_largeobject | data | bytea
- pg_largeobject_metadata | lomacl | aclitem[]
- pg_namespace | nspacl | aclitem[]
- pg_partitioned_table | partexprs | pg_node_tree
- pg_pltemplate | tmplacl | aclitem[]
- pg_pltemplate | tmplhandler | text
- pg_pltemplate | tmplinline | text
- pg_pltemplate | tmpllibrary | text
- pg_pltemplate | tmplvalidator | text
- pg_policy | polqual | pg_node_tree
- pg_policy | polroles | oid[]
- pg_policy | polwithcheck | pg_node_tree
- pg_replication_origin | roname | text
- pg_subscription | subconninfo | text
- pg_subscription | subpublications | text[]
- pg_subscription | subsynccommit | text
- pg_tablespace | spcacl | aclitem[]
- pg_tablespace | spcoptions | text[]
- pg_ts_dict | dictinitoption | text
- pg_type | typacl | aclitem[]
- pg_type | typdefault | text
- pg_type | typdefaultbin | pg_node_tree
- pg_user_mapping | umoptions | text[]
-(51 rows)
+ relname | attname | atttypid
+-------------------------+---------------+--------------
+ pg_attribute | attacl | aclitem[]
+ pg_attribute | attfdwoptions | text[]
+ pg_attribute | attmissingval | anyarray
+ pg_attribute | attoptions | text[]
+ pg_class | relacl | aclitem[]
+ pg_class | reloptions | text[]
+ pg_class | relpartbound | pg_node_tree
+ pg_index | indexprs | pg_node_tree
+ pg_index | indpred | pg_node_tree
+ pg_largeobject | data | bytea
+ pg_largeobject_metadata | lomacl | aclitem[]
+(11 rows)
diff --git a/src/test/regress/sql/misc_sanity.sql b/src/test/regress/sql/misc_sanity.sql
index fe15fdd..82ff4ce 100644
--- a/src/test/regress/sql/misc_sanity.sql
+++ b/src/test/regress/sql/misc_sanity.sql
@@ -75,10 +75,13 @@ end$$;
-- **************** pg_class ****************
--- Look for system tables with varlena columns but no toast table. At
--- the moment, the result just records the status quo so that changes
--- are deliberate. Which system tables have toast tables is a bit
--- arbitrary at the moment.
+-- Look for system tables with varlena columns but no toast table.
+-- All system tables with toastable columns should have toast
+-- tables, with the following exceptions:
+-- 1. pg_class, pg_attribute, and pg_index, due to fear of
+-- recursive dependencies
+-- 2. pg_largeobject and pg_largeobject_metadata, to avoid problems
+-- with pg_upgrade
SELECT relname, attname, atttypid::regtype
FROM pg_class c JOIN pg_attribute a ON c.oid = attrelid
--
2.7.4
