创意电子

标题: PostgreSQL如何获取系统视图/函数对应的源码 [打印本页]

作者: PostgreSQLChina 时间: 2021-9-18 10:36
标题: PostgreSQL如何获取系统视图/函数对应的源码
作者：吴聪

我们在使用PostgreSQL的时候大概经常会碰到如许的情况，对于某个体系视图的某个字段不知道是干啥的，看了官方文档后也一脸懵逼，不知道是啥意思。

这个时候如果我们可以看到该视图或者该字段对应的源码，那么想要理解起来就会方便多了。

以pg_stat_database这个视图为例，它的两个字段tup_returned和tup_fetched的官方文档表明如下：

tup_returned：这个数据库中查询返回的行数；
tup_fetched：这个数据库中查询获取的行数。

看完估计你也和我一样懵逼，返回、获取有啥差别？看这个字面表明压根就不明确啥意思啊，那我们该怎么查看其相关源码呢？

对于这种情况，建议按照下面方式一步步来获取：

1、查看视图对应的SQL语句

pg13@cnndr4pptliot-> psql bill bill -Epsql (13beta3)Type 'help' for help.bill@bill=>\d+ pg_stat_database                                  View 'pg_catalog.pg_stat_database'       Column       |          Type          | Collation | Nullable | Default | Storage | Description-----------------------+--------------------------+-----------+----------+---------+---------+------------- datid                | oid                   |          |       |       | plain | datname             | name                   |          |       |       | plain | numbackends          | integer                |          |       |       | plain | xact_commit          | bigint                |          |       |       | plain | xact_rollback       | bigint                |          |       |       | plain | blks_read          | bigint                |          |       |       | plain | blks_hit             | bigint                |          |       |       | plain | tup_returned       | bigint                |          |       |       | plain | tup_fetched          | bigint                |          |       |       | plain | tup_inserted       | bigint                |          |       |       | plain | tup_updated          | bigint                |          |       |       | plain | tup_deleted          | bigint                |          |       |       | plain | conflicts          | bigint                |          |       |       | plain | temp_files          | bigint                |          |       |       | plain | temp_bytes          | bigint                |          |       |       | plain | deadlocks          | bigint                |          |       |       | plain | checksum_failures    | bigint                |          |       |       | plain | checksum_last_failure | timestamp with time zone |          |       |       | plain | blk_read_time       | double precision       |          |       |       | plain | blk_write_time       | double precision       |          |       |       | plain | stats_reset          | timestamp with time zone |          |       |       | plain |View definition: SELECT d.oid AS datid, d.datname,       CASE          WHEN d.oid = 0::oid THEN 0          ELSE pg_stat_get_db_numbackends(d.oid)       END AS numbackends, pg_stat_get_db_xact_commit(d.oid) AS xact_commit, pg_stat_get_db_xact_rollback(d.oid) AS xact_rollback, pg_stat_get_db_blocks_fetched(d.oid) - pg_stat_get_db_blocks_hit(d.oid) AS blks_read, pg_stat_get_db_blocks_hit(d.oid) AS blks_hit, pg_stat_get_db_tuples_returned(d.oid) AS tup_returned, pg_stat_get_db_tuples_fetched(d.oid) AS tup_fetched, pg_stat_get_db_tuples_inserted(d.oid) AS tup_inserted, pg_stat_get_db_tuples_updated(d.oid) AS tup_updated, pg_stat_get_db_tuples_deleted(d.oid) AS tup_deleted, pg_stat_get_db_conflict_all(d.oid) AS conflicts, pg_stat_get_db_temp_files(d.oid) AS temp_files, pg_stat_get_db_temp_bytes(d.oid) AS temp_bytes, pg_stat_get_db_deadlocks(d.oid) AS deadlocks, pg_stat_get_db_checksum_failures(d.oid) AS checksum_failures, pg_stat_get_db_checksum_last_failure(d.oid) AS checksum_last_failure, pg_stat_get_db_blk_read_time(d.oid) AS blk_read_time, pg_stat_get_db_blk_write_time(d.oid) AS blk_write_time, pg_stat_get_db_stat_reset_time(d.oid) AS stats_reset FROM ( SELECT 0 AS oid,          NULL::name AS datname       UNION ALL       SELECT pg_database.oid,          pg_database.datname          FROM pg_database) d;

如许我们就知道了tup_returned和tup_fetched分别对应的是pg_stat_get_db_tuples_returned和pg_stat_get_db_tuples_fetched这两个函数。

2、查看体系函数对应的源码函数

bill@bill=>\df+ pg_stat_get_db_tuples_returnedList of functions-[ RECORD 1 ]-------+-----------------------------------------Schema             | pg_catalogName             | pg_stat_get_db_tuples_returnedResult data type | bigintArgument data types | oidType             | funcVolatility       | stableParallel          | restrictedOwner             | postgresSecurity          | invokerAccess privileges |Language          | internalSource code       | pg_stat_get_db_tuples_returnedDescription       | statistics: tuples returned for database

以pg_stat_get_db_tuples_returned为例，其对应的源码函数是pg_stat_get_db_tuples_returned。

3、分析相关源码

既然知道了对应的源码函数那就简单了，直接去搜索就完事了。

Datumpg_stat_get_tuples_returned(PG_FUNCTION_ARGS){ Oid          relid = PG_GETARG_OID(0); int64       result; PgStat_StatTabEntry *tabentry; if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)       result = 0; else       result = (int64) (tabentry->tuples_returned); PG_RETURN_INT64(result);}

接着逐步分析相关的源码，查看相关注释就可以了，我们可以得出结论：

tuples_returned,：直接扫描表的tuple行数 + 直接扫描索引的索引条目数。
tuples_fetched：通过索引指向的tid去回表访问的tuple行数。

参考链接：
http://www.postgres.cn/docs/13/monitoring-stats.html#MONITORING-PG-STAT-DATABASE-VIEW

欢迎光临创意电子 (https://wxcydz.cc/)