debugging SQL statements - Mailing list 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