Re: NATURAL INNER JOIN no longer working - Mailing list 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@marinconsulting.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@postgresql.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