3

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.

Improve this question
6
  • Is there anything on the CPU (including the CPU on the other side of the FDW), or is everything just locked up with no resource consumption?
    – jjanes
    Commented Jul 30, 2019 at 21:04
  • @jjanes CPU consumption on our server is about normal (10-40%). I do not have access to the foreign server to check CPU usage, as it is a commercial database where we only have read access. I ran each SQL statement independently, which ran in about 3 minutes, and after I finished I reran the function and it seems to run in about 30 seconds.
    – dcbeckman
    Commented Jul 30, 2019 at 22:32
  • I changed the PgAgent job to run each SQL statement independently, and I will see if I run into any problems with that. I have heard that there are sometimes issues with functions written in "Langauge SQL" locking up unexpectedly. This may be related to that and I could perhaps rewrite the functions in pl-pgsql. The foreign server is also running Postgres 9.3, so it could be an issue of our 10.5 server trying to push the execution plan to the foreign server, and the foreign server not knowing what to do.
    – dcbeckman
    Commented Jul 30, 2019 at 22:39
  • I would attach to the process with "gdb" and then get a backtrace. That should immediately clarify where it is coming from (at least if you have debug symbols installed, I don't know how useful it would be if you do not)
    – jjanes
    Commented Jul 31, 2019 at 14:27
  • @jjanes We are using a Windows server. Is there an equivalent to gdb? The foreign data server is running Red Hat if that helps.
    – dcbeckman
    Commented Jul 31, 2019 at 16:23

1 Answer 1

Reset to default
3

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.