Skip to content

Commit 96cdeae

Browse files
michaelpqmichaelpq
committed
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, as this can be useful when creating rather-long entries in catalogs, with recent examples being in the shape of policy expressions or customly-formatted SCRAM verifiers. 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 from the set as large object data is handled as user data. Those relations have no reason to use a toast table anyway. Author: Joe Conway, John Naylor Reviewed-by: Michael Paquier, Tom Lane Discussion: https://postgr.es/m/84ddff04-f122-784b-b6c5-3536804495f8@joeconway.com
1 parent 2409716 commit 96cdeae

File tree

4 files changed

+82
-68
lines changed

4 files changed

+82
-68
lines changed

‎src/backend/catalog/catalog.c

Lines changed: 15 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -253,12 +253,24 @@ IsSharedRelation(Oid relationId)
253253
relationId == SubscriptionNameIndexId)
254254
return true;
255255
/* These are their toast tables and toast indexes (see toasting.h) */
256-
if (relationId == PgShdescriptionToastTable ||
257-
relationId == PgShdescriptionToastIndex ||
256+
if (relationId == PgAuthidToastTable ||
257+
relationId == PgAuthidToastIndex ||
258+
relationId == PgDatabaseToastTable ||
259+
relationId == PgDatabaseToastIndex ||
258260
relationId == PgDbRoleSettingToastTable ||
259261
relationId == PgDbRoleSettingToastIndex ||
262+
relationId == PgPlTemplateToastTable ||
263+
relationId == PgPlTemplateToastIndex ||
264+
relationId == PgReplicationOriginToastTable ||
265+
relationId == PgReplicationOriginToastIndex ||
266+
relationId == PgShdescriptionToastTable ||
267+
relationId == PgShdescriptionToastIndex ||
260268
relationId == PgShseclabelToastTable ||
261-
relationId == PgShseclabelToastIndex)
269+
relationId == PgShseclabelToastIndex ||
270+
relationId == PgSubscriptionToastTable ||
271+
relationId == PgSubscriptionToastIndex ||
272+
relationId == PgTablespaceToastTable ||
273+
relationId == PgTablespaceToastIndex)
262274
return true;
263275
return false;
264276
}

‎src/include/catalog/toasting.h

Lines changed: 37 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -46,25 +46,59 @@ extern void BootstrapToastTable(char *relName,
4646
*/
4747

4848
/* normal catalogs */
49+
DECLARE_TOAST(pg_aggregate, 4159, 4160);
4950
DECLARE_TOAST(pg_attrdef, 2830, 2831);
51+
DECLARE_TOAST(pg_collation, 4161, 4162);
5052
DECLARE_TOAST(pg_constraint, 2832, 2833);
53+
DECLARE_TOAST(pg_default_acl, 4143, 4144);
5154
DECLARE_TOAST(pg_description, 2834, 2835);
55+
DECLARE_TOAST(pg_event_trigger, 4145, 4146);
56+
DECLARE_TOAST(pg_extension, 4147, 4148);
57+
DECLARE_TOAST(pg_foreign_data_wrapper, 4149, 4150);
58+
DECLARE_TOAST(pg_foreign_server, 4151, 4152);
59+
DECLARE_TOAST(pg_foreign_table, 4153, 4154);
60+
DECLARE_TOAST(pg_init_privs, 4155, 4156);
61+
DECLARE_TOAST(pg_language, 4157, 4158);
62+
DECLARE_TOAST(pg_namespace, 4163, 4164);
63+
DECLARE_TOAST(pg_partitioned_table, 4165, 4166);
64+
DECLARE_TOAST(pg_policy, 4167, 4168);
5265
DECLARE_TOAST(pg_proc, 2836, 2837);
5366
DECLARE_TOAST(pg_rewrite, 2838, 2839);
5467
DECLARE_TOAST(pg_seclabel, 3598, 3599);
5568
DECLARE_TOAST(pg_statistic, 2840, 2841);
5669
DECLARE_TOAST(pg_statistic_ext, 3439, 3440);
5770
DECLARE_TOAST(pg_trigger, 2336, 2337);
71+
DECLARE_TOAST(pg_ts_dict, 4169, 4170);
72+
DECLARE_TOAST(pg_type, 4171, 4172);
73+
DECLARE_TOAST(pg_user_mapping, 4173, 4174);
5874

5975
/* shared catalogs */
60-
DECLARE_TOAST(pg_shdescription, 2846, 2847);
61-
#define PgShdescriptionToastTable 2846
62-
#define PgShdescriptionToastIndex 2847
76+
DECLARE_TOAST(pg_authid, 4175, 4176);
77+
#define PgAuthidToastTable 4175
78+
#define PgAuthidToastIndex 4176
79+
DECLARE_TOAST(pg_database, 4177, 4178);
80+
#define PgDatabaseToastTable 4177
81+
#define PgDatabaseToastIndex 4178
6382
DECLARE_TOAST(pg_db_role_setting, 2966, 2967);
6483
#define PgDbRoleSettingToastTable 2966
6584
#define PgDbRoleSettingToastIndex 2967
85+
DECLARE_TOAST(pg_pltemplate, 4179, 4180);
86+
#define PgPlTemplateToastTable 4179
87+
#define PgPlTemplateToastIndex 4180
88+
DECLARE_TOAST(pg_replication_origin, 4181, 4182);
89+
#define PgReplicationOriginToastTable 4181
90+
#define PgReplicationOriginToastIndex 4182
91+
DECLARE_TOAST(pg_shdescription, 2846, 2847);
92+
#define PgShdescriptionToastTable 2846
93+
#define PgShdescriptionToastIndex 2847
6694
DECLARE_TOAST(pg_shseclabel, 4060, 4061);
6795
#define PgShseclabelToastTable 4060
6896
#define PgShseclabelToastIndex 4061
97+
DECLARE_TOAST(pg_subscription, 4183, 4184);
98+
#define PgSubscriptionToastTable 4183
99+
#define PgSubscriptionToastIndex 4184
100+
DECLARE_TOAST(pg_tablespace, 4185, 4186);
101+
#define PgTablespaceToastTable 4185
102+
#define PgTablespaceToastIndex 4186
69103

70104
#endif /* TOASTING_H */

‎src/test/regress/expected/misc_sanity.out

Lines changed: 22 additions & 58 deletions
Original file line numberDiff line numberDiff line change
@@ -77,69 +77,33 @@ NOTICE: pg_extension contains unpinned initdb-created object(s)
7777
NOTICE: pg_rewrite contains unpinned initdb-created object(s)
7878
NOTICE: pg_tablespace contains unpinned initdb-created object(s)
7979
-- **************** pg_class ****************
80-
-- Look for system tables with varlena columns but no toast table. At
81-
-- the moment, the result just records the status quo so that changes
82-
-- are deliberate. Which system tables have toast tables is a bit
83-
-- arbitrary at the moment.
80+
-- Look for system tables with varlena columns but no toast table. All
81+
-- system tables with toastable columns should have toast tables, with
82+
-- the following exceptions:
83+
-- 1. pg_class, pg_attribute, and pg_index, due to fear of recursive
84+
-- dependencies as toast tables depend on them.
85+
-- 2. pg_largeobject and pg_largeobject_metadata. Large object catalogs
86+
-- and toast tables are mutually exclusive and large object data is handled
87+
-- as user data by pg_upgrade, which would cause failures.
8488
SELECT relname, attname, atttypid::regtype
8589
FROM pg_class c JOIN pg_attribute a ON c.oid = attrelid
8690
WHERE c.oid < 16384 AND
8791
reltoastrelid = 0 AND
8892
relkind = 'r' AND
8993
attstorage != 'p'
9094
ORDER BY 1, 2;
91-
relname | attname | atttypid
92-
-------------------------+-----------------+--------------
93-
pg_aggregate | agginitval | text
94-
pg_aggregate | aggminitval | text
95-
pg_attribute | attacl | aclitem[]
96-
pg_attribute | attfdwoptions | text[]
97-
pg_attribute | attmissingval | anyarray
98-
pg_attribute | attoptions | text[]
99-
pg_authid | rolpassword | text
100-
pg_class | relacl | aclitem[]
101-
pg_class | reloptions | text[]
102-
pg_class | relpartbound | pg_node_tree
103-
pg_collation | collversion | text
104-
pg_database | datacl | aclitem[]
105-
pg_default_acl | defaclacl | aclitem[]
106-
pg_event_trigger | evttags | text[]
107-
pg_extension | extcondition | text[]
108-
pg_extension | extconfig | oid[]
109-
pg_extension | extversion | text
110-
pg_foreign_data_wrapper | fdwacl | aclitem[]
111-
pg_foreign_data_wrapper | fdwoptions | text[]
112-
pg_foreign_server | srvacl | aclitem[]
113-
pg_foreign_server | srvoptions | text[]
114-
pg_foreign_server | srvtype | text
115-
pg_foreign_server | srvversion | text
116-
pg_foreign_table | ftoptions | text[]
117-
pg_index | indexprs | pg_node_tree
118-
pg_index | indpred | pg_node_tree
119-
pg_init_privs | initprivs | aclitem[]
120-
pg_language | lanacl | aclitem[]
121-
pg_largeobject | data | bytea
122-
pg_largeobject_metadata | lomacl | aclitem[]
123-
pg_namespace | nspacl | aclitem[]
124-
pg_partitioned_table | partexprs | pg_node_tree
125-
pg_pltemplate | tmplacl | aclitem[]
126-
pg_pltemplate | tmplhandler | text
127-
pg_pltemplate | tmplinline | text
128-
pg_pltemplate | tmpllibrary | text
129-
pg_pltemplate | tmplvalidator | text
130-
pg_policy | polqual | pg_node_tree
131-
pg_policy | polroles | oid[]
132-
pg_policy | polwithcheck | pg_node_tree
133-
pg_replication_origin | roname | text
134-
pg_subscription | subconninfo | text
135-
pg_subscription | subpublications | text[]
136-
pg_subscription | subsynccommit | text
137-
pg_tablespace | spcacl | aclitem[]
138-
pg_tablespace | spcoptions | text[]
139-
pg_ts_dict | dictinitoption | text
140-
pg_type | typacl | aclitem[]
141-
pg_type | typdefault | text
142-
pg_type | typdefaultbin | pg_node_tree
143-
pg_user_mapping | umoptions | text[]
144-
(51 rows)
95+
relname | attname | atttypid
96+
-------------------------+---------------+--------------
97+
pg_attribute | attacl | aclitem[]
98+
pg_attribute | attfdwoptions | text[]
99+
pg_attribute | attmissingval | anyarray
100+
pg_attribute | attoptions | text[]
101+
pg_class | relacl | aclitem[]
102+
pg_class | reloptions | text[]
103+
pg_class | relpartbound | pg_node_tree
104+
pg_index | indexprs | pg_node_tree
105+
pg_index | indpred | pg_node_tree
106+
pg_largeobject | data | bytea
107+
pg_largeobject_metadata | lomacl | aclitem[]
108+
(11 rows)
145109

‎src/test/regress/sql/misc_sanity.sql

Lines changed: 8 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -75,10 +75,14 @@ end$$;
7575

7676
-- **************** pg_class ****************
7777

78-
-- Look for system tables with varlena columns but no toast table. At
79-
-- the moment, the result just records the status quo so that changes
80-
-- are deliberate. Which system tables have toast tables is a bit
81-
-- arbitrary at the moment.
78+
-- Look for system tables with varlena columns but no toast table. All
79+
-- system tables with toastable columns should have toast tables, with
80+
-- the following exceptions:
81+
-- 1. pg_class, pg_attribute, and pg_index, due to fear of recursive
82+
-- dependencies as toast tables depend on them.
83+
-- 2. pg_largeobject and pg_largeobject_metadata. Large object catalogs
84+
-- and toast tables are mutually exclusive and large object data is handled
85+
-- as user data by pg_upgrade, which would cause failures.
8286

8387
SELECT relname, attname, atttypid::regtype
8488
FROM pg_class c JOIN pg_attribute a ON c.oid = attrelid

0 commit comments

Comments
 (0)