Backend message type 0x50 arrived while idle

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

Hi...

We're using an older version of PostgreSQL (6.5.1, to be precise), and we're
running into a problem where during certain inserts and queries we receive a
message "Backend message type 0x50 arrived while idle". We've also seen
backend message 0x45. Are these defined somewhere? We're trying to figure
out why this is happening, and if upgrading to a newer version of PostgreSQL
might fix the problem, and I'm not finding much in the way of documentation
that gets into the meaning of the various backend messages. Any
information you can give me would be appreciated, because this is getting
really frustrating. Thanks.

I'm not sure what those messages mean, but 6.5.x is VERY old.

Upgrading is worth it in so many ways, that even if it didn't fix this it
would be worth it. Most likely this will be fixed by upgrading, but it's
not likely you'll get a lot of help on troubleshooting a version that old.

Since the release 6.5.x, 7.0.x, 7.1.x, 7.2.x and now 7.3 have come out.

For one thing, 7.3 is literally about 100 times faster than 6.5.x, and
it's definitely more reliable and has a lot fewer bugs in it.

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

Robin

-----Original Message-----
From: scott.marlowe [mailto:scott.marlowe@ihs.com]
Sent: Tuesday, December 03, 2002 3:35 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:

Hi...

We're using an older version of PostgreSQL (6.5.1, to be precise), and

we're

running into a problem where during certain inserts and queries we receive

a

message "Backend message type 0x50 arrived while idle". We've also seen
backend message 0x45. Are these defined somewhere? We're trying to

figure

out why this is happening, and if upgrading to a newer version of

PostgreSQL

might fix the problem, and I'm not finding much in the way of

documentation

that gets into the meaning of the various backend messages. Any
information you can give me would be appreciated, because this is getting
really frustrating. Thanks.

I'm not sure what those messages mean, but 6.5.x is VERY old.

Upgrading is worth it in so many ways, that even if it didn't fix this it
would be worth it. Most likely this will be fixed by upgrading, but it's
not likely you'll get a lot of help on troubleshooting a version that old.

Since the release 6.5.x, 7.0.x, 7.1.x, 7.2.x and now 7.3 have come out.

For one thing, 7.3 is literally about 100 times faster than 6.5.x, and
it's definitely more reliable and has a lot fewer bugs in it.

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.

On Tue, Dec 03, 2002 at 12:43:09PM -0800, 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

To be honest, it's totally worth it upgrading and nowhere near as hard as
you think. A while ago we moved from 6.5.x to 7.0.x and we didn't have to
change a line of code. Since then we've tested our stuff with 7.2.x and
again, it's worked without a single modification.

If you get the time, setup a test machine, just install everything as usual
but put 7.3.x on instead. I think you'll be pleasently suprised.

FWIW, those errors probably relate to actual errors signalled by the backend
not correctly flagged/noticed by the client. If you have logging turned on
in the backend you'll probably get much more helpful messages out of the
log.
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

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.

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

"Wynn, Robin" <RWynn@northropgrumman.com> writes:

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?

AFAIK it's extremely bad practice in general to share a connection
between two threads, unless you protect it with some kind of lock to
avoid simultaneous use. Using a connection per thread is a much
better idea. The only issue with that is that one thread won't see
results of an in-progress transaction until the other thread commits.

-Doug

"Hegyvari Krisztian" <Hegyvari.Krisztian@ardents.hu> writes:

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

No, that's what MVCC is all about. ;)

-Doug

Doug McNaught <doug@mcnaught.org> writes:

AFAIK it's extremely bad practice in general to share a connection
between two threads, unless you protect it with some kind of lock to
avoid simultaneous use.

I suspect Doug's put his finger on the problem --- are you trying to
use the same PGconn object in both threads? Not a good idea at all.
libpq isn't thread-aware (mainly because of the portability problems
that would ensue), and it *will* break if you try to use the same
PGconn concurrently in two different threads.

regards, tom lane

Doug McNaught wrote:

"Wynn, Robin" <RWynn@northropgrumman.com> writes:

AFAIK it's extremely bad practice in general to share a connection
between two threads, unless you protect it with some kind of lock to
avoid simultaneous use. Using a connection per thread is a much
better idea. The only issue with that is that one thread won't see
results of an in-progress transaction until the other thread commits.

I agree with you on seperate connections, and as for the multiple
threads seeing
the latest values of data or data sets, ... no new problem has been
added, its just
that now the source of the data object(s) are a TCP connectiion away,
instead of a
RAM memory controller (or mem manager) away....