Restructure to generate backend data from pg_catalog.pg_settings

[rustprooflabs]

Details

The current method to add a new version directly uses postgresql.conf to determine defaults. This was a bit awkward from the beginning and has a number drawbacks with maintainability and accuracy. This change should set the stage for a number of subsequent improvements.

Planned approach

Use a query against pg_catalog.pg_settings similar to this example. Create a Python script to dump to csv/json/something to parse back in. This calculates the postgresconf_line for matching current expectations, not sure that will make it to the final version. Also calculating a few bool indicators that may be helpful.

SELECT name, setting, context, source, reset_val, boot_val,
		unit, category,
		name || ' = ' ||  setting AS postgresconf_line,
		short_desc,
		CASE WHEN name LIKE 'lc%'
				THEN True
			WHEN name IN ('application_name', 'TimeZone')
				THEN True
			ELSE False
			END AS frequent_override,
		CASE WHEN boot_val = reset_val THEN True 
			ELSE False 
			END AS system_default,
		CASE WHEN reset_val = setting THEN False 
			ELSE True
			END AS session_override
	FROM pg_catalog.pg_settings
;

Possible future improvements

• Handle case when a GUC type is different Handle case when a GUC type is different #7
• Display differences between server config (postgresql.conf) & user config (SET TimeZone = 'America/Denver';)
• Allow for extension specific config, e.g. pg_stat_statements, Timescale. <-- Should be category='Customized Options'
• Allow for config specific to Pg variants, e.g AWS RDS, MS Azure, etc.

[rustprooflabs]

CREATE SCHEMA pgconfig;

DROP VIEW IF EXISTS pgconfig.settings;
CREATE VIEW pgconfig.settings AS
SELECT name, setting, context, source, reset_val, boot_val,
        unit, category,
        name || ' = ' ||  setting AS postgresconf_line,
        name || ' = ' || boot_val AS default_config_line,
        short_desc,
        CASE WHEN name LIKE 'lc%'
                THEN True
            WHEN name IN ('application_name', 'TimeZone')
                THEN True
            ELSE False
            END AS frequent_override,
        CASE WHEN boot_val = reset_val THEN True 
            ELSE False 
            END AS system_default,
        CASE WHEN reset_val = setting THEN False 
            ELSE True
            END AS session_override, 
        pending_restart
    FROM pg_catalog.pg_settings
;


SELECT postgresconf_line, *
    FROM pgconfig.settings
    WHERE default_config_line IS NOT NULL
        AND NOT frequent_override
;

[rustprooflabs]

Done via #17