Error importing connection log (W)

[smurthys]

Executing select * from classdb.importconnectionlog(); on the CS205 DB in production failed, and produced the following error message:

BTW, the IP address of the host has changed over time, but that should not cause the error reported.

ERROR:  could not open file "/opt/postgresql/data/logs/pg96/postgresql-03.04.csv" for reading: No such file or directory
CONTEXT:  SQL statement "COPY classdb.postgresLog FROM '/opt/postgresql/data/logs/pg96/postgresql-03.04.csv' WITH csv"

[smurthys]

IMPORTANT: For security sake, please be careful including details of the server or the DB in this thread, because the issue is about a system in production.

[KevinKelly25]

I am not sure exactly what happened with the server earlier this semester but when the server went down could the CSV file have gotten deleted, renamed, or the path changed?

[wildtayne]

I am unable to access the sever ATM, but I suspect that due to the power outages there are one or more daily log files that were not created due to the server being offline. You could try manually supplying a date to classdb.importconnectionlog(), this will import logs between the current date and supplied date. Based on the error, 03-05 would probably be a good date to try. See below for some caveats before trying this.

Note that with the current system, any log dates that are skipped by supplying a date to the function can't be imported at a later date. I would double check the last imported log date to make sure supplying a manual date will not skip a large chunk of existing logs. If the above solution will cause an unacceptable number of log files to be skipped, I can try creating some dummy log files to replace the missing ones. This should allow the import procedure to run normally.

[wildtayne]

This issue should probably be kept open - classdb.importconnectionlog() should have some sort of error handling added to deal with this situation.

[wildtayne]

I was able to log onto the server and confirm that only the 03-04 log file is missing. This is causing the import error. The solution outlined in the above comments still holds.

[wildtayne]

The issue on the local server has been fixed by "patching" the log files.
For future reference, I have two general ideas for approaching the code patch:

• Check if log files exist before trying to import
• Use exception handling to catch the error when importing a missing file

The first is not possible without using a scripting language function, I need to do more research on the second to see if the file read error is handleable.

[wildtayne]

PL\pgSQL does support exception handling (see here and here).
I have used this to add some simple exception handling to ClassDB.importConnectionLog() that solves the issue described in this thread by skipping missing log files. See here for a diff. I think there are a few points to consider with how missing log files should be handled:

• Should they be skipped silently or display a message
• Should ClassDB.importConnectionLog() still fail but with a more descriptive error message
• Should there be a manual override parameter to skip missing log files

Any input would be appreciated.

[afig]

Apologies for not mentioning earlier that PL/pgSQL does in fact have support for exception handling (I wrote that section in the paper after all).

If we opt for failing, then we would definitely need an (easy) override, since it should be possible to have days without logs. (Consider a situation where ClassDB is deployed on a computer that is not on every day.)

I think displaying a message would be appropriate, probably at the NOTICE level. That way persons importing the log will be alerted to the fact that there are missing logs, but it does not inconvenience anyone who expects there to be days missing.

[wildtayne]

No problem, my main concern was if there was a specific exception for file not found. I think the current issue with only display a notice is that it is currently not possible to go back and re-import logs from missed dates, should they be available. Although I'm not sure if this is likely to happen. It may be best to document this limitation for now.

As mentioned in #203, I think it would be a good idea to also check for logging_collector at the beginning of ClassDB.importConnectionLog(). logging_collector = off would raise a NOTICE as well, because it is possible there are remaining logs that need to be imported, and the exception handling will notify the user of any missing files.

[smurthys]

I have the following thoughts about ClassDB.importConnectionLog():

• Work normally and then raise a notice each if logging_collector or log_connections is off
• Return a table with three attributes with one row for each date it attempts to process: date, number of activity rows added to the ClassDB log, and a VARCHAR remark which can for now just be the exception string if there was an exception processing a file.
• Also raise a warning with each exception encountered when processing a file

PS: We already have exception handling in production. See function classDB.grantRole

[wildtayne]

log-exception-handling now contains much of the discussed functionality.