Re: libpq doing strange things

Daniel Andersen wrote:

> Hi,
>
> Is there some undocumented feature in libpq to do with executing update
> queries? I just programmed my mail server to put a mark next to a users name
> whenever they check their email, but for some reason the update query does
> not execute properly. It returns PGRES_COMMAND_OK, and if i check the text
> output of the query it says its updated one record. I know that the
> connection is working ok, because it extracts the password and username from
> the database ok in the query before. Is there any other way to check what is
> going wrong, or is there something else i should be doing i don't know about?
> all i do is load the query into a string using sprintf, and then use that
> string to execute the query like PGexec (conn, string)
> Heres the exact query and commands in case it helps:
>
> snprintf (ptr, 256, "update accounting set mail = 1 where time_stamp
> in (select max (time_stamp) from accounting where username='%s') and
> username='%s';", pinfo->userid, pinfo->userid);
> syslog(LOG_ERR,ptr);
> res2 = PQexec (conn, ptr);
> if (PQresultStatus(res2) != PGRES_TUPLES_OK) syslog(LOG_ERR,
> PQresStatus(PQresultStatus(res2)));
>
> and the output in the syslog is like:
>
> Jan 4 09:21:49 spa teapop[17449]: update accounting set mail = 1 where
> time_stamp in (select max (time_stamp) from accounting where username='bsn')
> and username='bsn';
> Jan 4 09:21:49 spa teapop[17449]: PGRES_COMMAND_OK
>
> which seems like it should be doing ok. Can anyone shed some light on what
> might be causing this problem?

So what are you worried about? EVERYTHING IS FINE :-) Just use the
PGRES_COMMAND_OK return code for your check:

if (PQresultStatus(res2) != PGRES_COMMAND_OK)
syslog(LOG_ERR, PQresStatus(PQresultStatus(res2)));

The PGRES_TUPLES_OK is the return code that informs you about a valid
_selection_ of records - but with an UPDATE statement, you just don't select
anything. Therefore it just can't give you the result PGRES_TUPLES_OK.

N.B.: If you do a select and get a PGRES_TUPLES_OK it is not a necessity
to have records selected. You might have executed a valid query which just
returned an empty result (for example any valid query in a totaly empty database).

There are other queries which only return PGRES_COMMAND_OK (for
example UPDATE and DELETE).

Hope this helps
Andreas