Re: exposing pg_controldata and pg_config as functions

On 01/17/2016 04:10 PM, Joe Conway wrote:
> On 01/16/2016 06:02 AM, Michael Paquier wrote:
>> On Wed, Dec 30, 2015 at 9:08 AM, Joe Conway <mail(at)joeconway(dot)com> wrote:
>>> 3) Adds new functions, more or less in line with previous discussions:
>>> * pg_checkpoint_state()
>>> * pg_controldata_state()
>>> * pg_recovery_state()
>>> * pg_init_state()
>>
>> Taking the opposite direction of Josh upthread, why is this split
>> actually necessary? Isn't the idea to provide a SQL interface of what
>> pg_controldata shows? If this split proves to be useful, shouldn't we
>> do it as well for pg_controldata?
>
> The last discussion moved strongly in the direction of separate
> functions, and that being different from pg_controldata was not a bad
> thing. That said, I'm still of the opinion that there are legitimate
> reasons to want the command line pg_controldata and the SQL functions to
> produce equivalent, if not identical, results. I just wish we could get
> a clear consensus one way or the other.

I've assumed that we are sticking with the separate functions. As such,
here is a rebased patch, with documentation and other fixes such as
Copyright year, Mkvcbuild support, and some cruft removal.

>> I think that those functions should be superuser-only. They provide
>> information about the system globally.
>
> The tail of this thread seems to be headed away from this direction.
> I'll take another look and propose something concrete.

I've looked at existing functions that seem similar, and as far as I can
see none are superuser-only. I'm certainly happy to make them so if
that's the consensus, but currently they are wide open. Opinions?

For convenience in answering that question, here is what information is
included in the output of each function (\df so you can see the data
types, plus SELECT output for a more readable example):

8<-------------------------
postgres=# \x
Expanded display is on.

postgres=# \df pg_checkpoint_state
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

postgres=# select * from pg_checkpoint_state();
-[ RECORD 1 ]--------+-------------------------
checkpoint_location | 0/14CD368
prior_location | 0/14CD0D0
redo_location | 0/14CD368
redo_wal_file | 000000010000000000000001
timeline_id | 1
prev_timeline_id | 1
full_page_writes | t
next_xid | 0:576
next_oid | 12415
next_multixact_id | 1
next_multi_offset | 0
oldest_xid | 568
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 | 2016-02-19 18:44:51-08

postgres=# \df pg_controldata_state
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

postgres=# select * from pg_controldata_state();
-[ RECORD 1 ]------------+-----------------------
pg_control_version | 942
catalog_version_no | 201602171
system_identifier | 6253198751269127743
pg_control_last_modified | 2016-02-19 18:44:58-08

postgres=# \df pg_init_state
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

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

postgres=# \df 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

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
8<-------------------------

Is there general consensus that we want this feature, and that we want
it in this form? Any other comments?

Joe

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