Re: exposing pg_controldata and pg_config as functions

On 12/23/2015 04:37 PM, Michael Paquier wrote:
> On Thu, Dec 24, 2015 at 2:08 AM, Joe Conway <mail(at)joeconway(dot)com> wrote:
>> 2) Change the pg_controldata to be a bunch of separate functions as
>> suggested by Josh Berkus rather than one SRF.
>
> This looks like a plan, thanks!

As discussed, a completely revamped and split off pg_controldata patch.
Below are the details for those interested.

Comments please.

Joe

===============
What this patch does:
---------------
1) Change NextXID output format from "%u/%u" to "%u:%u"
(see recent hackers thread)
2) Refactor bin/pg_controldata (there should be no visible change to
pg_controldata output)
3) Adds new functions, more or less in line with previous discussions:
* pg_checkpoint_state()
* pg_controldata_state()
* pg_recovery_state()
* pg_init_state()

===============
Missing (TODO once agreement on the above is reached):
---------------
a) documentation
b) catversion bump
c) regression tests

===============
New function detail and sample output:
---------------
postgres=# \x
Expanded display is on.

postgres=# \df pg_*_state
List of functions
-[ RECORD 1 ]-------+----------------------------------------------
Schema | pg_catalog
Name | pg_checkpoint_state
Result data type | record
Argument data types | OUT checkpoint_location pg_lsn,
| OUT prior_location pg_lsn,
| OUT redo_location pg_lsn,
| OUT redo_wal_file text,
| OUT timeline_id integer,
| OUT prev_timeline_id integer,
| OUT full_page_writes boolean,
| OUT next_xid text,
| OUT next_oid oid,
| OUT next_multixact_id xid,
| OUT next_multi_offset xid,
| OUT oldest_xid xid,
| OUT oldest_xid_dbid oid,
| OUT oldest_active_xid xid,
| OUT oldest_multi_xid xid,
| OUT oldest_multi_dbid oid,
| OUT oldest_commit_ts_xid xid,
| OUT newest_commit_ts_xid xid,
| OUT checkpoint_time timestamp with time zone
Type | normal
-[ RECORD 2 ]-------+----------------------------------------------
Schema | pg_catalog
Name | pg_controldata_state
Result data type | record
Argument data types | OUT pg_control_version integer,
| OUT catalog_version_no integer,
| OUT system_identifier bigint,
| OUT pg_control_last_modified
| timestamp with time zone
Type | normal
-[ RECORD 3 ]-------+----------------------------------------------
Schema | pg_catalog
Name | pg_init_state
Result data type | record
Argument data types | OUT max_data_alignment integer,
| OUT database_block_size integer,
| OUT blocks_per_segment integer,
| OUT wal_block_size integer,
| OUT bytes_per_wal_segment integer,
| OUT max_identifier_length integer,
| OUT max_index_columns integer,
| OUT max_toast_chunk_size integer,
| OUT large_object_chunk_size integer,
| OUT bigint_timestamps boolean,
| OUT float4_pass_by_value boolean,
| OUT float8_pass_by_value boolean,
| OUT data_page_checksum_version integer
Type | normal
-[ RECORD 4 ]-------+----------------------------------------------
Schema | pg_catalog
Name | pg_recovery_state
Result data type | record
Argument data types | OUT min_recovery_end_location pg_lsn,
| OUT min_recovery_end_timeline integer,
| OUT backup_start_location pg_lsn,
| OUT backup_end_location pg_lsn,
| OUT end_of_backup_record_required boolean
Type | normal

postgres=# select * from pg_controldata_state();
-[ RECORD 1 ]------------+-----------------------
pg_control_version | 942
catalog_version_no | 201511071
system_identifier | 6233852631805477166
pg_control_last_modified | 2015-12-29 15:32:09-08

postgres=# select * from pg_checkpoint_state();
-[ RECORD 1 ]--------+-------------------------
checkpoint_location | 0/14E8C38
prior_location | 0/14D6340
redo_location | 0/14E8C38
redo_wal_file | 000000010000000000000001
timeline_id | 1
prev_timeline_id | 1
full_page_writes | t
next_xid | 0:574
next_oid | 12407
next_multixact_id | 1
next_multi_offset | 0
oldest_xid | 565
oldest_xid_dbid | 1
oldest_active_xid | 0
oldest_multi_xid | 1
oldest_multi_dbid | 1
oldest_commit_ts_xid | 0
newest_commit_ts_xid | 0
checkpoint_time | 2015-12-29 15:32:02-08

postgres=# select * from pg_recovery_state();
-[ RECORD 1 ]-----------------+----
min_recovery_end_location | 0/0
min_recovery_end_timeline | 0
backup_start_location | 0/0
backup_end_location | 0/0
end_of_backup_record_required | f

postgres=# select * from pg_init_state();
-[ RECORD 1 ]--------------+---------
max_data_alignment | 8
database_block_size | 8192
blocks_per_segment | 131072
wal_block_size | 8192
bytes_per_wal_segment | 16777216
max_identifier_length | 64
max_index_columns | 32
max_toast_chunk_size | 1996
large_object_chunk_size | 2048
bigint_timestamps | t
float4_pass_by_value | t
float8_pass_by_value | t
data_page_checksum_version | 0

--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development