Label

PostgreSQL , create extension , pg_catalog

background

pg_catalog is the default metadata schema for PostgreSQL, where all metadata is located.

On the other hand, the pg_catalog schema is in the search_path by default and cannot be deleted, and its priority ranks ahead of all schemas.

For example, there are two renamed pg_classes in these two schema s. pg_catalog.pg_calss, public.pg_class, if you execute select* from pg_class, the search is pg_catalog.pg_class.

So when we create extension s or some shared objects, we can create them in public or pg_catalog. The advantage of creating in pg_catalog is that it has the highest priority and is visible to all users.

create extension extension_name with schema pg_catalog;   

You can create it in pg_catalog.

Or in this way

alter extension extension_name set schema pg_catalog;  

But for now, there are some minor problems.

For example, when an extension contains common composite types, tables and other objects, create extension_name with schema pg_catalog; grammar, errors will be reported

cd $PGHOME/share/extension  
  
cp btree_gist--1.2.sql btree_gist--9.9.9.sql  
  
vi btree_gist--9.9.9.sql  
-- Add as follows  
create type new_type as (  
  id int,  
  c1 int  
);  

test

postgres=# create extension btree_gist with schema pg_catalog;  
CREATE EXTENSION  
postgres=# drop extension btree_gist ;  
DROP EXTENSION  
  
//Creation failure due to the addition of composite types  
postgres=# \set VERBOSITY verbose  
postgres=# create extension btree_gist with schema pg_catalog version '9.9.9';  
ERROR:  42501: permission denied to create "pg_catalog.new_type"  
DETAIL:  System catalog modifications are currently disallowed.  
LOCATION:  heap_create, heap.c:280  

For extension s with table, seq, and composite types, if you want to create them into pg_catalog, you can fix them as follows

Repair method 1

Create it into a common schema, then modify it using alter extension set schema pg_catalog.

postgres=# create extension btree_gist version '9.9.9';;  
CREATE EXTENSION  
postgres=# alter extension btree_gist set schema pg_catalog;  
ALTER EXTENSION  

Repair method 2

Set allow_system_table_mods parameter to allow modification

Can't be modified directly  
postgres=# set allow_system_table_mods=on;    
ERROR:  55P02: parameter "allow_system_table_mods" cannot be changed without restarting the server  
LOCATION:  set_config_option, guc.c:5899  
  
  
//Modify to the configuration file and restart the database  
vi $PGDATA/postgresql.conf  
allow_system_table_mods=on  
  
pg_ctl restart -m fast  
  
//Then you can create it directly into pg_catalog  
postgres=# create extension btree_gist with schema pg_catalog version '9.9.9';  
CREATE EXTENSION  

The reason for this error is that a pg_catalog is not allowed to be modified, but a non-composite type is not allowed.

Intercept part of the original btree_gist--1.2.sql, including create type, operator and other operations, which allow execution in pg_catalog.  
  
CREATE TYPE gbtreekey_var (  
        INTERNALLENGTH = VARIABLE,  
        INPUT  = gbtreekey_var_in,  
        OUTPUT = gbtreekey_var_out,  
        STORAGE = EXTENDED  
);  

The community will fix the problem and unify the authority style.