Last Run/Modified Procedure Timestamp/Date in Postgresql

Question

I have a schema with a table along with a procedure. I want to find the last run or the modifications done in that procedure from that schema.schema is abc, so for abc schema the procedure proc1 should show the last run or any modifications done on it. How to find that for Postgresql Database?

Answer 1

There is no way to get this information retroactively, i.e. for past runs.

You can create a table like proc_last_run and have each procedure insert or update a row in it each time it's run, but this only works when you can modify each procedure, and only for runs after you modify it.

For runs in the past you simply can't. PostgreSQL doesn't keep track of that information, so you can't get it. You might be able to extract it from the server logs if you run with log_statement = 'all', but that's about it.

Answer 2

For anyone who just couldn't believe that a modern DBMS would NOT keep track of the created/last_altered date of a stored procedure, here's the doc: PostgreSQL → 12.3 → Reference → Manual ... where it says, "... Applies to a feature not available in PostgreSQL"