Re: Backend message type 0x50 arrived while idle

Reading about updates and selects rings a bell: does not a select wait until the update either commits or rolls back?

>>> "Wynn, Robin" <RWynn@northropgrumman.com> 12/04/02 02:34pm >>>
There are no other messages that I can see... that doesn't mean there aren't
any, of course, just that the legacy code I've inherited doesn't process
them.  I actually suspect that I have a handle on what's going on... what I
think it boils down to is that there are two threads (there are more, but
these two are what I think are causing the problem).  One periodically
updates an element called pct_complete in a table called JTRANSACTION, the
other periodically does a select from that same table to get the current
value of pct_complete.  Both have the same connection ID.  Randomly, we hit
(what I suspect is) a race condition where both things are happening at the
same time, and the one performing the SELECT hangs.  That happens to be the
parent thread, for what that's worth.  At any rate, it always seems to be
the case (at least, so far) that when the backend message comes through, the
parent thread is hung.  So, I'm going to dig around some more and see what's
been recommended for avoiding this condition... I'll also try making a new
connection with one of the threads (thus, a different backend, from what I
understand) and see if that avoids this problem.  Any other suggestions?
Could this theoretically happen with an INSERT/SELECT combination, or is it
unique to the UPDATE/SELECT pairing?

Robin

-----Original Message-----
From: scott.marlowe [mailto:scott.marlowe@ihs.com]
Sent: Tuesday, December 03, 2002 5:04 PM
To: Wynn, Robin
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Backend message type 0x50 arrived while idle


On Tue, 3 Dec 2002, Wynn, Robin wrote:

> I know... the problem is two-fold.  One, I had to have that in writing so
I
> could justify upgrading, and two, we release in a few days and can't
really
> change the database until that's complete (requires too many changes to
> existing things).  I was just hoping someone here would know what that
> message meant so I could either document it, or prove it's not the
database
> that's got a problem, it's our interaction with it.  I've seen a number of
> people ask this question on a number of different versions of PostgreSQL
(I
> believe the latest I've seen is 7.1.x), so I suspect that it's something
> we're doing... I just need to know what that message means, so I know how
to
> troubleshoot it on our end.  It's not incredibly descriptive :^P
>

OK, I was just wandering around the source code, and this same error can
happen in 6.5.x or 7.3.whatever.

The docs inside the source of
postgresql-7.3/src/interfaces/libpq/fe-exec.c say:

/*
* NOTIFY and WARNING messages can happen in any state besides
* COPY OUT; always process them right away.
*
* Most other messages should only be processed while in BUSY state.
* (In particular, in READY state we hold off further parsing
* until the application collects the current PGresult.)
*
* However, if the state is IDLE then we got trouble; we need to deal
* with the unexpected message somehow.
*/


And:

/*
* Unexpected message in IDLE state; need to recover somehow.
* ERROR messages are displayed using the notice processor;
* anything else is just dropped on the floor after displaying
* a suitable warning notice.  (An ERROR is very possibly the
* backend telling us why it is about to close the connection,
* so we don't want to just discard it...)
*/

So, are there any other messages to go with this error?

Are you using libpq to interface?  It sounds like maybe your client app is
sending data when it shouldn't.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org