Skip site navigation (1) Skip section navigation (2)

Re: NATURAL INNER JOIN no longer working

From: <operationsengineer1(at)yahoo(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: NATURAL INNER JOIN no longer working
Date: 2005-07-18 18:15:32
Message-ID: 20050718181532.62696.qmail@web33307.mail.mud.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-novice
Diana,

can you go back to a prior backup of your db (the
query should still work) and then make modifications
one at a time and test the query?  i wouldn't thinking
adding a column would impact this at all.

you can then use what you learn to help resolve your
current issue.

i also assume pgsql is the same version / revision as
before (when the query worked) and that the data is
the same or similar.  if not, that could be a cause,
too.

--- Diana Nemirovsky <diana(at)marinconsulting(dot)com>
wrote:

> Sorry for the vagueness of the last post, I'll try
> again.
> 
> After adding columns onto each table in my schema,
> queries that used to 
> work no longer work. I ran the following code on
> each table:
> 
> ALTER TABLE person ADD COLUMN record_created
> TIMESTAMP;
> ALTER TABLE person ADD COLUMN record_created_by_user
> INT2;
> ALTER TABLE person ADD COLUMN record_modified
> TIMESTAMP;
> ALTER TABLE person ADD COLUMN
> record_modified_by_user INT2;
> 
> Shortly after having done so, queries that used to
> work now don't. I can't 
> say with certainty that the two are related, but the
> timing fits. So, for 
> example,
> SELECT id_blog
> FROM blog NATURLAL INNER JOIN person_blog_role
> WHERE ID_blog_role != 6 AND id_person=590 and
> blog.top_level='news' AND 
> blog.title<>'School Notes'
> GROUP by id_blog
> 
> now returns nothing, whereas before it correctly
> found a handful of records.
> 
> If I rewrite the query as
> 
> SELECT blog.id_blog
> FROM blog, person_blog_role
> WHERE ID_blog_role != 6 AND person_blog_role.ID_blog
> = blog.ID_blog AND 
> id_person=590 and blog.top_level='news' AND
> blog.title<>'School Notes'
> GROUP by blog.id_blog
> 
> It works fine.
> 
> An example of the error I get is
> Warning: pg_fetch_result() 
>
[<http://www.codethis.org/function.pg-fetch-result>function.pg-fetch-result]:
> 
> Unable to jump to row 0 on PostgreSQL result index
> 48 in 
> /usr/local/www/codethis.org/www/lib/dbclasses.php on
> line 94
> 
> The dbClasses function just abstracts the database
> calls, and the line in 
> question in dbclasses is:
> return pg_fetch_result($this->cursor, $row, $field
> );
> 
> I also in the same time frame added a trigger on one
> table (and it wouldn't 
> for example, have touched the tables referenced
> above), but I dropped it 
> after this happened to eliminate it as a possible
> problem.
> 
> This has left me dead in the water, so any help or
> thoughts are appreciated.
> 
> 
> 
> 
> Diana Nemirovsky
> Marin Consulting, Inc.
> 770 Menlo Ave, Ste 223
> Menlo Park, CA 94025
> v (650) 617-8699
> f (650) 833-0790
> http://www.marinconsulting.com 
> 
> 
> 
> 
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please
> send an appropriate
>        subscribe-nomail command to
> majordomo(at)postgresql(dot)org so that your
>        message can get through to the mailing list
> cleanly
> 



		
____________________________________________________
Start your day with Yahoo! - make it your home page 
http://www.yahoo.com/r/hs 
 

In response to

pgsql-novice by date

Next:From: Sean MontagueDate: 2005-07-18 22:19:49
Subject: XML
Previous:From: Stephan SzaboDate: 2005-07-18 14:32:04
Subject: Re: NATURAL INNER JOIN no longer working

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group