SEO

Hello!
Here is patch which makes libpq more confortable when working with binary integers.
Automatic conversion intialized by PQsetconvertint(conn,1), so old applications continues to work proper.


--------------------------------------------------------------
diff -ur postgresql-8.2.4.orig/src/interfaces/libpq/exports.txt postgresql-8.2.4/src/interfaces/libpq/exports.txt
--- postgresql-8.2.4.orig/src/interfaces/libpq/exports.txt 2006-08-18 23:52:39.000000000 +0400
+++ postgresql-8.2.4/src/interfaces/libpq/exports.txt 2007-11-26 16:23:25.000000000 +0300
@@ -136,3 +136,6 @@
PQdescribePortal 134
PQsendDescribePrepared 135
PQsendDescribePortal 136
+PQsetconvertint 137
+PQisconverrtint 138
+PQexecPreparedParams 139
diff -ur postgresql-8.2.4.orig/src/interfaces/libpq/fe-exec.c postgresql-8.2.4/src/interfaces/libpq/fe-exec.c
--- postgresql-8.2.4.orig/src/interfaces/libpq/fe-exec.c 2006-10-04 04:30:13.000000000 +0400
+++ postgresql-8.2.4/src/interfaces/libpq/fe-exec.c 2007-11-26 16:20:28.000000000 +0300
@@ -12,16 +12,21 @@
*
*-------------------------------------------------------------------------
*/
+#include "postgres.h"
#include "postgres_fe.h"

#include <ctype.h>
#include <fcntl.h>
+#include <endian.h>
+#include <byteswap.h>

#include "libpq-fe.h"
#include "libpq-int.h"

#include "mb/pg_wchar.h"

+#include "catalog/pg_type.h"
+
#ifdef WIN32
#include "win32.h"
#else
@@ -879,6 +884,43 @@
resultFormat);
}

+
+/*
+ * PQsendQueryPreparedParams
+ * Like PQsendQuery, but execute a previously prepared statement,
+ * parameters passed as helpers for integer converting
+ */
+int
+PQsendQueryPreparedParams(PGconn *conn,
+ const char *stmtName,
+ int nParams,
+ const Oid *paramTypes,
+ const char *const * paramValues,
+ const int *paramLengths,
+ const int *paramFormats,
+ int resultFormat)
+{
+ if (!PQsendQueryStart(conn))
+ return 0;
+
+ if (!stmtName)
+ {
+ printfPQExpBuffer(&conn->errorMessage,
+ libpq_gettext("statement name is a null pointer\n"));
+ return 0;
+ }
+
+ return PQsendQueryGuts(conn,
+ NULL, /* no command to parse */
+ stmtName,
+ nParams,
+ paramTypes,
+ paramValues,
+ paramLengths,
+ paramFormats,
+ resultFormat);
+}
+
/*
* Common startup code for PQsendQuery and sibling routines
*/
@@ -1003,6 +1045,13 @@
if (paramValues && paramValues[i])
{
int nbytes;
+ char const *val;
+ union
+ {
+ uint64 i64;
+ uint32 i32;
+ uint16 i16;
+ } tmpint;

if (paramFormats && paramFormats[i] != 0)
{
@@ -1021,8 +1070,33 @@
/* text parameter, do not use paramLengths */
nbytes = strlen(paramValues[i]);
}
+ #if __BYTE_ORDER == __LITTLE_ENDIAN
+ if (conn->convert_int && paramTypes &&
+ (paramTypes[i]==INT2OID || paramTypes[i]==INT4OID ||
+ paramTypes[i]==INT8OID))
+ {
+ switch(nbytes)
+ {
+ case 2:
+ tmpint.i16=bswap_16(*(uint16*)paramValues[i]);
+ val=(char*)&tmpint;
+ break;
+ case 4:
+ tmpint.i32=bswap_32(*(uint32*)paramValues[i]);
+ val=(char*)&tmpint;
+ break;
+ case 8:
+ tmpint.i64=bswap_64(*(uint64*)paramValues[i]);
+ val=(char*)&tmpint;
+ break;
+ default:
+ val=paramValues[i];
+ }
+ }else
+ #endif
+ val=paramValues[i];
if (pqPutInt(nbytes, 4, conn) < 0 ||
- pqPutnchar(paramValues[i], nbytes, conn) < 0)
+ pqPutnchar(val, nbytes, conn) < 0)
goto sendFailed;
}
else
@@ -1360,6 +1434,30 @@
}

/*
+ * PQexecPreparedParams
+ * Like PQexec, but execute a previously prepared statement,
+ * parameters passed as helpers for integer convering
+ */
+PGresult *
+PQexecPreparedParams(PGconn *conn,
+ const char *stmtName,
+ int nParams,
+ const Oid *paramTypes,
+ const char *const * paramValues,
+ const int *paramLengths,
+ const int *paramFormats,
+ int resultFormat)
+{
+ if (!PQexecStart(conn))
+ return NULL;
+ if (!PQsendQueryPreparedParams(conn, stmtName,
+ nParams, paramTypes, paramValues, paramLengths,
+ paramFormats,resultFormat))
+ return NULL;
+ return PQexecFinish(conn);
+}
+
+/*
* Common code for PQexec and sibling routines: prepare to send command
*/
static bool
@@ -2488,6 +2586,27 @@
}


+/* PQsetconvertint:
+ * returns the null status of a field value.
+ */
+int
+PQsetconvertint(PGconn *conn, int arg)
+{
+ if (!conn) return -1;
+ conn->convert_int=arg;
+ return 0;
+}
+
+/* PQsetconvertint:
+ * returns the null status of a field value.
+ */
+int
+PQisconvertint(PGconn *conn)
+{
+ if (!conn) return -1;
+ return conn->convert_int;
+}
+
/* PQsetnonblocking:
* sets the PGconn's database connection non-blocking if the arg is TRUE
* or makes it non-blocking if the arg is FALSE, this will not protect
diff -ur postgresql-8.2.4.orig/src/interfaces/libpq/fe-protocol3.c postgresql-8.2.4/src/interfaces/libpq/fe-protocol3.c
--- postgresql-8.2.4.orig/src/interfaces/libpq/fe-protocol3.c 2006-10-04 04:30:13.000000000 +0400
+++ postgresql-8.2.4/src/interfaces/libpq/fe-protocol3.c 2007-11-26 16:20:35.000000000 +0300
@@ -12,14 +12,19 @@
*
*-------------------------------------------------------------------------
*/
+#include "postgres.h"
#include "postgres_fe.h"

#include <ctype.h>
#include <fcntl.h>
+#include <endian.h>
+#include <byteswap.h>

#include "libpq-fe.h"
#include "libpq-int.h"

+#include "catalog/pg_type.h"
+
#include "mb/pg_wchar.h"

#ifdef WIN32
@@ -519,6 +524,8 @@
result->attDescs[i].typid = typid;
result->attDescs[i].typlen = typlen;
result->attDescs[i].atttypmod = atttypmod;
+ result->attDescs[i].is_int = typid==INT2OID || typid==INT4OID || typid==INT8OID;
+

if (format != 1)
result->binary = 0;
@@ -674,6 +681,23 @@
return EOF;
/* we have to terminate this ourselves */
tup[i].value[vlen] = '\0';
+ #if __BYTE_ORDER == __LITTLE_ENDIAN
+ if (conn->convert_int && result->attDescs[i].is_int)
+ {
+ switch (tup[i].len)
+ {
+ case 2:
+ *(ut16*)tup[i].value);
+ break;
+ case 4:
+ *(uint32*)tup[i].value=bswap_32(*(uint32*)tup[i].value);
+ break;
+ case 8:
+ *(uint64*)tup[i].value=bswap_64(*(uint64*)tup[i].value);
+ break;
+ }
+ }
+ #endif
}

/* Success! Store the completed tuple in the result */
diff -ur postgresql-8.2.4.orig/src/interfaces/libpq/libpq-fe.h postgresql-8.2.4/src/interfaces/libpq/libpq-fe.h
--- postgresql-8.2.4.orig/src/interfaces/libpq/libpq-fe.h 2006-10-04 04:30:13.000000000 +0400
+++ postgresql-8.2.4/src/interfaces/libpq/libpq-fe.h 2007-11-26 10:17:11.000000000 +0300
@@ -322,6 +322,15 @@
const int *paramFormats,
int resultFormat);

+extern PGresult *PQexecPreparedParams(PGconn *conn,
+ const char *stmtName,
+ int nParams,
+ const Oid *paramTypes,
+ const char *const * paramValues,
+ const int *paramLengths,
+ const int *paramFormats,
+ int resultFormat);
+
/* Interface for multiple-result or asynchronous queries */
extern int PQsendQuery(PGconn *conn, const char *query);
extern int PQsendQueryParams(PGconn *conn,
@@ -342,6 +351,14 @@
const int *paramLengths,
const int *paramFormats,
int resultFormat);
+extern int PQsendQueryPreparedParams(PGconn *conn,
+ const char *stmtName,
+ int nParams,
+ const Oid *paramTypes,
+ const char *const * paramValues,
+ const int *paramLengths,
+ const int *paramFormats,
+ int resultFormat);
extern PGresult *PQgetResult(PGconn *conn);

/* Routines for managing an asynchronous query */
@@ -415,6 +432,9 @@
extern int PQsendDescribePrepared(PGconn *conn, const char *stmt);
extern int PQsendDescribePortal(PGconn *conn, const char *portal);

+extern int PQsetconvertint(PGconn *conn, int arg);
+extern int PQisconvertint(PGconn *conn);
+
/* Delete a PGresult */
extern void PQclear(PGresult *res);

diff -ur postgresql-8.2.4.orig/src/interfaces/libpq/libpq-int.h postgresql-8.2.4/src/interfaces/libpq/libpq-int.h
--- postgresql-8.2.4.orig/src/interfaces/libpq/libpq-int.h 2006-10-04 04:30:13.000000000 +0400
+++ postgresql-8.2.4/src/interfaces/libpq/libpq-int.h 2007-11-26 10:17:11.000000000 +0300
@@ -92,6 +92,7 @@
Oid typid; /* type id */
int typlen; /* type size */
int atttypmod; /* type-specific modifier info */
+ bool is_int;
} PGresAttDesc;

/* Data about a single parameter of a prepared statement */
@@ -358,6 +359,8 @@

/* Buffer for receiving various parts of messages */
PQExpBufferData workBuffer; /* expansible string */
+
+ bool convert_int;
};

/* PGcancel stores all data necessary to cancel a connection. A copy of this


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

xeb@mail.ru wrote:
> Hello!
> Here is patch which makes libpq more confortable when working with binary integers.
> Automatic conversion intialized by PQsetconvertint(conn,1), so old
> applications continues to work proper.

This seems to depend on <byteswap.h> which doesn't look like a portable
interface.

Please note that I'm not saying that fixing that issue means the patch
is acceptable. Personally I'm not sure that this is a worthy goal you
are pursuing here. Wouldn't it be a good idea to propose the feature
first and write the code later?

--
Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J
"Aprender sin pensar es inútil; pensar sin aprender, peligroso" (Confucio)

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> Please note that I'm not saying that fixing that issue means the patch
> is acceptable. Personally I'm not sure that this is a worthy goal you
> are pursuing here. Wouldn't it be a good idea to propose the feature
> first and write the code later?

Indeed. For starters, if we are going to try to provide serious
support in libpq for binary-format parameters, it probably ought to
cover more than just integers. OTOH, I think we've already seen
where that line of thought leads, and it looks pretty ugly too:
http://archives.postgresql.org/pgsql...2/msg00014.php

Anyway, I'd like to see a design discussion about what any libpq API
changes in this area ought to look like, rather than having it be
determined by who can submit the quickest-and-dirtiest patch.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

On Dec 8, 2007 6:50 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> > Please note that I'm not saying that fixing that issue means the patch
> > is acceptable. Personally I'm not sure that this is a worthy goal you
> > are pursuing here. Wouldn't it be a good idea to propose the feature
> > first and write the code later?
>
> Indeed. For starters, if we are going to try to provide serious
> support in libpq for binary-format parameters, it probably ought to
> cover more than just integers. OTOH, I think we've already seen
> where that line of thought leads, and it looks pretty ugly too:
> http://archives.postgresql.org/pgsql...2/msg00014.php
>
> Anyway, I'd like to see a design discussion about what any libpq API
> changes in this area ought to look like, rather than having it be
> determined by who can submit the quickest-and-dirtiest patch.

A major overhaul of this patch is coming...we are addressing some of
the issues that were raised. I think that this is an important issue
that needs to be solved. Dealing with arrays is a complete mess, and
many other things are more difficult than they have to be. User
defined types are problem as well, and marshaling everything into text
is always a good solution.

In the long term, my opinion is that postgresql types have to be
converted into a more pluggable interface that is available into both
the client and the server. It doesn't really make sense to
reimplement the send/receive routines in both the client and the
server...and the patch that we proposed (ditto the OP) did not really
leave room for this in the future. That said, I strongly feel that
libpq should in some fashion do a better job in handling binary data
than it currently does.

Andrew and I are taking this into consideration and will submit a
proposal that we hope that should hopefully deal with things in a more
acceptable way. We understand the ramifications of extending the
libpq api.

merlin

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match