Thread: libpq doing strange things
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?
Thanks,Daniel Andersen
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
On Mon, 7 Jan 2002 19:54, you wrote: > 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 Hrm i probably should have fixed up that earlier mistake in my code before copying and pasting. The pgres_tuples_ok thing is irrelevant,the printed results show pgres_command_ok is being returned, so as i said things should theoretically be ok. But they aren't. The query is not being executed correctly as the database is remaining unmodified, despite the query saying it has updated one row (the expected result). If i copy the query word for word into the psql database interface, it is executed perfectly and update the one record as it should. I probably just didn't explain this well enough in my original email :)Daniel Andersen
Daniel Andersen <zedar@free2air.com.au> writes:
> Hrm i probably should have fixed up that earlier mistake in my code before
> copying and pasting. The pgres_tuples_ok thing is irrelevant,the printed
> results show pgres_command_ok is being returned, so as i said things should
> theoretically be ok. But they aren't. The query is not being executed
> correctly as the database is remaining unmodified, despite the query saying
> it has updated one row (the expected result).
If the backend says it updated one row, then I'm reasonably confident
that it updated one row. I'll bet your problem is that you're not
committing the transaction (eg, you issued BEGIN but no COMMIT).
In that case, the updated row never becomes visible outside your own
transaction.
> If i copy the query word for
> word into the psql database interface, it is executed perfectly and update
> the one record as it should.
This pretty much exonerates both the backend and libpq, seeing as how
psql is just an application built on top of libpq. Time to start
looking at your own application logic.
regards, tom lane