Thread: Inconsistent query results...

Inconsistent query results...

From
Andrew Schmeder
Date:
Hello all,

I am experiencing a somewhat strange behavior with Postgres.  I am running 7.0
beta 5 on Linux Slackware, and connecting to Postgres through php with the pg_*
function set.  Everything seems to work fine, however A particular query fails
about 10% of the time.

The query is a simple two-way join:

select xapctl_https_dom.ssl_hostname, xapctl_dom.dom_id, xapctl_dom.prefix
from xapctl_https_dom, xapctl_dom
where xapctl_https_dom.ssl_hostname = 'blackbox.xapnet.com'
and xapctrl_https_dom.dom_id = xapctl_dom.dom_id

(For the curious, I this PHP program is performing virtual
host resolution using the database... this simple join should verify that the
hostname requested exists.)

Problem is, after using pg_numrows to determine how many rows were selected, it
returns 0 about 5-10% of the time.  Weird, isn't it?  When this happens the
PHP script will return an error stating "hostname could not be resolved", as
it should.

My PHP script uses a debugging log so I can check the full text of
the query everytime it executes, and it is always correct.  Also it is set up so
that if the query returns an error it will display a different error message
with the contents of the query.

I also vacuum and vacuum analyze the database on a regular basis.

To make a shot in the dark, I tried putting a 1/10th of a second delay before
making the call to pg_numrows() but that did nothing.

Does anyone have an idea as to how I might be able to track down the problem
(or should I upgrade to RC1?)..


Thanks,
-Andy

Re: Inconsistent query results...

From
Andrew Schmeder
Date:
Okay... so I tried putting a test query "select 1" in front of the given query
and that fails also....

> Problem is, after using pg_numrows to determine how many rows were selected,
it > returns 0 about 5-10% of the time.  Weird, isn't it?  When this happens the
> PHP script will return an error stating "hostname could not be resolved", as
> it should.

Basically one of the postgres backends was failing to work properly (or php
messed up the persistant connection...  ?) ... with 6 http processes ~15%
failure makes sense.

A restart of the http server fixed it ... for now.  I am still sort of
wondering on this event though... its not very nice behavior.  Maybe I will
have to schedule regular http restarts.

-Andy