Date: Tue, 14 Jun 2022 10:40:06 -0700
From: Mark Wong <markwkm@gmail.com>
To: Greg Stark <stark@mit.edu>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Chapman Flack <chap@anastigmatix.net>,
	Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: real/float example for testlibpq3
Message-ID: <YqjH9qGaIixfZXQo@workstation-mark-wong>
References: <Yhf70G9OYIKueYZi@workstation-mark-wong>
 <CAExHW5tm1FyoLZXdTjdrfRO7ObuoL_p2krc65C5s8D3eWFB50g@mail.gmail.com>
 <2511609.1646061540@sss.pgh.pa.us>
 <621D4CF3.6090808@anastigmatix.net>
 <2746817.1646088530@sss.pgh.pa.us>
 <CAM-w4HM2t7TonV7bz+V27Vvoo0j6hN9-omDo9SHmQ7O58UFYQg@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="FyU4WMjP7imLd5SM"
Content-Disposition: inline
In-Reply-To: 
 <CAM-w4HM2t7TonV7bz+V27Vvoo0j6hN9-omDo9SHmQ7O58UFYQg@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/YqjH9qGaIixfZXQo%40workstation-mark-wong>
Precedence: bulk


--FyU4WMjP7imLd5SM
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

On Wed, Mar 30, 2022 at 01:16:37PM -0400, Greg Stark wrote:
> On Mon, 28 Feb 2022 at 17:50, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >
> > Chapman Flack <chap@anastigmatix.net> writes:
> > > In the current state of affairs, what's considered the ur-source of that
> > > information?
> >
> > The source code for the type's send/receive functions :-(.  One could
> > wish for something better, but no one has stepped up to produce such
> > documentation.
> 
> Fwiw the client library I heard of attempting to have good binary mode
> support was the Crystal language client
> https://github.com/will/crystal-pg. I think he was aiming for full
> coverage of the built-in data types. That might make a good reference
> implementation to write up documentation from. He probably uncovered
> some corner cases in development that one might not find from just
> inspection of the server code.

Checking in for quick feedback to see if this refactor makes sense.

I've created a function for each data type with the idea that an example
for handling a specific data type can be more easily reviewed by looking
in a single place.

I've added examples for REAL, TIMESTAMP WITHOUT TIME ZONE, and BOOLEAN
to try to illustrate how testlibpq3.sql and testlibpq3.c will grow if
this is a good way to go.

Regards,
Mark

--FyU4WMjP7imLd5SM
Content-Type: text/plain; charset=us-ascii
Content-Disposition: attachment; filename="testlibpq3-v2.diff"

diff --git a/src/test/examples/testlibpq3.c b/src/test/examples/testlibpq3.c
index 4f7b791388..68972f17f3 100644
--- a/src/test/examples/testlibpq3.c
+++ b/src/test/examples/testlibpq3.c
@@ -11,21 +11,56 @@
  * CREATE SCHEMA testlibpq3;
  * SET search_path = testlibpq3;
  * SET standard_conforming_strings = ON;
- * CREATE TABLE test1 (i int4, t text, b bytea);
- * INSERT INTO test1 values (1, 'joe''s place', '\000\001\002\003\004');
- * INSERT INTO test1 values (2, 'ho there', '\004\003\002\001\000');
+ * CREATE SCHEMA testlibpq3;
+ * SET search_path = testlibpq3;
+ * SET standard_conforming_strings = ON;
+ * CREATE TABLE test1 (
+ *     i int4
+ *   , r real
+ *   , bo boolean
+ *   , ts timestamp
+ *   , t text
+ *   , b bytea
+ * );
+ * INSERT INTO test1
+ * VALUES (
+ *     1
+ *   , 3.141593
+ *   , true
+ *   , '2000-01-01 00:00:02.414213'
+ *   , 'joe''s place'
+ *   , '\000\001\002\003\004'
+ * );
+ * INSERT INTO test1
+ * VALUES (
+ *     2
+ *   , 1.618033
+ *   , false
+ *   , '2000-01-01 00:00:01.465571'
+ *   , 'ho there'
+ *   , '\004\003\002\001\000'
+ * );
  *
  * The expected output is:
  *
  * tuple 0: got
  *	i = (4 bytes) 1
+ *	r = (4 bytes) 3.141593
+ *	bo = (1 bytes) 1
  *	t = (11 bytes) 'joe's place'
  *	b = (5 bytes) \000\001\002\003\004
  *
  * tuple 0: got
  *	i = (4 bytes) 2
+ *	r = (4 bytes) 1.618033
+ *	bo = (1 bytes) 0
  *	t = (8 bytes) 'ho there'
  *	b = (5 bytes) \004\003\002\001\000
+ *
+ * General notes about this example:
+ *
+ * Use PQfnumber to avoid assumptions about field order in result but when
+ * getting the field values we ignore possibility they are null!
  */
 
 #ifdef WIN32
@@ -36,6 +71,7 @@
 #include <stdlib.h>
 #include <stdint.h>
 #include <string.h>
+#include <time.h>
 #include <sys/types.h>
 #include "libpq-fe.h"
 
@@ -44,6 +80,13 @@
 #include <arpa/inet.h>
 
 
+/* These macros hopefully make reading calculations for timestamps easier. */
+#define POSTGRES_EPOCH_JDATE 2451545 /* == date2j(2000, 1, 1) */
+#define UNIX_EPOCH_JDATE 2440588 /* == date2j(1970, 1, 1) */
+#define SECS_PER_DAY 86400
+
+uint64_t ntohll(uint64_t);
+
 static void
 exit_nicely(PGconn *conn)
 {
@@ -51,6 +94,142 @@ exit_nicely(PGconn *conn)
 	exit(1);
 }
 
+static void
+handle_boolean(PGresult *res, int i)
+{
+	int			fnum;
+	char	   *ptr;
+	int			val;
+
+	fnum = PQfnumber(res, "bo");
+	ptr = PQgetvalue(res, i, fnum);
+	val = (int) *ptr;
+	printf(" bo = (%d bytes) %d\n", PQgetlength(res, i, fnum), val);
+}
+
+static void
+handle_bytea(PGresult *res, int i)
+{
+	int			j;
+	int			fnum;
+	char	   *ptr;
+	int			len;
+
+	fnum = PQfnumber(res, "b");
+	ptr = PQgetvalue(res, i, fnum);
+
+	/*
+	 * The binary representation of BYTEA is a bunch of bytes, which could
+	 * include embedded nulls so we have to pay attention to field length.
+	 */
+	len = PQgetlength(res, i, fnum);
+	printf(" b = (%d bytes) ", len);
+	for (j = 0; j < len; j++) printf("\\%03o", ptr[j]);
+}
+
+static void
+handle_integer(PGresult *res, int i)
+{
+	int			fnum;
+	char	   *ptr;
+	int			val;
+
+	fnum = PQfnumber(res, "i");
+	ptr = PQgetvalue(res, i, fnum);
+
+	/*
+	 * The binary representation of INT4 is in network byte order, which
+	 * we'd better coerce to the local byte order.
+	 */
+	val = ntohl(*((uint32_t *) ptr));
+
+	printf(" i = (%d bytes) %d\n", PQgetlength(res, i, fnum), val);
+}
+
+static void
+handle_real(PGresult *res, int i)
+{
+	int			fnum;
+	char	   *ptr;
+	union {
+		int		i;
+		float	f;
+	}			val;
+
+	fnum = PQfnumber(res, "r");
+	ptr = PQgetvalue(res, i, fnum);
+
+	/*
+	 * The binary representation of INT4 is in network byte order, which
+	 * we'd better coerce to the local byte order.
+	 */
+	val.i = ntohl(*((uint32_t *) ptr));
+
+	printf(" r = (%d bytes) %f\n", PQgetlength(res, i, fnum), val.f);
+}
+
+static void
+handle_text(PGresult *res, int i)
+{
+	int			fnum;
+	char	   *ptr;
+
+	fnum = PQfnumber(res, "t");
+	ptr = PQgetvalue(res, i, fnum);
+
+	/*
+	 * The binary representation of TEXT is, well, text, and since libpq was
+	 * nice enough to append a zero byte to it, it'll work just fine as a C
+	 * string.
+	 */
+	printf(" t = (%d bytes) '%s'\n", PQgetlength(res, i, fnum), ptr);
+}
+
+static void
+handle_timestamp(PGresult *res, int i)
+{
+	int			fnum;
+	char	   *ptr;
+	uint64_t	val;
+
+	struct tm  *tm;
+	time_t		timep;
+	uint32_t	mantissa;
+
+	fnum = PQfnumber(res, "ts");
+	ptr = PQgetvalue(res, i, fnum);
+	val = ntohll(*((uint64_t *) ptr));
+
+	/*
+	 * The binary representation of a timestamp is in microseconds
+	 * from 2000-01-01.
+	 */
+	timep = val / (uint64_t) 1000000 +
+			(uint64_t) (POSTGRES_EPOCH_JDATE - UNIX_EPOCH_JDATE) *
+			(uint64_t) SECS_PER_DAY;
+	mantissa = val - (uint64_t) (timep -
+			(POSTGRES_EPOCH_JDATE - UNIX_EPOCH_JDATE) * SECS_PER_DAY) *
+			(uint64_t) 1000000;
+
+	/* For ease of testing, assume and print timestamps in GMT. */
+	tm = gmtime(&timep);
+
+	printf(" ts = (%d bytes) %04d-%02d-%02d %02d:%02d:%02d.%06d\n",
+			PQgetlength(res, i, fnum), tm->tm_year + 1900, tm->tm_mon + 1,
+			tm->tm_mday, tm->tm_hour, tm->tm_min, tm->tm_sec, mantissa);
+}
+
+/* This is a uint64_t version of ntohl from arpa/inet.h. */
+uint64_t
+ntohll(uint64_t netlonglong)
+{
+	if (ntohl(1) == 1)
+		return netlonglong;
+	else
+		return (uint64_t) (ntohl((int) ((netlonglong << 32) >> 32))) << 32 |
+				(uint64_t) ntohl(((int) (netlonglong >> 32)));
+}
+
 /*
  * This function prints a query result that is a binary-format fetch from
  * a table defined as in the comment above.  We split it out because the
@@ -59,54 +238,17 @@ exit_nicely(PGconn *conn)
 static void
 show_binary_results(PGresult *res)
 {
-	int			i,
-				j;
-	int			i_fnum,
-				t_fnum,
-				b_fnum;
-
-	/* Use PQfnumber to avoid assumptions about field order in result */
-	i_fnum = PQfnumber(res, "i");
-	t_fnum = PQfnumber(res, "t");
-	b_fnum = PQfnumber(res, "b");
+	int			i;
 
 	for (i = 0; i < PQntuples(res); i++)
 	{
-		char	   *iptr;
-		char	   *tptr;
-		char	   *bptr;
-		int			blen;
-		int			ival;
-
-		/* Get the field values (we ignore possibility they are null!) */
-		iptr = PQgetvalue(res, i, i_fnum);
-		tptr = PQgetvalue(res, i, t_fnum);
-		bptr = PQgetvalue(res, i, b_fnum);
-
-		/*
-		 * The binary representation of INT4 is in network byte order, which
-		 * we'd better coerce to the local byte order.
-		 */
-		ival = ntohl(*((uint32_t *) iptr));
-
-		/*
-		 * The binary representation of TEXT is, well, text, and since libpq
-		 * was nice enough to append a zero byte to it, it'll work just fine
-		 * as a C string.
-		 *
-		 * The binary representation of BYTEA is a bunch of bytes, which could
-		 * include embedded nulls so we have to pay attention to field length.
-		 */
-		blen = PQgetlength(res, i, b_fnum);
-
 		printf("tuple %d: got\n", i);
-		printf(" i = (%d bytes) %d\n",
-			   PQgetlength(res, i, i_fnum), ival);
-		printf(" t = (%d bytes) '%s'\n",
-			   PQgetlength(res, i, t_fnum), tptr);
-		printf(" b = (%d bytes) ", blen);
-		for (j = 0; j < blen; j++)
-			printf("\\%03o", bptr[j]);
+		handle_integer(res, i);
+		handle_real(res, i);
+		handle_boolean(res, i);
+		handle_timestamp(res, i);
+		handle_text(res, i);
+		handle_bytea(res, i);
 		printf("\n\n");
 	}
 }
diff --git a/src/test/examples/testlibpq3.sql b/src/test/examples/testlibpq3.sql
index 35a95ca347..94cb2b97b0 100644
--- a/src/test/examples/testlibpq3.sql
+++ b/src/test/examples/testlibpq3.sql
@@ -1,6 +1,29 @@
 CREATE SCHEMA testlibpq3;
 SET search_path = testlibpq3;
 SET standard_conforming_strings = ON;
-CREATE TABLE test1 (i int4, t text, b bytea);
-INSERT INTO test1 values (1, 'joe''s place', '\000\001\002\003\004');
-INSERT INTO test1 values (2, 'ho there', '\004\003\002\001\000');
+CREATE TABLE test1 (
+    i int4
+  , r real
+  , bo boolean
+  , ts timestamp
+  , t text
+  , b bytea
+);
+INSERT INTO test1
+VALUES (
+    1
+  , 3.141593
+  , true
+  , '2000-01-01 00:00:02.414213'
+  , 'joe''s place'
+  , '\000\001\002\003\004'
+);
+INSERT INTO test1
+VALUES (
+    2
+  , 1.618033
+  , false
+  , '2000-01-01 00:00:01.465571'
+  , 'ho there'
+  , '\004\003\002\001\000'
+);

--FyU4WMjP7imLd5SM--