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.