Thread: Sporadic query not returning anything..how to diagnose?

Hi.

(My pgbouncer is finally working and has results in at least a 3-fold
site speed increase! YAY! Thanks to everyone who helped.)

Now, a new small problem.

In my PHP code I have a condition that checks for the existence of a
record, and if not found, it INSERTs a new one.

Here's the first SQL to check existence:



# SELECT ip FROM links WHERE ip = 1585119341 AND url_md5 =
'cd4866fa5fca31dfdb07c29d8d80731c' LIMIT 1

                                                         QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..26.20 rows=1 width=8) (actual time=0.054..0.054
rows=1 loops=1)
  ->  Index Scan using idx_links_ip_url on links  (cost=0.00..26.20
rows=1 width=8) (actual time=0.053..0.053 rows=1 loops=1)
        Index Cond: ((ip = 1585119341) AND (url_md5 =
'cd4866fa5fca31dfdb07c29d8d80731c'::text))
 Total runtime: 0.078 ms
(4 rows)



About 5% of the times (in situations of high traffic), this is not
returning a value in my PHP code. Because it's not found, the code
tries to INSERT a new record and there's a duplicate key error, which
is in the logs. The traffic to the site is much higher than the number
of these entries in my log, which means it's only happening
sometimes--my guess is for 5% of all queries, which is still quite
significant (about 60,000 a day).

I began logging these "missed" SELECT queries, and when I manually go
into the postgresql terminal and execute those queries, the record is
indeed found. No problem.

So my question: is this related to some timeout or something with
pgbouncer, where I suppose the connection is held for a split-second
longer than it would, and therefore the query doesn't return anything?
Probably an inane guess. Just wondering aloud.

Welcome any thoughts on how to debug this. Btw, the logging is
happening in the postgresql usual log file, the pgbouncer log just has
hordes of one-liners stating how many requests per minute...

Thanks!

Am 29.11.2011 16:46, schrieb Phoenix Kiula:
> About 5% of the times (in situations of high traffic), this is not
> returning a value in my PHP code. Because it's not found, the code
> tries to INSERT a new record and there's a duplicate key error, which
> is in the logs. The traffic to the site is much higher than the number
> of these entries in my log, which means it's only happening
> sometimes--my guess is for 5% of all queries, which is still quite
> significant (about 60,000 a day).

As the two statements aren't atomic (i.e., after the select, how do you
make sure that no other process accessing the DB has a chance to
select/insert, before the [delayed] insert of the primary process gets
executed)? This is a common form of race-condition.

In the spirit of "it's easier to ask forgiveness than permission", just
do the insert and evaluate the result (i.e., whether a record was
actually inserted) to get at the same information and the same effect as
with the two statements you're currently executing.

--
--- Heiko.