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 PQresultErrorMe ssage 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@kill erbytes.com

http://www.killerbytes.com/

(303) 665-7007 voice


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

[Tom Lane]

Re: Why would query return PGRES_NONFATAL_ ERROR?

I said:[color=blue]
> 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.[/color]

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_ERR OR 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 PQresultErrorMe ssage 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]

Re: Why would query return PGRES_NONFATAL_ ERROR?

Scott Ribe <scott_ribe@kil lerbytes.com> writes:[color=blue]
> 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 PQresultErrorMe ssage is
> blank.[/color]

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?
[color=blue]
> I've gone into postgresql.conf and upped the logging info. Any clues as to
> what I should be looking for?[/color]

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]

Re: Why would query return PGRES_NONFATAL_ ERROR?

> In 7.3 there is only one use of PGRES_NONFATAL_ ERROR, and it's this:[color=blue]
>
> 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_ERR OR for this case, which I think is saner.)[/color]

That gives me the start of an idea...
[color=blue]
> 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 PQresultErrorMe ssage can tell you nothing
> (since there is no result), you should find something informative
> in the connection's error status (PQerrorMessage ).[/color]

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@kill erbytes.com

http://www.killerbytes.com/

(303) 665-7007 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