• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2499
  • Last Modified:

<command string not enabled>

I can't seem to see any commands in the stats tables despite setting stats_command_string = true. I can't stop it from saying ' <command string not enabled>'. I am running as the superuser.
0
rstaveley
Asked:
rstaveley
  • 7
  • 6
1 Solution
 
earth man2Commented:
Ia there any clue in the postgres log.  stats_command_string can be reset to off if the stats collector encounters a problem.  Like not being able to resolve localhost or socket errors.
0
 
earth man2Commented:
$ psql treacle
Welcome to psql 8.2beta3, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

treacle=# show stats_command_string;
 stats_command_string
----------------------
 on
(1 row)
0
 
rstaveleyAuthor Commented:
What do you make of this?

(1) Starting with stats_command_string disabled in .conf and using set (and failing)

postgresql.conf
--------8<--------
# - Query/Index Statistics Collector -

stats_start_collector = true
#stats_command_string = false
#stats_block_level = false
stats_row_level = true
#stats_reset_on_server_start = true
--------8<--------

Some interaction:
--------8<--------
root@seseiaa3:~# su - postgres
postgres@seseiaa3:~$ psql ibs
Welcome to psql 8.1.4, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

ibs=# show stats_command_string;
 stats_command_string
----------------------
 off
(1 row)

ibs=# \set stats_command_string on
ibs=# show stats_command_string;
 stats_command_string
----------------------
 off
(1 row)

ibs=# \set
VERSION = 'PostgreSQL 8.1.4 on i386-pc-linux-gnu, compiled by GCC cc (GCC) 3.3.5 (Debian 1:3.3.5-13)'
AUTOCOMMIT = 'on'
VERBOSITY = 'default'
PROMPT1 = '%/%R%# '
PROMPT2 = '%/%R%# '
PROMPT3 = '>> '
DBNAME = 'ibs'
USER = 'postgres'
HOST = '/var/run/postgresql'
PORT = '5432'
ENCODING = 'UTF8'
HISTSIZE = '500'
stats_command_string = 'on'

(1 row)
--------8<--------

(2) Starting with it enabled in the .conf file

--------8<--------
# - Query/Index Statistics Collector -

stats_start_collector = true
stats_command_string = true
#stats_block_level = false
stats_row_level = true
#stats_reset_on_server_start = true
--------8<--------

--------8<--------
root@seseiaa3:~# /etc/init.d/postgresql restart
Restarting PostgreSQL 8.1 database server: main.
postgres@seseiaa3:~$ psql ibs
Welcome to psql 8.1.4, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

ibs=# show stats_command_string;
 stats_command_string
----------------------
 off
(1 row)
--------8<--------

It doesn't seem to want to play ball.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
rstaveleyAuthor Commented:
> Ia there any clue in the postgres log

I've not figured out how to watch the logs, yet. I'll go RTFM....
0
 
rstaveleyAuthor Commented:
There is nothing to indicate why this might be happening in /var/log/postgresql/postgresql-8.1-main.log

Do I need to enable something to get it to log?

--------8<--------
#---------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#---------------------------------------------------------------------------

# - Syslog -

syslog = 0                      # range 0-2; 0=stdout; 1=both; 2=syslog
#syslog_facility = 'LOCAL0'
#syslog_ident = 'postgres'

# - When to Log -

#client_min_messages = notice   # Values, in order of decreasing detail:
                                #   debug5, debug4, debug3, debug2, debug1,
                                #   log, info, notice, warning, error

#log_min_messages = notice      # Values, in order of decreasing detail:
                                #   debug5, debug4, debug3, debug2, debug1,
                                #   info, notice, warning, error, log, fatal,
                                #   panic

#log_error_verbosity = default   # terse, default, or verbose messages

#log_min_error_statement = panic # Values in order of increasing severity:
                                 #   debug5, debug4, debug3, debug2, debug1,
                                 #   info, notice, warning, error, panic(off)

#log_min_duration_statement = -1 # Log all statements whose
                                 # execution time exceeds the value, in
                                 # milliseconds.  -1 disables. Zero logs
                                 # all statements.

silent_mode = false              # DO NOT USE without Syslog!

# - What to Log -

#debug_print_parse = false
#debug_print_rewritten = false
#debug_print_plan = false
#debug_pretty_print = false
log_connections = true
#log_duration = false
log_pid = true
#log_statement = false
log_timestamp = true
#log_hostname = false
#log_source_port = false
--------8<--------
0
 
earth man2Commented:
log_min_error_statement = error

will it display statement when you set it manually ?
0
 
rstaveleyAuthor Commented:
Alas no. Setting it interactively I see...

--------8<--------
ibs=# \set log_min_error_statement error
ibs=# show log_min_error_statement;
 log_min_error_statement
-------------------------
 panic
(1 row)

ibs=# \set stats_command_string on
ibs=# \set
VERSION = 'PostgreSQL 8.1.4 on i386-pc-linux-gnu, compiled by GCC cc (GCC) 3.3.5 (Debian 1:3.3.5-13)'
AUTOCOMMIT = 'on'
VERBOSITY = 'default'
PROMPT1 = '%/%R%# '
PROMPT2 = '%/%R%# '
PROMPT3 = '>> '
DBNAME = 'ibs'
USER = 'rob'
HOST = '/var/run/postgresql'
PORT = '5432'
ENCODING = 'UTF8'
HISTSIZE = '500'
log_min_error_statement = 'error'
stats_command_string = 'on'
ibs=# show stats_command_string;
 stats_command_string
----------------------
 off
(1 row)

ibs=# select * from pg_stat_activity;
  datid  | datname | procpid | usesysid | usename |        current_query         | query_start |         backend_start         | client_addr  | client_port
---------+---------+---------+----------+---------+------------------------------+-------------+-------------------------------+--------------+-------------
 1530956 | ibs     |   25776 |    16385 | rob     | <command string not enabled> |             | 2006-11-19 21:17:49.113316+00 |              |          -1
 1530956 | ibs     |   25643 |    16385 | rob     | <command string not enabled> |             | 2006-11-19 19:44:50.021055+00 | 80.175.31.50 |       44557
 1530956 | ibs     |   25575 |    16385 | rob     | <command string not enabled> |             | 2006-11-19 18:40:44.133451+00 | 80.175.31.50 |       33084
 1530956 | ibs     |   25567 |    16385 | rob     | <command string not enabled> |             | 2006-11-19 18:33:58.282051+00 | 80.175.31.50 |       40125
 1530956 | ibs     |   25565 |    16385 | rob     | <command string not enabled> |             | 2006-11-19 18:32:06.033148+00 | 80.175.31.50 |       47585
(5 rows)
--------8<--------

I see nothing informative in /var/log/postgresql/postgresql-8.1-main.log
0
 
earth man2Commented:
can you
ping localhost
0
 
rstaveleyAuthor Commented:
Tes
0
 
rstaveleyAuthor Commented:
Yes
0
 
earth man2Commented:
when the statistic collector is started up there are lots of networking operations performed to set up a socket if these fail you should get the following error message in your log file.

disabling statistics collector for lack of working socket
0
 
earth man2Commented:
the way I would resolve this is to run postmaster in a debugger with a breakpoint set in

file: src/postmaster/pgstat.c   function: pgstat_init()
0
 
rstaveleyAuthor Commented:
Sorry I missed these comments when you posted then earthman2. I'll certainly try reproducing it on a DEV box with a debugger. Many thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now