PostgreSQL cheat functions - (内存上下文planner内容等常用函数)
背景
https://github.com/MasaoFujii/pg_cheat_funcs
Fujii Masao
MasaoFujii
PostgreSQL committer, DBA at NTT DATA, maybe good husband & father.
这个插件汇集了常用的PG扩展函数（将来也许会引入到PG社区中），比如
1、查看内存上下文，
2、查看SQL的绑定变量的通用成本，
| 3、plan成本状态（算法见《PostgreSQL 12 preview - plan_cache_mode参数控制强制使用plan cache或强制custom plan (force_custom_plan and force_generic_plan)》 [《执行计划选择算法 与 绑定变量 - PostgreSQL prepared statement: SPI_prepare, prepare | execute COMMAND, PL/pgsql STYLE: custom & generic plan cache》](../201212/20121224_01.md) ）， |
4、给PID发信号，
5、设置进程优先级，
6、设置nextxid，nextoid，
7、字符串转换，
8、进制转换，
9、压缩，解压缩等。
例子
. ./env11.sh
git clone https://github.com/MasaoFujii/pg_cheat_funcs
cd pg_cheat_funcs/
USE_PGXS=1 make
USE_PGXS=1 make install
postgres=# create extension pg_cheat_funcs ;
CREATE EXTENSION
1、打印内存上下文
postgres=# select * from pg_stat_get_memory_context();
name | parent | level | total_bytes | total_nblocks | free_bytes | free_chunks | used_bytes
--------------------------+--------------------+-------+-------------+---------------+------------+-------------+------------
TopMemoryContext | | 0 | 312552 | 11 | 40520 | 21 | 272032
dynahash | TopMemoryContext | 1 | 8192 | 1 | 1456 | 0 | 6736
TopTransactionContext | TopMemoryContext | 1 | 8192 | 1 | 7744 | 1 | 448
PL/pgSQL function | TopMemoryContext | 1 | 16384 | 2 | 7176 | 1 | 9208
dynahash | TopMemoryContext | 1 | 8192 | 1 | 560 | 0 | 7632
dynahash | TopMemoryContext | 1 | 8192 | 1 | 560 | 0 | 7632
dynahash | TopMemoryContext | 1 | 24528 | 2 | 2624 | 0 | 21904
dynahash | TopMemoryContext | 1 | 8192 | 1 | 2096 | 0 | 6096
dynahash | TopMemoryContext | 1 | 8192 | 1 | 1584 | 0 | 6608
dynahash | TopMemoryContext | 1 | 24576 | 2 | 10760 | 3 | 13816
RowDescriptionContext | TopMemoryContext | 1 | 8192 | 1 | 6896 | 0 | 1296
MessageContext | TopMemoryContext | 1 | 32768 | 3 | 10904 | 1 | 21864
dynahash | TopMemoryContext | 1 | 8192 | 1 | 560 | 0 | 7632
dynahash | TopMemoryContext | 1 | 32768 | 3 | 16832 | 8 | 15936
TransactionAbortContext | TopMemoryContext | 1 | 32768 | 1 | 32512 | 0 | 256
dynahash | TopMemoryContext | 1 | 8192 | 1 | 560 | 0 | 7632
TopPortalContext | TopMemoryContext | 1 | 8192 | 1 | 7664 | 0 | 528
PortalContext | TopPortalContext | 2 | 1024 | 1 | 592 | 0 | 432
ExecutorState | PortalContext | 3 | 49208 | 4 | 15576 | 3 | 33632
printtup | ExecutorState | 4 | 8192 | 1 | 7936 | 0 | 256
Table function arguments | ExecutorState | 4 | 8192 | 1 | 7936 | 0 | 256
ExprContext | ExecutorState | 4 | 8192 | 1 | 4536 | 0 | 3656
dynahash | TopMemoryContext | 1 | 16384 | 2 | 3512 | 2 | 12872
CacheMemoryContext | TopMemoryContext | 1 | 524288 | 7 | 20960 | 26 | 503328
index info | CacheMemoryContext | 2 | 2048 | 2 | 624 | 1 | 1424
index info | CacheMemoryContext | 2 | 2048 | 2 | 624 | 1 | 1424
index info | CacheMemoryContext | 2 | 2048 | 2 | 624 | 1 | 1424
index info | CacheMemoryContext | 2 | 2048 | 2 | 624 | 1 | 1424
index info | CacheMemoryContext | 2 | 2048 | 2 | 680 | 1 | 1368
EventTriggerCache | CacheMemoryContext | 2 | 8192 | 1 | 7936 | 2 | 256
dynahash | EventTriggerCache | 3 | 8192 | 1 | 2624 | 0 | 5568
index info | CacheMemoryContext | 2 | 1024 | 1 | 48 | 0 | 976
index info | CacheMemoryContext | 2 | 2048 | 2 | 952 | 1 | 1096
index info | CacheMemoryContext | 2 | 2048 | 2 | 952 | 1 | 1096
index info | CacheMemoryContext | 2 | 2048 | 2 | 952 | 1 | 1096
relation rules | CacheMemoryContext | 2 | 16384 | 5 | 7352 | 0 | 9032
index info | CacheMemoryContext | 2 | 2048 | 2 | 648 | 2 | 1400
index info | CacheMemoryContext | 2 | 2048 | 2 | 704 | 3 | 1344
index info | CacheMemoryContext | 2 | 1024 | 1 | 16 | 0 | 1008
index info | CacheMemoryContext | 2 | 1024 | 1 | 48 | 0 | 976
index info | CacheMemoryContext | 2 | 2048 | 2 | 680 | 2 | 1368
index info | CacheMemoryContext | 2 | 1024 | 1 | 48 | 0 | 976
index info | CacheMemoryContext | 2 | 1024 | 1 | 48 | 0 | 976
index info | CacheMemoryContext | 2 | 2048 | 2 | 592 | 3 | 1456
index info | CacheMemoryContext | 2 | 2048 | 2 | 680 | 2 | 1368
index info | CacheMemoryContext | 2 | 2048 | 2 | 952 | 1 | 1096
index info | CacheMemoryContext | 2 | 1024 | 1 | 48 | 0 | 976
index info | CacheMemoryContext | 2 | 1024 | 1 | 48 | 0 | 976
index info | CacheMemoryContext | 2 | 3072 | 2 | 1136 | 2 | 1936
index info | CacheMemoryContext | 2 | 2048 | 2 | 952 | 1 | 1096
index info | CacheMemoryContext | 2 | 2048 | 2 | 760 | 2 | 1288
index info | CacheMemoryContext | 2 | 2048 | 2 | 704 | 3 | 1344
index info | CacheMemoryContext | 2 | 2048 | 2 | 952 | 1 | 1096
index info | CacheMemoryContext | 2 | 1024 | 1 | 16 | 0 | 1008
index info | CacheMemoryContext | 2 | 1024 | 1 | 48 | 0 | 976
index info | CacheMemoryContext | 2 | 2048 | 2 | 760 | 2 | 1288
index info | CacheMemoryContext | 2 | 1024 | 1 | 48 | 0 | 976
index info | CacheMemoryContext | 2 | 1024 | 1 | 48 | 0 | 976
index info | CacheMemoryContext | 2 | 1024 | 1 | 48 | 0 | 976
index info | CacheMemoryContext | 2 | 3072 | 2 | 1136 | 2 | 1936
index info | CacheMemoryContext | 2 | 3072 | 2 | 1136 | 2 | 1936
index info | CacheMemoryContext | 2 | 2048 | 2 | 760 | 2 | 1288
index info | CacheMemoryContext | 2 | 2048 | 2 | 760 | 2 | 1288
index info | CacheMemoryContext | 2 | 1024 | 1 | 48 | 0 | 976
index info | CacheMemoryContext | 2 | 1024 | 1 | 48 | 0 | 976
index info | CacheMemoryContext | 2 | 2048 | 2 | 704 | 3 | 1344
index info | CacheMemoryContext | 2 | 1024 | 1 | 48 | 0 | 976
index info | CacheMemoryContext | 2 | 2048 | 2 | 952 | 1 | 1096
index info | CacheMemoryContext | 2 | 2048 | 2 | 952 | 1 | 1096
index info | CacheMemoryContext | 2 | 1024 | 1 | 48 | 0 | 976
index info | CacheMemoryContext | 2 | 2048 | 2 | 680 | 2 | 1368
index info | CacheMemoryContext | 2 | 2048 | 2 | 760 | 2 | 1288
index info | CacheMemoryContext | 2 | 2048 | 2 | 760 | 2 | 1288
index info | CacheMemoryContext | 2 | 1024 | 1 | 48 | 0 | 976
index info | CacheMemoryContext | 2 | 2048 | 2 | 728 | 1 | 1320
index info | CacheMemoryContext | 2 | 2048 | 2 | 952 | 1 | 1096
index info | CacheMemoryContext | 2 | 2048 | 2 | 952 | 1 | 1096
index info | CacheMemoryContext | 2 | 1024 | 1 | 48 | 0 | 976
index info | CacheMemoryContext | 2 | 2048 | 2 | 624 | 2 | 1424
index info | CacheMemoryContext | 2 | 2048 | 2 | 672 | 3 | 1376
index info | CacheMemoryContext | 2 | 2048 | 2 | 704 | 3 | 1344
index info | CacheMemoryContext | 2 | 1024 | 1 | 48 | 0 | 976
index info | CacheMemoryContext | 2 | 1024 | 1 | 48 | 0 | 976
index info | CacheMemoryContext | 2 | 2048 | 2 | 760 | 2 | 1288
index info | CacheMemoryContext | 2 | 2048 | 2 | 680 | 2 | 1368
index info | CacheMemoryContext | 2 | 1024 | 1 | 48 | 0 | 976
index info | CacheMemoryContext | 2 | 1024 | 1 | 48 | 0 | 976
index info | CacheMemoryContext | 2 | 2048 | 2 | 952 | 1 | 1096
index info | CacheMemoryContext | 2 | 2048 | 2 | 624 | 2 | 1424
index info | CacheMemoryContext | 2 | 1024 | 1 | 16 | 0 | 1008
index info | CacheMemoryContext | 2 | 2048 | 2 | 760 | 2 | 1288
index info | CacheMemoryContext | 2 | 2048 | 2 | 680 | 2 | 1368
index info | CacheMemoryContext | 2 | 2048 | 2 | 952 | 1 | 1096
index info | CacheMemoryContext | 2 | 2048 | 2 | 952 | 1 | 1096
index info | CacheMemoryContext | 2 | 2048 | 2 | 952 | 1 | 1096
index info | CacheMemoryContext | 2 | 3072 | 2 | 1136 | 2 | 1936
index info | CacheMemoryContext | 2 | 1024 | 1 | 48 | 0 | 976
index info | CacheMemoryContext | 2 | 2048 | 2 | 760 | 2 | 1288
index info | CacheMemoryContext | 2 | 2048 | 2 | 952 | 1 | 1096
index info | CacheMemoryContext | 2 | 2048 | 2 | 728 | 1 | 1320
index info | CacheMemoryContext | 2 | 2048 | 2 | 728 | 1 | 1320
index info | CacheMemoryContext | 2 | 1024 | 1 | 48 | 0 | 976
index info | CacheMemoryContext | 2 | 2048 | 2 | 680 | 2 | 1368
index info | CacheMemoryContext | 2 | 2048 | 2 | 952 | 1 | 1096
index info | CacheMemoryContext | 2 | 2048 | 2 | 728 | 1 | 1320
index info | CacheMemoryContext | 2 | 1024 | 1 | 48 | 0 | 976
index info | CacheMemoryContext | 2 | 2048 | 2 | 952 | 1 | 1096
index info | CacheMemoryContext | 2 | 2048 | 2 | 704 | 3 | 1344
index info | CacheMemoryContext | 2 | 1024 | 1 | 16 | 0 | 1008
index info | CacheMemoryContext | 2 | 2048 | 2 | 952 | 1 | 1096
index info | CacheMemoryContext | 2 | 2048 | 2 | 760 | 2 | 1288
index info | CacheMemoryContext | 2 | 1024 | 1 | 16 | 0 | 1008
index info | CacheMemoryContext | 2 | 2048 | 2 | 728 | 1 | 1320
index info | CacheMemoryContext | 2 | 2048 | 2 | 952 | 1 | 1096
index info | CacheMemoryContext | 2 | 2048 | 2 | 952 | 1 | 1096
WAL record construction | TopMemoryContext | 1 | 49768 | 2 | 6368 | 0 | 43400
dynahash | TopMemoryContext | 1 | 8192 | 1 | 2624 | 0 | 5568
MdSmgr | TopMemoryContext | 1 | 8192 | 1 | 7432 | 0 | 760
dynahash | TopMemoryContext | 1 | 8192 | 1 | 560 | 0 | 7632
dynahash | TopMemoryContext | 1 | 104120 | 2 | 2624 | 0 | 101496
ErrorContext | TopMemoryContext | 1 | 8192 | 1 | 7936 | 0 | 256
(121 rows)
2、文本压缩
postgres=# select pglz_compress(repeat(md5(random()::text),1024)); pglz_compress
-------------------------------------------------------------------------------------------------------------------------------------
\x00800040006135663437636166003162353066626137006637356161363639003532316233336139ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f
20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f2
0ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f
20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20e7
(1 row)
3、解压缩
pglz_decompress(bytea)
postgres=# select pglz_compress(repeat(md5(random()::text),8));
pglz_compress
--------------------------------------------------------------------------------------------
\x00010040003236666162656631003262323262636230006462656638616330003536343161383937010f20ce
(1 row)
postgres=# select pglz_decompress('\x00010040003236666162656631003262323262636230006462656638616330003536343161383937010f20ce');
pglz_decompress
------------------------------------------------------------------------------
26fabef12b22bcb0dbef8ac05641a89726fabef12b22bcb0dbef8ac05641a89726fabef12b22bcb0dbef8ac05641a89726fabef12b22bcb0dbef8ac05641a89726fabef12b22bcb0dbef8ac05641a89726fabef12b22bcb0dbef8ac05641a89726fabef12b22bcb0dbef8ac05641a89726fabef12b22
bcb0dbef8ac05641a897
(1 row)
4、查看绑定变量QUERY的PLAN COSE。
postgres=# prepare a (name) as select * from pg_class where relname=$1;
PREPARE
postgres=# select * from pg_cached_plan_source('a');
generic_cost | total_custom_cost | num_custom_plans | force_generic | force_custom
--------------+-------------------+------------------+---------------+--------------
-1 | 0 | 0 | f | f
(1 row)
postgres=# execute a('abc');
relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | relallvisible | reltoastrelid | relhasindex | relisshared | relpersistence | relkind | relnatts | relchecks | relhaso
ids | relhasrules | relhastriggers | relhassubclass | relrowsecurity | relforcerowsecurity | relispopulated | relreplident | relispartition | relrewrite | relfrozenxid | relminmxid | relacl | reloptions | relpartbound
---------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+-------------+-------------+----------------+---------+----------+-----------+--------
----+-------------+----------------+----------------+----------------+---------------------+----------------+--------------+----------------+------------+--------------+------------+--------+------------+--------------
(0 rows)
postgres=# select * from pg_cached_plan_source('a');
generic_cost | total_custom_cost | num_custom_plans | force_generic | force_custom
--------------+-------------------+------------------+---------------+--------------
-1 | 4.61208554676785 | 1 | f | f
(1 row)
postgres=# execute a('abc');
relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | relallvisible | reltoastrelid | relhasindex | relisshared | relpersistence | relkind | relnatts | relchecks | relhaso
ids | relhasrules | relhastriggers | relhassubclass | relrowsecurity | relforcerowsecurity | relispopulated | relreplident | relispartition | relrewrite | relfrozenxid | relminmxid | relacl | reloptions | relpartbound
---------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+-------------+-------------+----------------+---------+----------+-----------+--------
----+-------------+----------------+----------------+----------------+---------------------+----------------+--------------+----------------+------------+--------------+------------+--------+------------+--------------
(0 rows)
postgres=# select * from pg_cached_plan_source('a');
generic_cost | total_custom_cost | num_custom_plans | force_generic | force_custom
--------------+-------------------+------------------+---------------+--------------
-1 | 9.22417109353571 | 2 | f | f
(1 row)
参考
https://github.com/MasaoFujii/pg_cheat_funcs