Re: Locks

On Mon, Nov 16, 2020 at 12:50 PM Yambu <hyambu(at)gmail(dot)com> wrote:

> Hi
> How 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

https://www.postgresql.org/docs/13/runtime-config-logging.html#GUC-LOG-LOCK-WAITS

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.

https://www.postgresql.org/docs/13/runtime-config-logging.html#GUC-LOG-MIN-DURATION-STATEMENT

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.

https://github.com/darold/pgbadger

--
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com