PostgreSQL libpq“整数超出范围”以二进制形式发送整数时出错

发布于 2024-08-02 12:30:16 字数 1208 浏览 10 评论 0原文

我正在尝试使用以下稍微简单的代码将一些整数插入 Postgres 表中。

#include <libpq-fe.h>
#include <stdio.h>
#include <stdint.h>

int main() {
  int64_t i = 0;
  PGconn * connection = PQconnectdb( "dbname='babyfood'" );
  if( !connection || PQstatus( connection ) != CONNECTION_OK )
    return 1;
  printf( "Number: " );
  scanf( "%d", &i );

  char * params[1];
  int param_lengths[1];
  int param_formats[1];
  param_lengths[0] = sizeof( i );
  param_formats[0] = 1;
  params[0] = (char*)&i;
  PGresult * res =  PQexecParams( connection, 
                                  "INSERT INTO intlist VALUES ( $1::int8 )",
                                  1,
                                  NULL,
                                  params,
                                  param_lengths,
                                  param_formats,
                                  0 );
  printf( "%s\n", PQresultErrorMessage( res ) );
  PQclear( res );
  PQfinish( connection );
  return 0;
}

我得到以下结果：

Number: 55
ERROR:  integer out of range

Number: 1
ERROR:  integer out of range

我非常确定 int64_t 在任何正常平台上始终适合 8 字节整数。我做错了什么？

原文

I'm attempting to insert some integers into a Postgres table with the following somewhat simple code.

#include <libpq-fe.h>
#include <stdio.h>
#include <stdint.h>

int main() {
  int64_t i = 0;
  PGconn * connection = PQconnectdb( "dbname='babyfood'" );
  if( !connection || PQstatus( connection ) != CONNECTION_OK )
    return 1;
  printf( "Number: " );
  scanf( "%d", &i );

  char * params[1];
  int param_lengths[1];
  int param_formats[1];
  param_lengths[0] = sizeof( i );
  param_formats[0] = 1;
  params[0] = (char*)&i;
  PGresult * res =  PQexecParams( connection, 
                                  "INSERT INTO intlist VALUES ( $1::int8 )",
                                  1,
                                  NULL,
                                  params,
                                  param_lengths,
                                  param_formats,
                                  0 );
  printf( "%s\n", PQresultErrorMessage( res ) );
  PQclear( res );
  PQfinish( connection );
  return 0;
}

I get the following results:

Number: 55
ERROR:  integer out of range

Number: 1
ERROR:  integer out of range

I'm pretty sure that an int64_t will always fit in an 8 byte integer on any sane platform. What am I doing wrong?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

靖瑶 2024-08-09 12:30:16

而不是：

params[0] = (char*)&i;

您应该使用：

#include <endian.h>
/* ... */
int64_t const i_big_endian = htobe64(i);
params[0] = (char*)&i_big_endian;

htobe64 函数将在小端上切换端序，而在大端上不执行任何操作。

放弃你的 flip_endian 函数，因为它会使你的程序与大端/双端计算机不兼容，如 PowerPC、Alpha、Motorola、SPARC、IA64 等。即使你的程序不希望在它们上运行是一种糟糕的风格，缓慢且容易出错。

Instead of:

params[0] = (char*)&i;

you should use:

#include <endian.h>
/* ... */
int64_t const i_big_endian = htobe64(i);
params[0] = (char*)&i_big_endian;

A htobe64 function will switch endianness on little-endian, and do nothing on big-endian.

Ditch your flip_endian function, as it would make your program incompatible with big-endian/bi-endian computers, like PowerPC, Alpha, Motorola, SPARC, IA64 etc. Even if your program does not expect to be run on them it is a bad style, slow and error prone.

回复收藏 0 原文

差↓一点笑了 2024-08-09 12:30:16

好吧，看来这是一个字节序问题，这仍然不能完全解释它，因为小字节序（即 x86）64 位有符号整数应该适合大字节序 64 位整数，反之亦然，他们只是被损坏。然而，交换整数的字节序会产生正确的值。交换是通过以下函数完成的：

int64_t flip_endian( int64_t endi ) {
  char* bytearray;
  char swap;
  int64_t orig = endi;
  int i;

  bytearray = (char*)&orig;

  for( i = 0; i < sizeof( orig )/2; ++i ) {
    swap = bytearray[i];
    bytearray[i] = bytearray[ sizeof( orig ) - i - 1 ];
    bytearray[ sizeof( orig ) - i - 1 ] = swap;
  }

  return orig;

}

Alright, it appears that it's an endian issue, which still doesn't quite explain it, since a little-endian (i.e. x86) 64 bit signed integer should fit in a big-endian 64 bit integer and vice versa, they'd just be corrupted. Swapping the endian on the integer yields the correct value, however. Swapping is done with the following function:

int64_t flip_endian( int64_t endi ) {
  char* bytearray;
  char swap;
  int64_t orig = endi;
  int i;

  bytearray = (char*)&orig;

  for( i = 0; i < sizeof( orig )/2; ++i ) {
    swap = bytearray[i];
    bytearray[i] = bytearray[ sizeof( orig ) - i - 1 ];
    bytearray[ sizeof( orig ) - i - 1 ] = swap;
  }

  return orig;

}

回复收藏 0 原文