Обсуждение: SELECT (sometimes) returning Zero Rows?

Поиск
Список
Период
Сортировка

SELECT (sometimes) returning Zero Rows?

От
"Matt Friedman"
Дата:
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.

The queries on the page are all selects. No inserts or updates so I didn't
see the need for a transaction. I can't see why that would help anyway.

I've included the selects from the page below.

Anyone have an experience like this? Please let me know if more info is
req'd.


SELECT pr_article.title, pr_article.article_id FROM pr_article WHERE
pr_article.article_id = pr_article_age.article_id AND pr_article_age.age_id
= 1 AND pr_article.is_active = true AND pr_article.is_approved = true AND
pr_article.release_date < 982093578 AND ((pr_article.end_date = 0) or
(pr_article.end_date > 982093578 )) ORDER BY pr_article.release_date DESC
LIMIT 2

SELECT pr_article.title, pr_article.article_id FROM pr_article WHERE
pr_article.article_id = pr_article_age.article_id AND pr_article_age.age_id
= 1 AND pr_article.is_active = true AND pr_article.is_approved = true AND
pr_article.release_date < 982093578 AND ((pr_article.end_date = 0) or
(pr_article.end_date > 982093578 )) ORDER BY pr_article.release_date DESC
LIMIT 3

SELECT DISTINCT ON (release_date, article_id) pr_article.title,
pr_article.article_id, pr_cat.prog_name FROM pr_article WHERE
pr_article.article_id = pr_article_age.article_id AND pr_article_age.age_id
= 1 AND pr_article_cat.cat_id=pr_cat.cat_id AND pr_article.article_id =
pr_article_cat.article_id AND pr_article.is_active = true AND
pr_article.is_approved = true AND pr_article.release_date < 982093578 AND
((pr_article.end_date = 0) or (pr_article.end_date > 982093578 )) ORDER BY
pr_article.release_date DESC LIMIT 5

SELECT DISTINCT ON (pr_cat.name) pr_cat.name, pr_cat.prog_name FROM
pr_article, pr_article_age, pr_article_cat, pr_cat WHERE
pr_article_age.age_id = 1 AND pr_article_age.article_id =
pr_article.article_id AND pr_article.article_id = pr_article_cat.article_id
AND pr_article_cat.cat_id = pr_cat.cat_id AND pr_article.is_active = true
AND pr_article.is_audio = false AND pr_article.is_approved = true AND
pr_article.release_date < 982093578 AND ((pr_article.end_date = 0) or
(pr_article.end_date > 982093578 ))


Matt Friedman

Re: SELECT (sometimes) returning Zero Rows?

От
Tom Lane
Дата:
"Matt Friedman" <matt@daart.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

Re: SELECT (sometimes) returning Zero Rows?

От
"Matt Friedman"
Дата:
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@sss.pgh.pa.us>
To: "Matt Friedman" <matt@sprynewmedia.com>
Cc: "PgSql General List" <pgsql-general@postgresql.org>
Sent: Tuesday, February 13, 2001 3:46 PM
Subject: Re: SELECT (sometimes) returning Zero Rows?


> "Matt Friedman" <matt@daart.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
>
>

Re: Re: SELECT (sometimes) returning Zero Rows?

От
Christopher Sawtell
Дата:
On Wed, 14 Feb 2001 15:03, you wrote:
> 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.

This has the same smell about it as the one a couple of days ago
where the CR characters in a file loaded into the database using COPY were
upsetting things.

So, How did you populate your database?

--
Sincerely etc.,

 NAME       Christopher Sawtell
 CELL PHONE 021 257 4451
 ICQ UIN    45863470
 EMAIL      csawtell @ xtra . co . nz
 CNOTES     ftp://ftp.funet.fi/pub/languages/C/tutorials/sawtell_C.tar.gz

 -->> Please refrain from using HTML or WORD attachments in e-mails to me
<<--

Re: Re: SELECT (sometimes) returning Zero Rows?

От
"Matt Friedman"
Дата:
> This has the same smell about it as the one a couple of days ago
> where the CR characters in a file loaded into the database using COPY were
> upsetting things.

I didn't use COPY but...
I used a dump created by pg_dump. We had some problems with the database a
while ago so I recreated it using a recent dump file.
To reimport it I had psql read it's commands from the file and do it's thing
that way. It ran smoothly and I didn't think anything of it.
The command I used would have been:
$psql -f /path/to/sql/file.sql
Could this be causing the problem? If so, how would I go about fixing it?

Many thanks,
Matt Friedman


----- Original Message -----
From: "Christopher Sawtell" <csawtell@xtra.co.nz>
To: "Matt Friedman" <matt@sprynewmedia.com>
Cc: "PgSql General List" <pgsql-general@postgresql.org>
Sent: Tuesday, February 13, 2001 6:58 PM
Subject: Re: Re: SELECT (sometimes) returning Zero Rows?


> On Wed, 14 Feb 2001 15:03, you wrote:
> > 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.
>
>
> So, How did you populate your database?
>
> --
> Sincerely etc.,
>
>  NAME       Christopher Sawtell
>  CELL PHONE 021 257 4451
>  ICQ UIN    45863470
>  EMAIL      csawtell @ xtra . co . nz
>  CNOTES     ftp://ftp.funet.fi/pub/languages/C/tutorials/sawtell_C.tar.gz
>
>  -->> Please refrain from using HTML or WORD attachments in e-mails to me
> <<--
>
>
>

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

От
"Matt Friedman"
Дата:
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@daart.ca>
To: "Tom Lane" <tgl@sss.pgh.pa.us>; "PgSql General List"
<pgsql-general@postgresql.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@sss.pgh.pa.us>
> To: "Matt Friedman" <matt@sprynewmedia.com>
> Cc: "PgSql General List" <pgsql-general@postgresql.org>
> Sent: Tuesday, February 13, 2001 3:46 PM
> Subject: Re: SELECT (sometimes) returning Zero Rows?
>
>
> > "Matt Friedman" <matt@daart.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
> >
> >
>
>
>

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

От
"Dan Wilson"
Дата:
> 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.

This is a known problem with the persistent connections through PHP.  IIRC,
we have someone working on this directly in PHP.  Correct me if I'm wrong.

> 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?

Because of the above mentioned problems, I have never used persisten
connections, and I don't see much of a lag.  I suppose it all depends on
your resources.

-Dan