Thread: Query to help in debugging
On Fri, Jan 18, 2013 at 07:35:43AM -0800, bricklen wrote:
> Please post the results of the following query:
>
> SELECT 'version'::text AS "name",
> version() AS "current_setting"
> UNION ALL
> SELECT name,
> current_setting(name)
> FROM pg_settings
> WHERE NOT source='default'AND NOT name IN
>
('config_file','data_directory','hba_file','ident_file','log_timezone','DateStyle','lc_messages','lc_monetary','lc_numeric','lc_time','timezone_abbreviations','default_text_search_config','application_name','transaction_deferrable','transaction_isolation','transaction_read_only');
Above is a very creative query that was posted to the bugs list by
bricklen. It reports all non-default server settings, plus version().
Here is a realigned version:
SELECT 'version'::text AS name, version() AS current_settingUNION ALLSELECT name, current_setting(name)FROM
pg_settingsWHERE source != 'default' AND name NOT IN(
'config_file','data_directory','hba_file','ident_file','log_timezone',
'DateStyle','lc_messages','lc_monetary','lc_numeric','lc_time',
'timezone_abbreviations','default_text_search_config','application_name',
'transaction_deferrable','transaction_isolation','transaction_read_only');
and the output from my server with an unmodified postgresql.conf:
name |
current_setting-----------------+-------------------------------------------------------------------------------------------------
version | PostgreSQL 9.3devel on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.4.5-8) 4.4.5, 64-bit
client_encoding| UTF8 lc_collate | en_US.UTF-8 lc_ctype | en_US.UTF-8 max_connections | 100 max_stack_depth
|2MB server_encoding | UTF8 shared_buffers | 128MB TimeZone | US/Eastern wal_buffers | 4MB(10 rows)
I am wondering if we should make this query more widely used, perhaps by
putting it in our docs about reporting bugs, or on our website.
-- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB
http://enterprisedb.com
+ It's impossible for everything to be true. +
Bruce Momjian wrote: > I am wondering if we should make this query more widely used, perhaps by > putting it in our docs about reporting bugs, or on our website. http://wiki.postgresql.org/wiki/Server_Configuration http://wiki.postgresql.org/wiki/Guide_to_reporting_problems#Things_you_need_to_mention_in_problem_reports Feel free to make any adjustments you feel are needed. :-) -Kevin
On Sat, Jan 19, 2013 at 11:20:19AM -0500, Kevin Grittner wrote: > Bruce Momjian wrote: > > > I am wondering if we should make this query more widely used, perhaps by > > putting it in our docs about reporting bugs, or on our website. > > http://wiki.postgresql.org/wiki/Server_Configuration > > http://wiki.postgresql.org/wiki/Guide_to_reporting_problems#Things_you_need_to_mention_in_problem_reports > > Feel free to make any adjustments you feel are needed. :-) Oh, so we already have it documnted. Great. I adjusted it slightly to be clearer. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
Bruce Momjian <bruce@momjian.us> writes:
> I am wondering if we should make this query more widely used, perhaps by
> putting it in our docs about reporting bugs, or on our website.
I find the manual exclusion list to be poor style, and not at all
future-proof. Maybe we could use
select name, setting, source from pg_settings
where source not in ('default', 'override');
This would print a few not-all-that-interesting settings made by initdb,
but not having to adjust the exclusion list for different versions is
easily worth that. I think the source column is potentially useful when
we're casting this type of fishing net, too.
regards, tom lane
Tom Lane wrote:
> I find the manual exclusion list to be poor style, and not at all
> future-proof. Maybe we could use
>
> select name, setting, source from pg_settings
> where source not in ('default', 'override');
>
> This would print a few not-all-that-interesting settings made by initdb,
> but not having to adjust the exclusion list for different versions is
> easily worth that. I think the source column is potentially useful when
> we're casting this type of fishing net, too.
Done.
-Kevin
On Sat, Jan 19, 2013 at 12:58:35PM -0500, Kevin Grittner wrote:
> Tom Lane wrote:
>
> > I find the manual exclusion list to be poor style, and not at all
> > future-proof. Maybe we could use
> >
> > select name, setting, source from pg_settings
> > where source not in ('default', 'override');
> >
> > This would print a few not-all-that-interesting settings made by initdb,
> > but not having to adjust the exclusion list for different versions is
> > easily worth that. I think the source column is potentially useful when
> > we're casting this type of fishing net, too.
>
> Done.
Here is my very wide output:
name | current_setting
|
source----------------------------+----------------------------------------------------------------------------------------------+----------------------
version | PostgreSQL 9.3devel on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.4.5-8) 4.4.5,
64-.|version() |.bit
| application_name | psql
| client client_encoding | UTF8
| client DateStyle | ISO, MDY
| configuration file default_text_search_config | pg_catalog.english
| configuration file lc_messages |
en_US.UTF-8 | configuration file
lc_monetary | en_US.UTF-8
| configuration file lc_numeric | en_US.UTF-8
| configuration file lc_time | en_US.UTF-8
| configuration file log_timezone | US/Eastern
| configuration file max_connections | 100
| configuration file
max_stack_depth | 2MB
| environment variable shared_buffers | 128MB
| configuration file TimeZone | US/Eastern
| configuration file
Is there an easy way to wrap the 'version' value to a 40-character width?
-- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB
http://enterprisedb.com
+ It's impossible for everything to be true. +
Bruce Momjian <bruce@momjian.us> writes:
>> Tom Lane wrote:
>>> select name, setting, source from pg_settings
>>> where source not in ('default', 'override');
> Here is my very wide output:
Why are you insisting on cramming version() into this? It could
just as easily be a different query.
regards, tom lane
On Sat, Jan 19, 2013 at 03:29:36PM -0500, Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> >> Tom Lane wrote:
> >>> select name, setting, source from pg_settings
> >>> where source not in ('default', 'override');
>
> > Here is my very wide output:
>
> Why are you insisting on cramming version() into this? It could
> just as easily be a different query.
I am fine with that:
SELECT version();SELECT name, current_setting(name), sourceFROM pg_settingsWHERE source NOT IN ('default',
'override');
Output:
test=> SELECT version();
version-------------------------------------------------------------------------------------------------PostgreSQL
9.3develon x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.4.5-8) 4.4.5, 64-bit(1 row)test=> SELECT name,
current_setting(name),sourcetest-> FROM pg_settingstest-> WHERE source NOT IN ('default', 'override');
name | current_setting |
source----------------------------+--------------------+----------------------application_name | psql
| client client_encoding | UTF8 | client DateStyle | ISO, MDY
|configuration file default_text_search_config | pg_catalog.english | configuration file lc_messages |
en_US.UTF-8 | configuration file lc_monetary | en_US.UTF-8 | configuration file lc_numeric
| en_US.UTF-8 | configuration file lc_time | en_US.UTF-8 | configuration
filelog_timezone | US/Eastern | configuration file max_connections | 100
| configuration file max_stack_depth | 2MB | environment variable shared_buffers
|128MB | configuration file TimeZone | US/Eastern | configuration file(13 rows)
-- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB
http://enterprisedb.com
+ It's impossible for everything to be true. +
Bruce Momjian wrote: >> Why are you insisting on cramming version() into this? It could >> just as easily be a different query. > > I am fine with that: Done. -Kevin