Обсуждение: debugging SQL statements

Usually, I see the SQL statements which my application emits appear in
syslog:


Nov 28 06:47:09 guru postgres[5753]: [4-1] LOG:  00000: statement:
SELECT mac, ip_addr, vlan, ad_ou, os_hostname, dns_hostname,
Nov 28 06:47:09 guru postgres[5753]: [4-2]                version_name,
snmp_sys_descr, snmp_sys_objectid, wireless_ap,
Nov 28 06:47:09 guru postgres[5753]: [4-3]
switch_name, slot, port, first_seen, last_seen, last_updated FROM hosts
LEFT JOIN switch_ports ON
Nov 28 06:47:09 guru postgres[5753]: [4-4]  hosts.switch_port =
switch_ports.portid LEFT JOIN switches ON switch_ports.switch =
switches.switchid LEFT JOIN os_versions ON
Nov 28 06:47:09 guru postgres[5753]: [4-5]  hosts.osver =
os_versions.versionid WHERE ad_ou ILIKE '%adm%' ORDER BY ip_addr ASC
Nov 28 06:47:09 guru postgres[5753]: [4-6] LOCATION:  exec_simple_query,
postgres.c:848


And when I fumble and emit invalid SQL, postgres logs an error:

Nov 28 06:17:00 guru postgres[5436]: [4-1] ERROR:  22P02: invalid input
syntax for integer: "*"
Nov 28 06:17:00 guru postgres[5436]: [4-2] LOCATION:  pg_atoi, numutils.c:64
Nov 28 06:17:00 guru postgres[5436]: [4-3] STATEMENT:  SELECT mac,
ip_addr, vlan, ad_ou, os_hostname, dns_hostname, ^M
Nov 28 06:17:00 guru postgres[5436]: [4-4]
version_name, snmp_sys_descr, snmp_sys_objectid, wireless_ap, ^M
Nov 28 06:17:00 guru postgres[5436]: [4-5]
switch_name, slot, port, first_seen, last_seen, last_updated FROM hosts
LEFT JOIN switch_ports ON
Nov 28 06:17:00 guru postgres[5436]: [4-6]  hosts.switch_port =
switch_ports.portid LEFT JOIN switches ON switch_ports.switch =
switches.switchid LEFT JOIN os_versions ON
Nov 28 06:17:00 guru postgres[5436]: [4-7]  hosts.osver =
os_versions.versionid WHERE vlan = '*' ORDER BY ip_addr ASC


I'm working on a statement now which is valid -- I can copy and paste
this into the database prompt and return results:


soma=# SELECT mac, ip_addr, vlan, ad_ou, os_hostname, dns_hostname,
version_name, snmp_sys_descr, snmp_sys_objectid, wireless_ap,
switch_name, slot, port, first_seen, last_seen, last_updated FROM hosts
LEFT JOIN switch_ports ON hosts.switch_port = switch_ports.portid LEFT
JOIN switches ON switch_ports.switch = switches.switchid LEFT JOIN
os_versions ON hosts.osver = os_versions.versionid WHERE ad_ou IS NOT
NULL ORDER BY ip_addr ASC;

         mac        |     ip_addr     | vlan |   ad_ou    |
os_hostname   |
dns_hostname     |                                  version_name
                            |                      snmp_sys_descr
                |
      snmp_sys_objectid      | wireless_ap |   switch_name    | slot |
port |
  first_seen      |      last_seen      |    last_updated
-------------------+-----------------+------+------------+-----------------+----
-----------------+--------------------------------------------------------------
-------------------+----------------------------------------------------------+-
----------------------------+-------------+------------------+------+------+----
-----------------+---------------------+---------------------
  00:21:55:04:7d:b8 | 10.5.31.212     | 2030 | ADM        |
SEP002155047DB8 | dhcp-voip-031212    | Cisco IP Phone 7941 or 7961

[...]


But when my application emits this statement, postgres logs neither the
SQL query nor an error message ... just the usual connect/disconnect
stuff.  And the result set, of course, is empty.

Nov 28 06:53:28 guru postgres[5788]: [2-1] LOG:  00000: connection
received: host=localhost port=52347
Nov 28 06:53:28 guru postgres[5788]: [2-2] LOCATION:  BackendInitialize,
postmaster.c:3027
Nov 28 06:53:28 guru postgres[5788]: [3-1] LOG:  00000: connection
authorized: user=reader database=soma
Nov 28 06:53:28 guru postgres[5788]: [3-2] LOCATION:  BackendInitialize,
postmaster.c:3097
Nov 28 06:53:28 guru postgres[5788]: [4-1] LOG:  00000: disconnection:
session time: 0:00:00.012 user=reader database=soma host=localhost
port=52347
Nov 28 06:53:28 guru postgres[5788]: [4-2] LOCATION:
log_disconnections, postgres.c:4041

Now, maybe my application is fumbling the statement and introducing a
syntax error ... but then why wouldn't I see an error message?

Under what circumstances will postgres log nothing, when handed a SELECT
statement?

--sk

Stuart Kendrick
FHCRC

Stuart Kendrick <skendric@fhcrc.org> writes:
> Usually, I see the SQL statements which my application emits appear in
> syslog:

You have not shown us your log-related settings, but it looks like the
log entries you're mentioning here are for duration logging, which is
only done on successful completion.

> But when my application emits this statement, postgres logs neither the
> SQL query nor an error message ... just the usual connect/disconnect
> stuff.  And the result set, of course, is empty.

Perhaps you have log_min_error_statement set to a funny value?  But I'd
suspect there is some logic error on the client side such that you're
never actually submitting the statement.

            regards, tom lane

Hi Tom,

I have log_min_error_statement set to 'info'

But, right you are.  I put Wireshark on the job and yes, I'm producing a
mangled query, which the postgres driver for my application never
actually submits.

Thank you,

--sk

On 11/28/2010 8:58 AM, Tom Lane wrote:
> Stuart Kendrick<skendric@fhcrc.org>  writes:
>> Usually, I see the SQL statements which my application emits appear in
>> syslog:
>
> You have not shown us your log-related settings, but it looks like the
> log entries you're mentioning here are for duration logging, which is
> only done on successful completion.
>
>> But when my application emits this statement, postgres logs neither the
>> SQL query nor an error message ... just the usual connect/disconnect
>> stuff.  And the result set, of course, is empty.
>
> Perhaps you have log_min_error_statement set to a funny value?  But I'd
> suspect there is some logic error on the client side such that you're
> never actually submitting the statement.
>
>             regards, tom lane

  I have a PostgreSQL database which was created, and presumably
installed, when I installed TimeTrex under Ubuntu.

Since I now want to upgrade to the latest version of Timetrex I thought
it would be a good ida to first back up mu database but attempting to
use the psql utility I get this:

sudo psql
psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"

 From this i assume a non-standard installation took place with the
TimeTrex install.


Can anyone offer suggestions so I can back up my database?