Thread
-
SELECT (sometimes) returning Zero Rows?
Matt Friedman <matt@daart.ca> — 2001-02-13T19:59:00Z
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 <tgl@sss.pgh.pa.us> — 2001-02-13T23:46:26Z
"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 <matt@daart.ca> — 2001-02-14T02:03:59Z
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 <csawtell@xtra.co.nz> — 2001-02-14T02:58:54Z
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 <matt@daart.ca> — 2001-02-14T03:59:48Z
> 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 <matt@daart.ca> — 2001-02-17T19:42:37Z
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 <phppgadmin@acucore.com> — 2001-02-18T00:55:04Z
> 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