Oid Questions

Hello.

Zeev Suraski <zeev@php.net> is making some changes to the PostgreSQL code
in PHP3 so that the Oid is returned in the pg_exec funtion if it is an
insert.

What is the size of the Oid (unsigned, signed etc) and will it ever be
zero.

typedef unsigned int Oid;

A zero value for OID is reserved for an Invalid OID. If it returns a
zero, I would pass it back to the application. You may want to call
libpq's PQoidStatus() to get this information. That is a new function
for 6.2.1.

--
Bruce Momjian
maillist@candle.pha.pa.us

At 16:25 22/01/98 -0500, Bruce Momjian wrote:

A zero value for OID is reserved for an Invalid OID. If it returns a
zero, I would pass it back to the application. You may want to call
libpq's PQoidStatus() to get this information. That is a new function
for 6.2.1.

Ok, please tell me if this logic is correct. It describes the logic behind
the implementation of pg_exec() in PHP.

First, the query is executed. If the status is PGRES_EMPTY_QUERY or
PGRES_BAD_RESPONSE or PGRES_NONFATAL_ERROR or PGRES_FATAL_ERROR, return
FALSE (failure).
If it returns PGRES_COMMAND_OK (which is, from what I understand, a
successful query that is, by definition, not supposed to return rows) -
check PQoidStatus(). If it's not null, atoi() of it is not 0, return its
return value as a numeric integer. If it is zero, assume that this was a
successful query that didn't cause the oid to be updated, and return TRUE
(successful query that does not return rows or oid).
Otherwise, assume that that was a succesful query that returned rows -
return a PostgresSQL result identifier.

Sorry, got lost in this paragraph.

I guess that my key question is, whether or not it's correct to assume
PGRES_COMMAND_OK + PQoidStatus() == 0 or "0" => successful query that did
not return rows and did not update the oid, OR, is it possible that
PQoidStatus() of zero reflects some error, even though the return value was
PGRES_COMMAND_OK?

It is the result status, PGRES_COMMAND_OK that is important. The
PQoidStatus() is really there just as an aid. Only an INSERT returns an
OID as part of the result string. We use this function to just pull it
out of the string. If they do a SELECT ... INTO, they are inserting
zero or multiple OIDs, so this value is the last oid inserted.

We also have new in 6.2.1 PQcmdTuples(), which shows how many rows were
affected by the INSERT, UPDATE, or DELETE. Again, it comes out of the
string returned in the Result structure.

See libpq/fe-exec.c for the source to both of these.

PHP can return different datatypes from the same function, in case you were
wondering :)

Yep, I remember.

--
Bruce Momjian
maillist@candle.pha.pa.us

I'll try to rephrase the question without taking 3 complex paragraphs to do
that :)

Is there a way to know a PostgresSQL result holds NO interesting
information (no rows, no oids, no nothing)?

The more I think of it, the more it seems like this isn't the case with
PostgresSQL. Moreover, it seems like in most cases the result holds one
interesting tidbit of information or another. When I wrote the MySQL
module, basically, I made any query that did not return rows (not including
select's that returned 0 rows) but succeeded return TRUE instead of a
result handler, since there wasn't much point at keeping that result. With
MySQL the information about the last inserted id (mysql_insert_it(), I
think it's comparable to the last oid in pgsql) and the number of affected
rows can be obtained from the 'server' structure, and not the restul
structure as it is with Postgres.

I guess I'll change the Postgres module to always keep the result
structures and return result identifiers on a successful query.

Yes, all the return information for results that return zero rows are in
the Result structure, so you can have multiple results open at the same
time, and query them separately. They remain valid until PQclear()'ed.

--
Bruce Momjian
maillist@candle.pha.pa.us

At 16:25 22/01/98 -0500, Bruce Momjian wrote:

A zero value for OID is reserved for an Invalid OID. If it returns a
zero, I would pass it back to the application. You may want to call
libpq's PQoidStatus() to get this information. That is a new function
for 6.2.1.

Ok, please tell me if this logic is correct. It describes the logic behind
the implementation of pg_exec() in PHP.

First, the query is executed. If the status is PGRES_EMPTY_QUERY or
PGRES_BAD_RESPONSE or PGRES_NONFATAL_ERROR or PGRES_FATAL_ERROR, return
FALSE (failure).
If it returns PGRES_COMMAND_OK (which is, from what I understand, a
successful query that is, by definition, not supposed to return rows) -
check PQoidStatus(). If it's not null, atoi() of it is not 0, return its
return value as a numeric integer. If it is zero, assume that this was a
successful query that didn't cause the oid to be updated, and return TRUE
(successful query that does not return rows or oid).
Otherwise, assume that that was a succesful query that returned rows -
return a PostgresSQL result identifier.

I guess that my key question is, whether or not it's correct to assume
PGRES_COMMAND_OK + PQoidStatus() == 0 or "0" => successful query that did
not return rows and did not update the oid, OR, is it possible that
PQoidStatus() of zero reflects some error, even though the return value was
PGRES_COMMAND_OK?

PHP can return different datatypes from the same function, in case you were
wondering :)

Zeev
---
Zeev Suraski <zeev@php.net>
Web programmer, System administrator, Netvision LTD
http://bourbon.netvision.net.il/ ICQ: 1450980
For a PGP public key, finger bourbon@netvision.net.il

I'll try to rephrase the question without taking 3 complex paragraphs to do
that :)

Is there a way to know a PostgresSQL result holds NO interesting
information (no rows, no oids, no nothing)?

The more I think of it, the more it seems like this isn't the case with
PostgresSQL. Moreover, it seems like in most cases the result holds one
interesting tidbit of information or another. When I wrote the MySQL
module, basically, I made any query that did not return rows (not including
select's that returned 0 rows) but succeeded return TRUE instead of a
result handler, since there wasn't much point at keeping that result. With
MySQL the information about the last inserted id (mysql_insert_it(), I
think it's comparable to the last oid in pgsql) and the number of affected
rows can be obtained from the 'server' structure, and not the restul
structure as it is with Postgres.

I guess I'll change the Postgres module to always keep the result
structures and return result identifiers on a successful query.

Zeev
---
Zeev Suraski <zeev@php.net>
Web programmer, System administrator, Netvision LTD
http://bourbon.netvision.net.il/ ICQ: 1450980
For a PGP public key, finger bourbon@netvision.net.il