Skip site navigation (1) Skip section navigation (2)

debugging SQL statements

From: Stuart Kendrick <skendric(at)fhcrc(dot)org>
To: pgsql-novice(at)postgresql(dot)org
Subject: debugging SQL statements
Date: 2010-11-28 14:58:35
Message-ID: 4CF26E1B.6070803@fhcrc.org (view raw or flat)
Thread:
Lists: pgsql-novice
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

Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2010-11-28 16:54:35
Subject: Re: log statements span multiple lines
Previous:From: Stuart KendrickDate: 2010-11-28 14:44:55
Subject: log statements span multiple lines

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group