Postgresql's QueryExecutor hanging indefinitely at `receiveChar()` for query which has successfully completed

81
September 19, 2021, at 03:50 AM

I'm facing a weird issue where Postgres seemingly isn't sending a reply to my client code even after successfully completing the query. As such, it hangs around indefinitely in the blocking receiveChar() call waiting for something that never arrives. Note, this is not an intermittent behavior, it's happening every time we attempt to run the query through Java.

Here's the relevant code where it's blocking.

    boolean doneAfterRowDescNoData = false;
    while (!endQuery) {
      c = pgStream.receiveChar();   // <- blocks here and never comes back 
      switch (c) {
        case 'A': // Asynchronous Notify
          receiveAsyncNotify();
          break;

This occurs while trying to move about 2m records from one table into another. e.g.

insert into target_table (
   select * from blah where xyz
)

How I checked it was not the query itself still working and is actually the client hanging

The query itself happily completes, which I verified multiple ways.

  1. select count(*) on the target table and finding the transferred data
  2. verifying that no database activity remains.

Here's the Performance tab of RDS showing nothing going on.

Similarly, this was confirmed in the database itself via

select relname,
       psa.pid,
       psa.application_name,
       l.granted,
       query,
       l.*,
       psa.*
from pg_locks l
         inner join pg_stat_activity psa ON (psa.pid = l.pid)
         left outer join pg_class c ON (l.relation = c.oid)

which shows no activity going on. And yet, the receiveChar() call still sits there waiting.

Logs show no timeouts, connection drops, or anomalies

Last entries in the log are the query and then the dump of the params.

2021-09-18 03:16:52 UTC:72.21.198.65(10130):foo@postgres:[20850]:DETAIL:  parameters: $1 = 'foo', $2 = 'bar'

Nothing after this. The insert finishes. I can see the results in the DB. All the while, the debugger is still hanging out at the receiveChar() line waiting for a response from Postgres.

Plz halp...

Extra Info

  • I'm using the postgresql = 42.18 version of the Postgres java library.
  • I'm the only client of the database.
  • there are no errors in the log.
  • The insert executes without issue if I limit it to smaller sets. However, when doing the full 2m, which is still pretty small potatoes, it does this hanging behavior.
READ ALSO
paste event not loading data in the list for first time paste ,but working fine after that

paste event not loading data in the list for first time paste ,but working fine after that

whenever the paste event happens in input box it does not load options in data-list in first attempt but in second attempts it works and enables the button to proceed furtherAlso it works fine in keyup event

73
Model as a variable

Model as a variable

I use switch to execute the code I want depending on the modelIs there a way to immediately substitute the model into the code from the variable that comes to me to avoid repeating the code 3 times?

52
CSS SVG BackGround image in Django

CSS SVG BackGround image in Django

i used en template who use bootstrap 5,

85
S3 object restore from GLACIER not working with Python boto3

S3 object restore from GLACIER not working with Python boto3

Trying to restore an s3 object from GLACIER with the code below

60