{"id":3000,"date":"2016-01-19T00:26:23","date_gmt":"2016-01-19T00:26:23","guid":{"rendered":"http:\/\/www.garysieling.com\/blog\/?p=3000"},"modified":"2016-01-19T00:26:23","modified_gmt":"2016-01-19T00:26:23","slug":"postgres-log-message-regex-locks","status":"publish","type":"post","link":"https:\/\/www.garysieling.com\/blog\/postgres-log-message-regex-locks\/","title":{"rendered":"Regular Expression for Postgres log messages warning of locks"},"content":{"rendered":"

To match locks in Postgres logs you can use the following regular expressions. The first matches messages that show tables being locked, and the second shows the application loading share locks.<\/p>\n

\n.*user=(?P<lock_user_name>\\w+),db=(?P<lock_database>\\w+) \nLOG:\\s+process (?P<lock_process_id>\\d+) \nacquired (?P<lock_type>\\w+) on (?<lock_on>\\w+) \n(?P<lock_tuple>[\\(\\)0-9,]+) of (?P<lock_object_type>\\w+) \n(?P<lock_object_oid>\\d+) of database (?P<lock_db_oid>\\d+) \nafter (?P<lock_wait_time>[0-9.]+).*\t\n<\/pre>\n
\n.*user=(?P<lock_user_name>\\w+),db=(?P<lock_database>\\w+) \nLOG:\\s+process (?P<lock_process_id>\\d+) acquired \n(?P<lock_type>\\w+) on transaction (?<lock_transaction_id>\\d+) \nafter (?P<lock_wait_time>[0-9.]+) .*\t\n<\/pre>\n
The useful thing about this is it allows you to see which queries wait on locks, and for how long.<\/p>\n
To make these entries show up in the log, you need this in postgresql.conf:<\/p>\n
\nlog_lock_waits = on\n<\/pre>\n
Locks on tables are particularly interesting, because they will list both the rows and tables that are locked. However, they do this using internal identifiers.<\/p>\n
You can look up the tables like so:<\/p>\n
\nselect pg_class.oid, nspname, relname\nfrom pg_class, pg_namespace\nwhere pg_class.relnamespace = pg_namespace.oid\n  and pg_class.oid = 1234\n<\/pre>\n
The log output will give you row IDs (representing where the row is stored). You can query this like so:<\/p>\n
\nselect *\nfrom mytable\nwhere ctid::text = '(0,1)'\n<\/pre>\n
If you are experiencing a lot of locks and are concerned, it is also worth trying to get the server name of the offending transaction, so that you can determine whether multiple servers in a farm are causing you issues, or several processes on the same server.<\/p>\n","protected":false},"excerpt":{"rendered":"
To match locks in Postgres logs you can use the following regular expressions. The first matches messages that show tables being locked, and the second shows the application loading share locks. .*user=(?P<lock_user_name>\\w+),db=(?P<lock_database>\\w+) LOG:\\s+process (?P<lock_process_id>\\d+) acquired (?P<lock_type>\\w+) on (?<lock_on>\\w+) (?P<lock_tuple>[\\(\\)0-9,]+) of (?P<lock_object_type>\\w+) (?P<lock_object_oid>\\d+) of database (?P<lock_db_oid>\\d+) after (?P<lock_wait_time>[0-9.]+).* .*user=(?P<lock_user_name>\\w+),db=(?P<lock_database>\\w+) LOG:\\s+process (?P<lock_process_id>\\d+) acquired (?P<lock_type>\\w+) on transaction (?<lock_transaction_id>\\d+) … <\/p>\n
Continue reading “Regular Expression for Postgres log messages warning of locks”<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"footnotes":""},"categories":[4],"tags":[186,345,347,370,437,458,522],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t