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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2010-11-28 16:54:35 | Re: log statements span multiple lines |
Previous Message | Stuart Kendrick | 2010-11-28 14:44:55 | log statements span multiple lines |