Re: SELECT (sometimes) returning Zero Rows? Fixed, sort of...

A number of days ago I posted this thread complaining that occasionally I
was getting zero rows from selects where I was certain that the select
should produce 1 or more rows.

I have found that switching to pg_connect from pg_pconnect has caused the
problem to go away. I have been unable to reproduce the problem using
pr_connect. And switching back to pg_pconnect, I find the problem starts
again.

I find this odd, but I am hoping the knowledge of the folks on this list
might be able to enlighten me as to why this could be. Are there major
disadvantages to using connect instead of persistent connect?

What could cause this behavior based on the type of connect?

Many thanks,

Matt Friedman

----- Original Message -----
From: "Matt Friedman" <matt(at)daart(dot)ca>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>; "PgSql General List"
<pgsql-general(at)postgresql(dot)org>
Sent: Tuesday, February 13, 2001 6:03 PM
Subject: Re: SELECT (sometimes) returning Zero Rows?

> It's version 7.0.3
>
> Here's the output for the selects using explain below.
> We had planned to do all of the optimization later. We just haven't had
time
> to get to it yet. Could that be the problem?
>
> Anyhow here's the info. Please let me know if you need more information.
>
> Appreciatively,
> Matt Friedman.
>
>
> parent_report_new=> explain
> parent_report_new-> SELECT pr_article.title, pr_article.article_id FROM
> pr_article WHERE
> parent_report_new-> pr_article.article_id = pr_article_age.article_id AND
> pr_article_age.age_id
> parent_report_new-> = 1 AND pr_article.is_active = true AND
> pr_article.is_approved = true AND
> parent_report_new-> pr_article.release_date < 982093578 AND
> ((pr_article.end_date = 0) or
> parent_report_new(> (pr_article.end_date > 982093578 )) ORDER BY
> pr_article.release_date DESC
> parent_report_new-> LIMIT 2;
> NOTICE: QUERY PLAN:
>
> Sort (cost=4.39..4.39 rows=1 width=24)
> -> Nested Loop (cost=0.00..4.38 rows=1 width=24)
> -> Seq Scan on pr_article_age (cost=0.00..1.68 rows=1 width=4)
> -> Index Scan using pr_article_pkey on pr_article
(cost=0.00..2.03
> rows=1 width=20)
>
> EXPLAIN
>
>
> parent_report_new=> explain
> parent_report_new-> SELECT pr_article.title, pr_article.article_id FROM
> pr_article WHERE
> parent_report_new-> pr_article.article_id = pr_article_age.article_id AND
> pr_article_age.age_id
> parent_report_new-> = 1 AND pr_article.is_active = true AND
> pr_article.is_approved = true AND
> parent_report_new-> pr_article.release_date < 982093578 AND
> ((pr_article.end_date = 0) or
> parent_report_new(> (pr_article.end_date > 982093578 )) ORDER BY
> pr_article.release_date DESC
> parent_report_new-> LIMIT 3;
> NOTICE: QUERY PLAN:
>
> Sort (cost=4.39..4.39 rows=1 width=24)
> -> Nested Loop (cost=0.00..4.38 rows=1 width=24)
> -> Seq Scan on pr_article_age (cost=0.00..1.68 rows=1 width=4)
> -> Index Scan using pr_article_pkey on pr_article
(cost=0.00..2.03
> rows=1 width=20)
>
> EXPLAIN
>
>
> parent_report_new=> explain
> parent_report_new-> SELECT DISTINCT ON (release_date, article_id)
> pr_article.title,
> parent_report_new-> pr_article.article_id, pr_cat.prog_name FROM
pr_article
> WHERE
> parent_report_new-> pr_article.article_id = pr_article_age.article_id AND
> pr_article_age.age_id
> parent_report_new-> = 1 AND pr_article_cat.cat_id=pr_cat.cat_id AND
> pr_article.article_id =
> parent_report_new-> pr_article_cat.article_id AND pr_article.is_active =
> true AND
> parent_report_new-> pr_article.is_approved = true AND
> pr_article.release_date < 982093578 AND
> parent_report_new-> ((pr_article.end_date = 0) or (pr_article.end_date >
> 982093578 )) ORDER BY
> parent_report_new-> pr_article.release_date DESC LIMIT 5;
> NOTICE: QUERY PLAN:
>
> Unique (cost=7.63..7.64 rows=0 width=48)
> -> Sort (cost=7.63..7.63 rows=1 width=48)
> -> Nested Loop (cost=0.00..7.62 rows=1 width=48)
> -> Nested Loop (cost=0.00..5.58 rows=1 width=28)
> -> Nested Loop (cost=0.00..4.36 rows=1 width=12)
> -> Seq Scan on pr_article_age (cost=0.00..1.68
> rows=1 width=4)
> -> Index Scan using
pr_article_cat_article_id_key
> on pr_article_cat (cost=0.00..2.01 rows=1 width=8)
> -> Seq Scan on pr_cat (cost=0.00..1.10 rows=10
> width=16)
> -> Index Scan using pr_article_pkey on pr_article
> (cost=0.00..2.03 rows=1 width=20)
>
> EXPLAIN
>
>
> parent_report_new=> explain
> parent_report_new-> SELECT DISTINCT ON (pr_cat.name) pr_cat.name,
> pr_cat.prog_name FROM
> parent_report_new-> pr_article, pr_article_age, pr_article_cat, pr_cat
WHERE
> parent_report_new-> pr_article_age.age_id = 1 AND
pr_article_age.article_id
> =
> parent_report_new-> pr_article.article_id AND pr_article.article_id =
> pr_article_cat.article_id
> parent_report_new-> AND pr_article_cat.cat_id = pr_cat.cat_id AND
> pr_article.is_active = true
> parent_report_new-> AND pr_article.is_audio = false AND
> pr_article.is_approved = true AND
> parent_report_new-> pr_article.release_date < 982093578 AND
> ((pr_article.end_date = 0) or
> parent_report_new(> (pr_article.end_date > 982093578 ))
> parent_report_new-> ;
> NOTICE: QUERY PLAN:
>
> Unique (cost=7.64..7.64 rows=0 width=44)
> -> Sort (cost=7.64..7.64 rows=1 width=44)
> -> Nested Loop (cost=0.00..7.63 rows=1 width=44)
> -> Nested Loop (cost=0.00..6.40 rows=1 width=16)
> -> Nested Loop (cost=0.00..4.36 rows=1 width=12)
> -> Seq Scan on pr_article_age (cost=0.00..1.68
> rows=1 width=4)
> -> Index Scan using
pr_article_cat_article_id_key
> on pr_article_cat (cost=0.00..2.01 rows=1 width=8)
> -> Index Scan using pr_article_pkey on pr_article
> (cost=0.00..2.03 rows=1 width=4)
> -> Seq Scan on pr_cat (cost=0.00..1.10 rows=10 width=28)
>
> EXPLAIN
>
>
>
>
>
> ----- Original Message -----
> From: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> To: "Matt Friedman" <matt(at)sprynewmedia(dot)com>
> Cc: "PgSql General List" <pgsql-general(at)postgresql(dot)org>
> Sent: Tuesday, February 13, 2001 3:46 PM
> Subject: Re: SELECT (sometimes) returning Zero Rows?
>
>
> > "Matt Friedman" <matt(at)daart(dot)ca> writes:
> > > We are having what seems like a baffling problem to me.
> > > On occasion, our SELECTs will return zero rows when we know with
> certainty
> > > that they should be returning at least some rows. No error occurs, the
> > > select just behaves as if the db is empty or something.
> >
> > What PG version is this? What does EXPLAIN show for the misbehaving
> > queries?
> >
> > regards, tom lane
> >
> >
>
>
>