Обсуждение: [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)

От
"Gao Yanxiao"
Дата:

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)> ��dM[1]

From backend (#4)> 8

From backend (8)> ��dM[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)

От
Tom Lane
Дата:
"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