How do I make PostgreSQL log the file path and line number of the errors? (If it's even possible at all.)

Question

My postgresql.conf contains this:

log_destination = 'csvlog'
logging_collector = on
log_directory = 'C:\\Users\\John Doe\\Documents\\PostgreSQL logs'
log_filename = 'PG_%Y-%m-%d_%H;%M;%S'
log_rotation_age = 1min
log_rotation_size = 0
log_truncate_on_rotation = on
log_min_error_statement = 'info'

This makes PG log its errors to CSV files in my custom dir.

Then, I have a constantly running script which looks for new files in that dir, and if it finds any, COPYies it into a custom database table (as described/recommended in the manual) and deletes the file that was successfully added. It doesn't touch the currently active log file.

My table only has one custom column, a boolean called "unimportant", which I set depending on whether I think the error is important or not, so that I can hide the noise in various views and statistics.

Since PostgreSQL does not provide any fields such as "file path" or "line number", I'm at a total loss as to what caused the various logged errors. The only thing I have to go by is the application_name, which is uselessly limited due to being 64 characters maximum and no Unicode, even preventing me from abusing this field to, for example, set it to the relevant file path. But even if it were possible, I wouldn't want to do that, since application_name is supposed to be the application name and nothing else. (But again, it doesn't matter since the path would be too long and contain non-ANSI characters anyway.)

I've thought long and hard about this, but I just can't figure out a way to make me able to know where exactly the error occurred.

Application (PHP) errors will include the relevant location, but these errors are not always logged (I'm unsure why). Here are some examples of errors which don't trigger PHP errors but are logged by PostgreSQL:

• there is no transaction in progress for the query COMMIT in application_name whatever123. Okay? Which script did it? And which line?
• there is already a transaction in progress for the query BEGIN in application_name whatever123. Okay? Which script did it? And which line?
• current transaction is aborted, commands ignored until end of transaction block and deadlock detected ones at least specify what query caused it, but again, I'd have to hunt it down manually by searching for parts of the query. Usually, I don't bother, or am unable to because the query was constructed in code in an unsearchable way.

Is there really no way to solve this? Any idea why the above errors aren't considered "real" errors to PHP (or rather, the pg_*/pglib functions)? And can I control that somehow?

Answer 1

Database logging is the wrong end to handle the problem, since the database does not have the required information.

You will have to write your client application so that it logs the information you need whenever it receives a database error.