pg_stat_transaction patch

Background

==========

The views pg_stat_user_tables and pg_stat_user_functions shows statistics on tables and functions.

The underlying functions named pg_stat_get_* fetches recent data from the statistics collector, and returns the requested value for the given "oid" (i.e. "tableid/relationid" or "functionid").

In the end of each transaction[1], the collected statistics are sent to the statistics collector[2].

[1] upon COMMIT/ROLLBACK, or a bit later (the report frequency is controlled by the PGSTAT_STAT_INTERVAL setting, default value is 500 ms)

[2] if you do a ps aux, it is the process named "postgres: stats collector process"

Problem

=======

Within a current transaction, there was no way of accessing the internal data structures which contains the so far collected statistics.

I wanted to check exactly what data changes my functions made and what functions they called, without having to commit the transaction

and without mixing the statistics data with all the other simultaneously running transactions.

Solution

========

I have exported get accessor methods to the internal data structure containing so far collected statistics for the current transaction.

I have also exported the method pgstat_report_stat to make it possible to force a "report and reset" of the so far collected statistics.

This was necessary to avoid not-yet-reported statistics for a previous transaction to affect the current transaction.

I used the unused_oids script to find unused oids and choosed the range between 3030-3044 for the new functions.

Functions

=========

test=# \df+ pg_catalog.pg_stat_get_transaction_*

                                                                                                                        List of functions

   Schema   |                    Name                    | Result data type | Argument data types |  Type  | Volatility | Owner | Language |                Source code                 |                               Description                               

------------+--------------------------------------------+------------------+---------------------+--------+------------+-------+----------+--------------------------------------------+-------------------------------------------------------------------------

 pg_catalog | pg_stat_get_transaction_blocks_fetched     | bigint           | oid                 | normal | stable     | joel  | internal | pg_stat_get_transaction_blocks_fetched     | statistics: number of blocks fetched in current transaction

 pg_catalog | pg_stat_get_transaction_blocks_hit         | bigint           | oid                 | normal | stable     | joel  | internal | pg_stat_get_transaction_blocks_hit         | statistics: number of blocks found in cache in current transaction

 pg_catalog | pg_stat_get_transaction_dead_tuples        | bigint           | oid                 | normal | stable     | joel  | internal | pg_stat_get_transaction_dead_tuples        | statistics: number of dead tuples in current transaction

 pg_catalog | pg_stat_get_transaction_function_calls     | bigint           | oid                 | normal | stable     | joel  | internal | pg_stat_get_transaction_function_calls     | statistics: number of function calls in current transaction

 pg_catalog | pg_stat_get_transaction_function_self_time | bigint           | oid                 | normal | stable     | joel  | internal | pg_stat_get_transaction_function_self_time | statistics: self execution time of function in current transaction

 pg_catalog | pg_stat_get_transaction_function_time      | bigint           | oid                 | normal | stable     | joel  | internal | pg_stat_get_transaction_function_time      | statistics: execution time of function in current transaction

 pg_catalog | pg_stat_get_transaction_live_tuples        | bigint           | oid                 | normal | stable     | joel  | internal | pg_stat_get_transaction_live_tuples        | statistics: number of live tuples in current transaction

 pg_catalog | pg_stat_get_transaction_numscans           | bigint           | oid                 | normal | stable     | joel  | internal | pg_stat_get_transaction_numscans           | statistics: number of scans done for table/index in current transaction

 pg_catalog | pg_stat_get_transaction_tuples_deleted     | bigint           | oid                 | normal | stable     | joel  | internal | pg_stat_get_transaction_tuples_deleted     | statistics: number of tuples deleted in current transaction

 pg_catalog | pg_stat_get_transaction_tuples_fetched     | bigint           | oid                 | normal | stable     | joel  | internal | pg_stat_get_transaction_tuples_fetched     | statistics: number of tuples fetched by idxscan in current transaction

 pg_catalog | pg_stat_get_transaction_tuples_hot_updated | bigint           | oid                 | normal | stable     | joel  | internal | pg_stat_get_transaction_tuples_hot_updated | statistics: number of tuples hot updated in current transaction

 pg_catalog | pg_stat_get_transaction_tuples_inserted    | bigint           | oid                 | normal | stable     | joel  | internal | pg_stat_get_transaction_tuples_inserted    | statistics: number of tuples inserted in current transaction

 pg_catalog | pg_stat_get_transaction_tuples_returned    | bigint           | oid                 | normal | stable     | joel  | internal | pg_stat_get_transaction_tuples_returned    | statistics: number of tuples read by seqscan in current transaction

 pg_catalog | pg_stat_get_transaction_tuples_updated     | bigint           | oid                 | normal | stable     | joel  | internal | pg_stat_get_transaction_tuples_updated     | statistics: number of tuples updated in current transaction

(14 rows)

I also had to create a new internal function, "get_funcstat_entry".

This function find or create a PgStat_BackendFunctionEntry entry for the given oid (functionid).

The name and behaviour is similar to the existing function "get_tabstat_entry".

System views

============

pg_stat_transaction_tables - shows so far collected table statistics for the current transaction (almost identical structure as pg_stat_user_tables, but lacks the last_* columns)

pg_stat_transaction_functions - shows so far collected function statistics for the current transaction (identical structure as pg_stat_user_functions)

Test/Use case

=============

Patched files

=============

/doc/src/sgml/monitoring.sgml

/src/backend/catalog/system_views.sql

/src/backend/postmaster/pgstat.c

/src/backend/utils/adt/pgstatfuncs.c

/src/include/catalog/pg_proc.h

/src/include/pgstat.h