Is it possible to track database activity from the beginning, until the end of a PHP script invocation?
For example, let's say I have a script which creates a new record for a user in a database. You fill in a form, hit submit and provided all the data is valid the script proceeds to affect multiple tables inserting data.
The script inserts data into a main user table; (name, address, tel, etc...) is returned a new userid and then also adds a record to a department table (userid, departmentid) and a salary amount to a payroll table (userid, salary).
Is there a way to see all the tables that were affected by the script, the order in which they were used and the data passed to them for a single invocation?
Extra info: I'm using Ubuntu 14.04 LTS with Apache 2.4.7, PHP 5.5.9 and Postgres 9.3.5. I am the sole user of this machine; Everything will be run locally.
Ultimately I want to set the script running and watch what it does in the Postgres DB.
Thank you.
You can do this server-side, by setting log_statement = 'all'
and setting a suitable log_line_prefix
to include the backend process ID, transaction ID, etc.
You might find it useful to set an application_name
in the client connection string or SET application_name = 'blah'
in your script, so it's easier to find in the logs too.
Note that you can set log_statement
on a per-user basis with ALTER USER blah SET log_statement = 'all'
, or on a per-database basis with ALTER DATABASE blah SET log_statement = 'all'
. You can clear the setting later with ALTER ... RESET log_statement
.
Use of CSV format logs makes it easier too.