Postgres Pro
Downloads
Home   >   mailing lists

Thread: Why would query return PGRES_NONFATAL_ERROR?

Why would query return PGRES_NONFATAL_ERROR?

From
Scott Ribe
Date:
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
scott_ribe@killerbytes.com
http://www.killerbytes.com/
(303) 665-7007 voice

Re: Why would query return PGRES_NONFATAL_ERROR?

From
Tom Lane
Date:
Scott Ribe <scott_ribe@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

Re: Why would query return PGRES_NONFATAL_ERROR?

From
Tom Lane
Date:
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

Re: Why would query return PGRES_NONFATAL_ERROR?

From
Scott Ribe
Date:
> 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
scott_ribe@killerbytes.com
http://www.killerbytes.com/
(303) 665-7007 voice