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@joeconway.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