Context : I have a legacy PostgreSQL database (currently 9.5 but can be any newer version) and a tons of script / program acceding it. All this script can do various operations on the table's row (READ, INSERT, UPDATE, DELETE).
Problem : I don't know which script do what on which table, and it become really problematic when I when to modify some table structure (like make the primary key a SERIAL and not a plain bigint with "SELECT MAX(id) + 1" to have the next id ...). I have also some "ninja" script that connect to the database using god know forsaken login/pwd, and for legacy reason, I can't just change the pwd.
What I want to do : I want to know which table are "used" by wich connection (I can know which program do which connection).
So far, I tried to apply a trigger on every table that I want to supervise. The main idea is to simply insert into a table "trace" that the "pg_backend_pid()" have do a "INSERT" into the table "foo" if the query "INSERT INTO foo (bar) VALUES (10);" But a trigger on a SELECT event doesn't exist, so it's not enough.
I activated all the logging that PostgreSQL 9.5 can offer
extract of postgresql.conf
- What to Log -
debug_print_parse = on
debug_print_rewritten = on
debug_print_plan = on
debug_pretty_print = on
log_checkpoints = on
log_connections = on
log_disconnections = on
log_duration = on
log_error_verbosity = terse
log_hostname = on
But I can't find a way to get what I want. There is the query plan that seem promising, but this "thing" is so frickkin' huge that I can't begin to understand how to read it (also, I don't find any documentation even is the EXPLAIN keyword documentation on how to read that !).
So I begin to be clueless. Is there a way to do it ? Is the query plan the good lead ?