I have a function that was written in Language SQL (not plpgsql) that is scheduled to be called nightly from pgAgent. It was running fine until last night. The function usually takes under 3 minutes to execute, but when I came in this morning it was still running 5 hours later.
I queried pg_stat_activity and see that the call is hanging on a wait_event: Extension, wait_event_type: Extension. I have been trying to research this issue, and the only information I have found is that the query is waiting on a Postgres extension. The only extensions I currently have installed are plpgsql, postgres_fdw, tablefunc, adminpack, and pgAgent. The only extensions that seem likely are pgAgent – which is calling the function at the right time, postgres_fdw – foreign tables are used in the function, but querying the foreign tables outside of the function seem to be working fine.
How do I find which extension is causing the wait, and what are the next steps for solving this issue? There is a lot of information out there about resolving locks, but I was unable to find anything about the Extension wait_event other than a brief description.
Best Answer
I discovered that the wait_event_type: Extension was referring to it getting locked up in a query passed to the foreign data wrapper. However, the problem does not seem to be the Postgres_FDW, but rather using Language SQL to write my function and not using LANGUAGE plpgsql. I recalled a post either here, on StackOverflow, or another forum which mentioned a bug in the SQL procedural language in Postgres that would unexpectedly lock up queries intermittently. I rewrote the function in plpgsql, which was very simple, and it has been working flawlessly for over 3 months now. I wish I could find the thread that mentioned the bug, but they had said it had been reported to Postgres, and the likelihood of them fixing it was slim. Just use LANGUAGE plpgsql for your functions and stay away from the LANGUAGE SQL.