extension 生成的对象

版本

# cat /etc/centos-release
CentOS Linux release 7.4.1708 (Core) 
# 
# 
# su - postgres
$ 
$ 
$ psql
psql (10.10)
Type "help" for help.

postgres=# select version();
                                                 version                                                  
----------------------------------------------------------------------------------------------------------
 PostgreSQL 10.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit
(1 row)

postgres=# 

extension

生成 pg_pathman extension

postgres=# create extension pg_pathman;

postgres=# \dx
                                      List of installed extensions
    Name     | Version |   Schema   |                            Description                            
-------------+---------+------------+-------------------------------------------------------------------
 pg_pathman  | 1.5     | public     | Partitioning tool for PostgreSQL
 plpgsql     | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

查看 extension 生成对象

postgres=# \dx+ pg_pathman
                                  Objects in extension "pg_pathman"
                                         Object description                                          
-----------------------------------------------------------------------------------------------------
 event trigger pathman_ddl_trigger
 function add_range_partition(regclass,anyelement,anyelement,text,text)
 function add_to_pathman_config(regclass,text)
 function add_to_pathman_config(regclass,text,text)
 function alter_partition(regclass,text,regnamespace,text)
 function append_partition_internal(regclass,regtype,text,anyarray,text,text)
 function append_range_partition(regclass,text,text)
 function attach_range_partition(regclass,regclass,anyelement,anyelement)
 function build_check_constraint_name(regclass)
 function build_hash_condition(regtype,text,integer,integer)
 function build_range_condition(regclass,text,anyelement,anyelement)
 function build_sequence_name(regclass)
 function check_boundaries(regclass,text,anyelement,anyelement)
 function check_range_available(regclass,anyelement,anyelement)
 function check_security_policy(regclass)
 function copy_foreign_keys(regclass,regclass)
 function create_hash_partitions_internal(regclass,text,integer,text[],text[])
 function create_hash_partitions(regclass,text,integer,boolean,text[],text[])
 function create_naming_sequence(regclass)
 function create_range_partitions_internal(regclass,anyarray,text[],text[])
 function create_range_partitions(regclass,text,anyarray,text[],text[],boolean)
 function create_range_partitions(regclass,text,anyelement,anyelement,integer,boolean)
 function create_range_partitions(regclass,text,anyelement,interval,integer,boolean)
 function create_single_range_partition(regclass,anyelement,anyelement,text,text)
 function debug_capture()
 function detach_range_partition(regclass)
 function disable_pathman_for(regclass)
 function drop_naming_sequence(regclass)
 function drop_partitions(regclass,boolean)
 function drop_range_partition_expand_next(regclass)
 function drop_range_partition(regclass,boolean)
 function generate_range_bounds(anyelement,anyelement,integer)
 function generate_range_bounds(anyelement,interval,integer)
 function get_base_type(regtype)
 function get_hash_part_idx(integer,integer)
 function get_number_of_partitions(regclass)
 function get_parent_of_partition(regclass)
 function get_partition_cooked_key(regclass)
 function get_partition_key(regclass)
 function get_partition_key_type(regclass)
 function get_partition_type(regclass)
 function get_part_range(regclass,anyelement)
 function get_part_range(regclass,integer,anyelement)
 function get_plain_schema_and_relname(regclass)
 function get_tablespace(regclass)
 function invoke_on_partition_created_callback(regclass,regclass,regprocedure)
 function invoke_on_partition_created_callback(regclass,regclass,regprocedure,anyelement,anyelement)
 function is_date_type(regtype)
 function is_operator_supported(regtype,text)
 function is_tuple_convertible(regclass,regclass)
 function merge_range_partitions(regclass[])
 function _partition_data_concurrent(regclass,anyelement,anyelement,integer)
 function partition_data(regclass)
 function partition_table_concurrently(regclass,integer,double precision)
 function pathman_config_params_trigger_func()
 function pathman_ddl_trigger_func()
 function pathman_set_param(regclass,text,anyelement)
 function pathman_version()
 function prepare_for_partitioning(regclass,text,boolean)
 function prepend_partition_internal(regclass,regtype,text,anyarray,text,text)
 function prepend_range_partition(regclass,text,text)
 function prevent_data_modification(regclass)
 function prevent_part_modification(regclass)
 function replace_hash_partition(regclass,regclass,boolean)
 function set_auto(regclass,boolean)
 function set_enable_parent(regclass,boolean)
 function set_init_callback(regclass,regprocedure)
 function set_interval(regclass,anyelement)
 function set_spawn_using_bgw(regclass,boolean)
 function show_cache_stats()
 function show_concurrent_part_tasks()
 function show_partition_list()
 function split_range_partition(regclass,anyelement,text,text)
 function stop_concurrent_part_task(regclass)
 function validate_expression(regclass,text)
 function validate_interval_value(regclass,text,integer,text)
 function validate_part_callback(regprocedure,boolean)
 function validate_relname(regclass)
 table pathman_config
 table pathman_config_params
 view pathman_cache_stats
 view pathman_concurrent_part_tasks
 view pathman_partition_list
(83 rows)

开启参数 log_statement =‘all’ 后，可以从日志里抓取到对应的SQL。

SELECT e.extname AS "Name", e.extversion AS "Version", n.nspname AS "Schema", c.description AS "Description"
FROM pg_catalog.pg_extension e 
     LEFT JOIN pg_catalog.pg_namespace n 
	           ON n.oid = e.extnamespace 
	 LEFT JOIN pg_catalog.pg_description c 
	           ON c.objoid = e.oid 
			   AND c.classoid = 'pg_catalog.pg_extension'::pg_catalog.regclass
ORDER BY 1;

SELECT e.extname, e.oid
  FROM pg_catalog.pg_extension e
 WHERE e.extname OPERATOR(pg_catalog.~) '^(pg_pathman)$'
 ORDER BY 1;

SELECT pg_catalog.pg_describe_object(classid, objid, 0) AS "Object description"
  FROM pg_catalog.pg_depend
 WHERE refclassid = 'pg_catalog.pg_extension'::pg_catalog.regclass 
   AND refobjid = '24619' AND deptype = 'e'
 ORDER BY 1;

做些改进后，可以查看所有extension 的表、视图对象

SELECT pe.extoid,pe.extname,
	   pe.extnspoid,pe.extnspname,
	   pc.oid as reloid,pc.relname,pc.relkind,
	   pn.oid as relnspoid,pn.nspname as relnspname,
	   pg_catalog.pg_describe_object(pd.classid, pd.objid, 0) as  objdescription,
	   pd.classid, pd.objid
  FROM pg_catalog.pg_depend pd,
	   (  SELECT e.oid as extoid,e.extname , 
					   e.extversion , 
					   n.oid as extnspoid,n.nspname as extnspname ,c.description 
			FROM pg_catalog.pg_extension e 
					  LEFT JOIN pg_catalog.pg_namespace n 
								 ON n.oid = e.extnamespace 
					  LEFT JOIN pg_catalog.pg_description c 
								 ON c.objoid = e.oid 
									   AND c.classoid = 'pg_catalog.pg_extension'::pg_catalog.regclass
			ORDER BY 1 ) pe,
		pg_catalog.pg_class pc,
		pg_catalog.pg_namespace pn
  WHERE pd.refclassid = 'pg_catalog.pg_extension'::pg_catalog.regclass 
    AND pd.refobjid = pe.extoid
    AND pd.deptype = 'e'
    AND pd.classid = (select pc0.oid from pg_class pc0 where pc0.oid= 'pg_catalog.pg_class'::pg_catalog.regclass)
    AND pd.objid = pc.oid
    AND pc.relnamespace = pn.oid
ORDER BY 1;

参考: