文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

怎么使用PostgreSQL扩展函数

2024-04-02 19:55

关注

这篇文章主要讲解了“怎么使用PostgreSQL扩展函数”,文中的讲解内容简单清晰,易于学习与理解,下面请大家跟着小编的思路慢慢深入,一起来研究和学习“怎么使用PostgreSQL扩展函数”吧!

例子

. ./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)

感谢各位的阅读,以上就是“怎么使用PostgreSQL扩展函数”的内容了,经过本文的学习后,相信大家对怎么使用PostgreSQL扩展函数这一问题有了更深刻的体会,具体使用情况还需要大家实践验证。这里是亿速云,小编将为大家推送更多相关知识点的文章,欢迎关注!

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯