Thread: Strange interaction with php's pg_query

I'm a little burned out right now, so I figured I'll ask the
question before calling quits for tonight, and (hopefully) the
answer will be in my inbox come morning. ;)

I'm building a web app in php with Postgre as the backend db.
I'm pretty new to Postgre, but I've done a lot of work with
php+MySQL.

I have a simple table as follows:

create table sysinfo (
    attname char(30) primary key,
    attvalue text
    ) without oids

And if I start psql and enter:
"select * from sysinfo"
I get the expected list of name/value pairs that were entered
into the table at an earlier operation.

However, if I execute the same query using pg_query() in php,
I get the error:
Warning: pg_query() query failed: ERROR: Relation "sysinfo" does not exist in
/usr/home/group/webpages/epd/class/base.class.phpon line 44 

This really confuses me, as "sysinfo" isn't even a relation and
why should the parser even imagine it to be a relation?  I've
tried rearranging the SELECT statement (with and without WHERE
clauses, with and without FROM clause).  Every incarnation I try
works fine in the psql program, but gives the same error (as above)
in php.

I'm using FreeBSD 4.5-STABLE as of April 23rd, Postgres 7.2.1_1,
mod_php4-4.2.0, and apache-1.3.24_7 all installed from FreeBSD
ports collection.

Any advice is much welcome.

--
Bill Moran
Potential Technology
http://www.potentialtech.com

Bill Moran <wmoran@potentialtech.com> writes:
> And if I start psql and enter:
> "select * from sysinfo"
> I get the expected list of name/value pairs that were entered
> into the table at an earlier operation.

> However, if I execute the same query using pg_query() in php,
> I get the error:
> Warning: pg_query() query failed: ERROR: Relation "sysinfo" does not exist in
/usr/home/group/webpages/epd/class/base.class.phpon line 44 

I think your php client must be connecting to a different database
than you are connecting to when you run psql by hand.  If you are
allowing the default choice of database name = user name to be taken,
this isn't real surprising...

> This really confuses me, as "sysinfo" isn't even a relation and
> why should the parser even imagine it to be a relation?

"relation" and "table" are more or less synonymous in PG error messages.
(IMHO the exact meaning of "relation" in our code is "something with a
pg_class entry", which covers also views, sequences, indexes, etc; but
you probably didn't need to know that.)

            regards, tom lane

Tom Lane wrote:
> Bill Moran <wmoran@potentialtech.com> writes:
>
>>And if I start psql and enter:
>>"select * from sysinfo"
>>I get the expected list of name/value pairs that were entered
>>into the table at an earlier operation.
>
>>However, if I execute the same query using pg_query() in php,
>>I get the error:
>>Warning: pg_query() query failed: ERROR: Relation "sysinfo" does not exist in
/usr/home/group/webpages/epd/class/base.class.phpon line 44 
>
> I think your php client must be connecting to a different database
> than you are connecting to when you run psql by hand.  If you are
> allowing the default choice of database name = user name to be taken,
> this isn't real surprising...

That's it.  I guess it really was _too_ late for me to be working
on this last night.  Thanks.

>>This really confuses me, as "sysinfo" isn't even a relation and
>>why should the parser even imagine it to be a relation?
>
> "relation" and "table" are more or less synonymous in PG error messages.
> (IMHO the exact meaning of "relation" in our code is "something with a
> pg_class entry", which covers also views, sequences, indexes, etc; but
> you probably didn't need to know that.)

That helps too.  Knowing the terminology that Postgre uses will help
me be less of a dummy when interpreting error messages in the future.

--
Bill Moran
Potential Technology
http://www.potentialtech.com

Sorry, but we really need to see your code.  My guess is that you've got a
simple syntax error in your php script and php thinks it's interpreting
your sql statement.

On Mon, 13 May 2002, Bill Moran wrote:

> I'm a little burned out right now, so I figured I'll ask the
> question before calling quits for tonight, and (hopefully) the
> answer will be in my inbox come morning. ;)
>
> I'm building a web app in php with Postgre as the backend db.
> I'm pretty new to Postgre, but I've done a lot of work with
> php+MySQL.
>
> I have a simple table as follows:
>
> create table sysinfo (
>     attname char(30) primary key,
>     attvalue text
>     ) without oids
>
> And if I start psql and enter:
> "select * from sysinfo"
> I get the expected list of name/value pairs that were entered
> into the table at an earlier operation.
>
> However, if I execute the same query using pg_query() in php,
> I get the error:
> Warning: pg_query() query failed: ERROR: Relation "sysinfo" does not exist in
/usr/home/group/webpages/epd/class/base.class.phpon line 44 
>
> This really confuses me, as "sysinfo" isn't even a relation and
> why should the parser even imagine it to be a relation?  I've
> tried rearranging the SELECT statement (with and without WHERE
> clauses, with and without FROM clause).  Every incarnation I try
> works fine in the psql program, but gives the same error (as above)
> in php.
>
> I'm using FreeBSD 4.5-STABLE as of April 23rd, Postgres 7.2.1_1,
> mod_php4-4.2.0, and apache-1.3.24_7 all installed from FreeBSD
> ports collection.
>
> Any advice is much welcome.
>
>