On Mon, Nov 16, 2020 at 12:50 PM Yambu <hyambu@xxxxxxxxx> wrote:
HiHow can i find out when a lock happened?Im writing a function that will check why some queries ran slowly at particular times, so i want to know if i can store lock information somewhere and retrieve what time the lock happened?regards
That historical information is not kept in the database. However, you can turn on logging of lock waits if they are longer than a given amount of time. Just set it to your desired threshold in the postgresql.conf and reload
Note that if you set this very low, you can greatly increase the size of your log files. So I'd recommend figuring out a threshold value where it's actually a problem if the wait is that long.
Couple this with the log_min_duration_statement, which will also log queries that have run longer than a given time, and you could then do some log analysis to match up long running queries with associated locks. Again, be cautious with setting this too low and making your logs too large to manage.
If you're looking for something to help with log analysis, I'd recommend pgbadger. If you have the above to options turned on, it allows the reports it generates to be very informative.
