I'm trying to tame a geoserver 2.3.2 installation (backed by Postgres 9.1 and Postgis 1.5) by determining which of the many, many layers are actually being used. To this end, I've set up an audit table that logs SELECT activity on the tables I think might be deprecated.
However, when I make a WMS request to a layer that should be audited, I don't get any records in my audit table. Of course, auditing SELECT in PostgreSQL isn't foolproof because INSERTs into the audit table are susceptible to ROLLBACKs. Indeed, when logging all queries from geoserver to postgres, I see that geoserver is querying data in a transaction and then rolling back
bind S_1: BEGIN execute S_1: BEGIN parse <unnamed>: SELECT ... bind <unnamed>: SELECT ... execute <unnamed>: SELECT ... parse S_2: ROLLBACK bind S_2: ROLLBACK execute S_2: ROLLBACK
There doesn't appear to be any reason to run a SELECT inside a transaction, and even less of a reason to ROLLBACK the transaction as there are no errors. Is there a setting to turn off this transactional behavior when responding to a WMS request?
I like the database audit solution because it is easily searchable and reportable. As a work-around I could certainly just turn on verbose logging at either the database or geoserver level and grep the logs for layer names.