Solved

<command string not enabled>

Posted on 2006-11-18
13
2,075 Views
Last Modified: 2008-01-09
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
Comment
Question by:rstaveley
  • 7
  • 6
13 Comments
 
LVL 22

Expert Comment

by:earth man2
ID: 17974074
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
 
LVL 22

Expert Comment

by:earth man2
ID: 17974080
$ 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
 
LVL 17

Author Comment

by:rstaveley
ID: 17974454
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
 
LVL 17

Author Comment

by:rstaveley
ID: 17974457
> Ia there any clue in the postgres log

I've not figured out how to watch the logs, yet. I'll go RTFM....
0
 
LVL 17

Author Comment

by:rstaveley
ID: 17974481
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
 
LVL 22

Expert Comment

by:earth man2
ID: 17975649
log_min_error_statement = error

will it display statement when you set it manually ?
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 17

Author Comment

by:rstaveley
ID: 17975879
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
 
LVL 22

Expert Comment

by:earth man2
ID: 17975906
can you
ping localhost
0
 
LVL 17

Author Comment

by:rstaveley
ID: 17976107
Tes
0
 
LVL 17

Author Comment

by:rstaveley
ID: 17976110
Yes
0
 
LVL 22

Expert Comment

by:earth man2
ID: 17978159
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
 
LVL 22

Accepted Solution

by:
earth man2 earned 250 total points
ID: 17978180
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
 
LVL 17

Author Comment

by:rstaveley
ID: 18089484
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

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Best database to use for Maps is PostgreSQL. This is an open source database. Comes as a package with most Linux OS. For more info visit the following site: http://www.postgresql.org/ (http://www.postgresql.org/) This requires some add-o…
Many developers have database experience, but are new to PostgreSQL. It has some truly inspiring capabilities. I have several years' experience with Microsoft's SQL Server. When I began working with MySQL, I wanted a quick-reference to MySQL (htt…
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
This video discusses moving either the default database or any database to a new volume.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now