Why would query return PGRES_NONFATAL_ERROR?

[Scott Ribe]

I'm using 7.3.2, invoke a small stored procedure via PQexec, and
PQresultStatus returns PGRES_NONFATAL_ERROR. I can find nothing in the docs
to help me understand what could cause this, and PQresultErrorMessage is
blank.

This is pretty rare; most of the time by far the stored procedure executes
and I get the correct result back. I have almost no clue about what's
different when the failure occurs. I did notice this last time that there
was a backend stuck in a state with a transaction open forever. Could that
be it? A write lock conflict?

I've gone into postgresql.conf and upped the logging info. Any clues as to
what I should be looking for?

The stored procedure in question returns a longint, and when getting any
kind of error indication I don't examine its results. Should I be going
ahead and using PQgetvalue after getting a non-fatal error???

I guess next I should look putting some debug code into the stored procedure
to log its progress...
--
Scott Ribe
sc********@killerbytes.com
http://www.killerbytes.com/
[phone] voice
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Tom Lane]

I said:

Hmm ... PGRES_NONFATAL_ERROR is only used for reporting NOTICE messages
coming from the backend, and AFAICS such a result should never be
returned out of PQexec; it's only passed to the notice-message receiver.

Wait, forget that; it's based on looking at CVS tip code :-(

In 7.3 there is only one use of PGRES_NONFATAL_ERROR, and it's this:

ExecStatusType
PQresultStatus(const PGresult *res)
{
if (!res)
return PGRES_NONFATAL_ERROR;
return res->resultStatus;
}

So what you're seeing is a NULL PGresult pointer. (7.4 uses
PGRES_FATAL_ERROR for this case, which I think is saner.)

The most likely causes for a NULL result from PQexec would be
out-of-memory or failure to send the query due to communication
failure. Although PQresultErrorMessage can tell you nothing
(since there is no result), you should find something informative
in the connection's error status (PQerrorMessage).

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Tom Lane]

Scott Ribe <sc********@killerbytes.com> writes:

I'm using 7.3.2, invoke a small stored procedure via PQexec, and
PQresultStatus returns PGRES_NONFATAL_ERROR. I can find nothing in the docs
to help me understand what could cause this, and PQresultErrorMessage is
blank.
Hmm ... PGRES_NONFATAL_ERROR is only used for reporting NOTICE messages
coming from the backend, and AFAICS such a result should never be
returned out of PQexec; it's only passed to the notice-message receiver.
You say you're getting it back from PQexec? You *sure* your libpq is
7.3 vintage?
I've gone into postgresql.conf and upped the logging info. Any clues as to
what I should be looking for?

A NOTICE or WARNING sent to that client ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Scott Ribe]

> In 7.3 there is only one use of PGRES_NONFATAL_ERROR, and it's this:

ExecStatusType
PQresultStatus(const PGresult *res)
{
if (!res)
return PGRES_NONFATAL_ERROR;
return res->resultStatus;
}

So what you're seeing is a NULL PGresult pointer. (7.4 uses
PGRES_FATAL_ERROR for this case, which I think is saner.)
That gives me the start of an idea...
The most likely causes for a NULL result from PQexec would be
out-of-memory or failure to send the query due to communication
failure. Although PQresultErrorMessage can tell you nothing
(since there is no result), you should find something informative
in the connection's error status (PQerrorMessage).

I don't think there's any communication failure, since the app and server
are running on the same machine. But I'm using a pool of connections, so
perhaps some prior thread abused a connection and left it in a
non-functioning state. I'll have to take a close look at some of the error
handling, at least make sure that everything is logged by my app, and of
course get the connection's error status when I see PGRES_NONFATAL_ERROR.

Thanks.
--
Scott Ribe
sc********@killerbytes.com
http://www.killerbytes.com/
[phone] voice
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match