Thread: [BUGS] BUG? select count(*) from table don't get value via PQgetvalue() function in libpq(C)
[BUGS] BUG? select count(*) from table don't get value via PQgetvalue() function in libpq(C)
Hi, postgresql:
First, my englist is not good. So I try to my best. Hope you can understand me.
I programing a program use linux via C language. And I used libpq connect to postgresql.
Then I want calculate rows in a table. So I write PQexecparams() perfrom sql statement: select count(*) from tablename.
Like this:
PGresult *res;
PQtrace(pgconn, stdout);
res = PQexecParams(pgconn, "select count(*) from chat_connstate", 0, NULL, NULL, NULL, NULL, 1);
Then I check res variable use
PQresultStatus(res) == PGRES_TUPLES_OK
that the status is successed.
So, I use PQgetvalue() function get the value, like this:
Int result = *((int*)PQgetvalue(res, 0, 0));
Or
Long result = *((long*)PQgetvalue(res, 0, 0);
But, the result variable value is zero, the I use PQfname(res, 0) and PQfnumber(res, “count”) check status that output is corrected.
So, I change my code to:
Int result = *((int*)PQgetvalue(res, 0, PQfnumber(res, “count”);
But, the result value still is zero.
I login postgresql use psql, then perfrom select count(*) from tablename. The output is current, like:
chatdb=# select count(*) from chat_connstate;
count
-------
1
(1 行记录)
Then I perfrom “select * from tablename” in PQexecparams, and get the values. The values is currect this time.
So, I test
Select sum(*) from tablename;
Int result = *((int*)PQgetvalue(res, 0, 0); //the result value is zero
Select avg(*) from tablename;
Double result = *((double*)PQgetvalue(res, 0, 0); // the result value is not zero, but still is not equal to psql output.
Finally, I have no ideas for new test way. Please help me.
Thanks.
output infomations use psql and PQtrace() function in gdb: (The system and postgresql environment in the bottom.)
chatdb=# select * from chat_connstate;
id | ip | port | connfd | account | token | expires | create_date
----+------------------------------------------------+------+--------+---------+------------------------------------------+----------------------------+----------------------------
1 | 127.0.0.1 | 1 | 7 | | 34a57ff275715c87235bf880f4c642d2539372fc | 2017-02-18 22:33:55.381506 | 2017-02-18 22:33:55.381506
(1 行记录)
1. Select count(*) from chat_connstate
(gdb)
41 chat_psql_init_connstate(1);
(gdb) s
chat_psql_init_connstate (clear=1) at chat_psql.c:38
38 PQtrace(pgconn, stdout);
(gdb) n
39 res = PQexecParams(pgconn, "select count(*) from chat_connstate",
(gdb)
To backend> Msg P
To backend> ""
To backend> "select count(*) from chat_connstate"
To backend (2#)> 0
To backend> Msg complete, length 44
To backend> Msg B
To backend> ""
To backend> ""
To backend (2#)> 0
To backend (2#)> 0
To backend (2#)> 1
To backend (2#)> 1
To backend> Msg complete, length 15
To backend> Msg D
To backend> P
To backend> ""
To backend> Msg complete, length 7
To backend> Msg E
To backend> ""
To backend (4#)> 0
To backend> Msg complete, length 10
To backend> Msg S
To backend> Msg complete, length 5
From backend> 1
From backend (#4)> 4
From backend> 2
From backend (#4)> 4
From backend> T
From backend (#4)> 30
From backend (#2)> 1
From backend> "count"
From backend (#4)> 0
From backend (#2)> 0
From backend (#4)> 20
From backend (#2)> 8
From backend (#4)> -1
From backend (#2)> 1
From backend> D
From backend (#4)> 18
From backend (#2)> 1
From backend (#4)> 8
From backend (8)>
From backend> C
From backend (#4)> 13
From backend> "SELECT 1"
From backend> Z
From backend (#4)> 5
From backend> Z
From backend (#4)> 5
From backend> I
43 if(chat_psql_resultstate(res, PGRES_TUPLES_OK) == CHAT_ERROR){
(gdb) n
49 int result = *((int*)PQgetvalue(res, 0, 0));
(gdb) n
50 PQuntrace(pgconn);
(gdb) print result
$111 = 0
---
chatdb=# select count(*) from chat_connstate;
count
-------
1
2
41 chat_psql_init_connstate(1);
(gdb) s
chat_psql_init_connstate (clear=1) at chat_psql.c:38
38 PQtrace(pgconn, stdout);
(gdb) n
39 res = PQexecParams(pgconn, "select sum(connfd) from chat_connstate",
(gdb) n
To backend> Msg P
To backend> ""
To backend> "select sum(connfd) from chat_connstate"
To backend (2#)> 0
To backend> Msg complete, length 47
To backend> Msg B
To backend> ""
To backend> ""
To backend (2#)> 0
To backend (2#)> 0
To backend (2#)> 1
To backend (2#)> 1
To backend> Msg complete, length 15
To backend> Msg D
To backend> P
To backend> ""
To backend> Msg complete, length 7
To backend> Msg E
To backend> ""
To backend (4#)> 0
To backend> Msg complete, length 10
To backend> Msg S
To backend> Msg complete, length 5
From backend> 1
From backend (#4)> 4
From backend> 2
From backend (#4)> 4
From backend> T
From backend (#4)> 28
From backend (#2)> 1
From backend> "sum"
From backend (#4)> 0
From backend (#2)> 0
From backend (#4)> 20
From backend (#2)> 8
From backend (#4)> -1
From backend (#2)> 1
From backend> D
From backend (#4)> 18
From backend (#2)> 1
From backend (#4)> 8
From backend (8)>
From backend> C
From backend (#4)> 13
From backend> "SELECT 1"
From backend> Z
From backend (#4)> 5
From backend> Z
From backend (#4)> 5
From backend> I
43 if(chat_psql_resultstate(res, PGRES_TUPLES_OK) == CHAT_ERROR){
(gdb) n
49 int result = *((int*)PQgetvalue(res, 0, 0));
(gdb)
50 PQuntrace(pgconn);
(gdb) print result
$112 = 0
---
chatdb=# select sum(connfd) from chat_connstate;
sum
-----
7
3.
(gdb) s
chat_psql_init_connstate (clear=1) at chat_psql.c:38
38 PQtrace(pgconn, stdout);
(gdb) n
39 res = PQexecParams(pgconn, "select avg(connfd) from chat_connstate",
(gdb)
To backend> Msg P
To backend> ""
To backend> "select avg(connfd) from chat_connstate"
To backend (2#)> 0
To backend> Msg complete, length 47
To backend> Msg B
To backend> ""
To backend> ""
To backend (2#)> 0
To backend (2#)> 0
To backend (2#)> 1
To backend (2#)> 1
To backend> Msg complete, length 15
To backend> Msg D
To backend> P
To backend> ""
To backend> Msg complete, length 7
To backend> Msg E
To backend> ""
To backend (4#)> 0
To backend> Msg complete, length 10
To backend> Msg S
To backend> Msg complete, length 5
From backend> 1
From backend (#4)> 4
From backend> 2
From backend (#4)> 4
From backend> T
From backend (#4)> 28
From backend (#2)> 1
From backend> "avg"
From backend (#4)> 0
From backend (#2)> 0
From backend (#4)> 1700
From backend (#2)> 65535
From backend (#4)> -1
From backend (#2)> 1
From backend> D
From backend (#4)> 20
From backend (#2)> 1
From backend (#4)> 10
From backend (10)>
From backend> C
From backend (#4)> 13
From backend> "SELECT 1"
From backend> Z
From backend (#4)> 5
From backend> Z
From backend (#4)> 5
From backend> I
43 if(chat_psql_resultstate(res, PGRES_TUPLES_OK) == CHAT_ERROR){
(gdb)
49 double result = *((double*)PQgetvalue(res, 0, 0));
(gdb)
50 PQuntrace(pgconn);
(gdb) print result
$114 = 1.2882297539194999e-231
---
chatdb=# select avg(connfd) from chat_connstate;
avg
--------------------
7.0000000000000000
(1 行记录)
4.
(gdb) s
chat_psql_init_connstate (clear=1) at chat_psql.c:38
38 PQtrace(pgconn, stdout);
(gdb) n
39 res = PQexecParams(pgconn, "select * from chat_connstate",
(gdb)
To backend> Msg P
To backend> ""
To backend> "select * from chat_connstate"
To backend (2#)> 0
To backend> Msg complete, length 37
To backend> Msg B
To backend> ""
To backend> ""
To backend (2#)> 0
To backend (2#)> 0
To backend (2#)> 1
To backend (2#)> 1
To backend> Msg complete, length 15
To backend> Msg D
To backend> P
To backend> ""
To backend> Msg complete, length 7
To backend> Msg E
To backend> ""
To backend (4#)> 0
To backend> Msg complete, length 10
To backend> Msg S
To backend> Msg complete, length 5
From backend> 1
From backend (#4)> 4
From backend> 2
From backend (#4)> 4
From backend> T
From backend (#4)> 202
From backend (#2)> 8
From backend> "id"
From backend (#4)> 70536
From backend (#2)> 1
From backend (#4)> 20
From backend (#2)> 8
From backend (#4)> -1
From backend (#2)> 1
From backend> "ip"
From backend (#4)> 70536
From backend (#2)> 2
From backend (#4)> 1042
From backend (#2)> 65535
From backend (#4)> 50
From backend (#2)> 1
From backend> "port"
From backend (#4)> 70536
From backend (#2)> 3
From backend (#4)> 23
From backend (#2)> 4
From backend (#4)> -1
From backend (#2)> 1
From backend> "connfd"
From backend (#4)> 70536
From backend (#2)> 4
From backend (#4)> 23
From backend (#2)> 4
From backend (#4)> -1
From backend (#2)> 1
From backend> "account"
From backend (#4)> 70536
From backend (#2)> 5
From backend (#4)> 1042
From backend (#2)> 65535
From backend (#4)> 35
From backend (#2)> 1
From backend> "token"
From backend (#4)> 70536
From backend (#2)> 6
From backend (#4)> 25
From backend (#2)> 65535
From backend (#4)> -1
From backend (#2)> 1
From backend> "expires"
From backend (#4)> 70536
From backend (#2)> 7
From backend (#4)> 1114
From backend (#2)> 8
From backend (#4)> -1
From backend (#2)> 1
From backend> "create_date"
From backend (#4)> 70536
From backend (#2)> 8
From backend (#4)> 1114
From backend (#2)> 8
From backend (#4)> -1
From backend (#2)> 1
From backend> D
From backend (#4)> 156
From backend (#2)> 8
From backend (#4)> 8
From backend (8)>
From backend (#4)> 46
From backend (46)> 127.0.0.1
From backend (#4)> 4
From backend (4)>
From backend (#4)> 4
From backend (4)>
From backend (#4)> -1
From backend (#4)> 40
From backend (40)> 34a57ff275715c87235bf880f4c642d2539372fc
From backend (#4)> 8
From backend (8)> ��d�M[1]
From backend (#4)> 8
From backend (8)> ��d�M[1]
From backend> C
From backend (#4)> 13
From backend> "SELECT 1"
From backend> Z
From backend (#4)> 5
From backend> Z
From backend (#4)> 5
From backend> I
And system environment:
sudo lsb_release -a
[sudo] password for ubuntu:
No LSB modules are available.
Distributor ID: Ubuntu
Description: Ubuntu 14.04.5 LTS
Release: 14.04
Codename: trusty
uname -a
Linux ubuntu-VB 4.4.0-62-generic #83~14.04.1-Ubuntu SMP Wed Jan 18 18:10:30 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux
Postgresql environment
Postgresql-9.5 postgresql-contrib-9.5 libpq-dev from apt-get
Dpkg -s postgresql-9.6 infomations:
ubuntu@ubuntu-VB:~/sdb/chat$ sudo dpkg -s postgresql-9.5
[sudo] password for ubuntu:
Package: postgresql-9.5
Status: install ok installed
Priority: optional
Section: database
Installed-Size: 18411
Maintainer: Debian PostgreSQL Maintainers <pkg-postgresql-public@lists.alioth.debian.org>
Architecture: amd64
Version: 9.5.6-1.pgdg14.04+1
Depends: libc6 (>= 2.16), libgssapi-krb5-2 (>= 1.8+dfsg), libldap-2.4-2 (>= 2.4.7), libpam0g (>= 0.99.7.1), libpq5 (>= 9.2~beta3), libssl1.0.0 (>= 1.0.0), libxml2 (>= 2.7.4), postgresql-client-9.5, postgresql-common (>= 158~), tzdata, ssl-cert, locales
Recommends: postgresql-contrib-9.5, sysstat
Suggests: locales-all
Description: object-relational SQL database, version 9.5 server
PostgreSQL is a powerful, open source object-relational database
system. It is fully ACID compliant, has full support for foreign
keys, joins, views, triggers, and stored procedures (in multiple
languages). It includes most SQL:2008 data types, including INTEGER,
NUMERIC, BOOLEAN, CHAR, VARCHAR, DATE, INTERVAL, and TIMESTAMP. It
also supports storage of binary large objects, including pictures,
sounds, or video. It has native programming interfaces for C/C++,
Java, .Net, Perl, Python, Ruby, Tcl, ODBC, among others, and
exceptional documentation.
.
This package provides the database server for PostgreSQL 9.5.
Homepage: http://www.postgresql.org/
Re: [BUGS] BUG? select count(*) from table don't get value via PQgetvalue() function in libpq(C)
"Gao Yanxiao" <553216793@qq.com> writes:
> res = PQexecParams(pgconn, "select count(*) from chat_connstate", 0, NULL, NULL, NULL, NULL, 1);
So you asked for binary-format results ...
> So, I use PQgetvalue() function get the value, like this:
> Int result = *((int*)PQgetvalue(res, 0, 0));
> Or
> Long result = *((long*)PQgetvalue(res, 0, 0);
The first of those is certainly not going to work, and depending on what
platform you're on the second won't either, because it's only extracting
the first 32 bits of the 64-bit result of count().
The other problem you've got here is that the result is coming off the
wire in big-endian byte order, and you're not doing anything to convert
that into native byte order (which more than likely is little-endian).
The reason you're seeing zeroes is that the high 32 bits of the result
are zeroes.
Personally, I would not bother with binary format unless I anticipated
processing enormous volumes of data; it's just too error-prone.
Better to use text and apply strtol() or whatever.
If you really must do it in binary format, here's the way pq_getmsgint64
reads a 64-bit big-endian value:
int64 result;
uint32 h32;
uint32 l32;
pq_copymsgbytes(msg, (char *) &h32, 4);
pq_copymsgbytes(msg, (char *) &l32, 4);
h32 = ntohl(h32);
l32 = ntohl(l32);
result = h32;
result <<= 32;
result |= l32;
which is pretty tedious, but there's no standard 64-bit version
of ntohl/htonl, so we have to swap each half separately.
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs