Date: Fri, 1 Jul 2022 10:18:20 -0700
From: Mark Wong <markwkm@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Robert Haas <robertmhaas@gmail.com>, Greg Stark <stark@mit.edu>,
	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: <Yr8sXL9J/nel7BEM@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>
 <YqjH9qGaIixfZXQo@workstation-mark-wong>
 <CA+TgmobLR929aHtOfC=JKqz+qzDxdYo_dssj06TyOpnU+HbT6A@mail.gmail.com>
 <967228.1655408510@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="r+VDod33A49rXvwb"
Content-Disposition: inline
In-Reply-To: <967228.1655408510@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/Yr8sXL9J/nel7BEM%40workstation-mark-wong>
Precedence: bulk


--r+VDod33A49rXvwb
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

On Thu, Jun 16, 2022 at 03:41:50PM -0400, Tom Lane wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
> > On Tue, Jun 14, 2022 at 1:40 PM Mark Wong <markwkm@gmail.com> wrote:
> >> 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.
> 
> > I'm not sure that we want to let these test programs grow very large.
> > In particular, I don't think we should let ourselves get sucked into
> > adding an example for every data type under the sun -- if that's
> > wanted, the solution is perhaps to add documentation for the binary
> > formats, not hide impromptu documentation inside a test program.  But
> > doing this much seems OK to me.
> 
> Yeah, "hiding impromptu documentation inside a test program" is what
> this looks like, and I'm not sure that's a reasonable way to go.
> 
> (1) Who's going to think to look in src/test/examples/testlibpq3.c for
> documentation of binary formats?
> 
> (2) The useful details are likely to get buried in notational and
> portability concerns, as I think your build failure illustrates.
> 
> (3) I bet few if any packagers install these files, so that the new
> info would be unavailable to many people.
> 
> I think some new appendix in the main SGML docs would be the appropriate
> place if we want to provide real documentation.

Just wanted to do another quick check in before I go too far.  How do
does this start look?  Extending the libpq section with a code snippet
and description per data type?

Regards,
Mark

--r+VDod33A49rXvwb
Content-Type: text/plain; charset=us-ascii
Content-Disposition: attachment; filename="libpq-binary-format-doc-v0.patch"

diff --git a/doc/src/sgml/libpq.sgml b/doc/src/sgml/libpq.sgml
index 37ec3cb4e5..44c60223ee 100644
--- a/doc/src/sgml/libpq.sgml
+++ b/doc/src/sgml/libpq.sgml
@@ -8874,6 +8874,109 @@ int PQisthreadsafe();
   </para>
  </sect1>
 
+ <sect1 id="libpq-binary-format">
+  <title>Binary Format</title>
+  <para>
+    Various <application>libpq</application> functions support sending or
+    receiving data in binary format.  Data in binary format requires the
+    application to be handle any differences between the system and the network
+    byte order.
+  </para>
+
+  <para>
+    <xref linkend="libpq-example-3"/> demonstrates how to write a complete
+    program that uses <application>libpq</application> functions that request
+    data in binary foramt.  This example shows how to handle only a few data
+    types.  This section will detail the handling of as many of the data types
+    as possible.  See <xref linkend="datatype"/> for descriptions of each of the
+    built-in data types.
+  </para>
+
+  <sect2 id="libpq-binary-boolean">
+   <title><type>boolean</type></title>
+    <para>
+       A <type>boolean</type> is transmitted as single byte that, when cast to
+       an <literal>int</literal>, will be <literal>0</literal> for
+       <literal>false</literal> and <literal>1</literal> for
+       <literal>true</literal>.
+    </para>
+<programlisting>
+<![CDATA[
+int         value;
+
+ptr = PQgetvalue(res, row_number, column_number);
+value = (int) *ptr;
+printf("%d\n", value);
+]]>
+</programlisting>
+  </sect2>
+
+  <sect2 id="libpq-binary-real">
+   <title><type>real</type></title>
+    <para>
+      A <type>real</type> is composed of 4 bytes and needs to be handled
+      correctly for byte order.
+    </para>
+<programlisting>
+<![CDATA[
+union {
+    int     i;
+    float   f;
+}           value;
+
+ptr = PQgetvalue(res, row_number, column_number);
+val.i = ntohl(*((uint32_t *) ptr));
+printf("%f\n", value.f);
+]]>
+</programlisting>
+  </sect2>
+
+  <sect2 id="libpq-binary-timestamp-without-time-zone">
+   <title><type>timestamp without time zone</type></title>
+    <para>
+      A <type>timestamp without time zone</type> is a 64-bit data type
+      representing the number of microseconds since January 1, 2000.  It can be
+      converted into a broken-down time representation by converting the time
+      into seconds and saving the microseconds elsewhere.
+    </para>
+    <para>
+      Note that in C time is counted from January 1, 1970 so this discrepency
+      needs to be accounted for in addition to handling the network byte order.
+    </para>
+<programlisting>
+<![CDATA[
+#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    value;
+
+struct tm  *tm;
+time_t      timep;
+uint32_t    mantissa;
+
+ptr = PQgetvalue(res, column_number, row_number);
+/* Note ntohll() is not implemented on all platforms. */
+val = ntohll(*((uint64_t *) ptr));
+
+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;
+
+/* Assume and print timestamps in GMT for simplicity. */
+tm = gmtime(&timep);
+
+printf("%04d-%02d-%02d %02d:%02d:%02d.%06d\n",
+        tm->tm_year + 1900, tm->tm_mon + 1, tm->tm_mday, tm->tm_hour,
+        tm->tm_min, tm->tm_sec, mantissa);
+]]>
+</programlisting>
+  </sect2>
+
+ </sect1>
 
  <sect1 id="libpq-build">
   <title>Building <application>libpq</application> Programs</title>

--r+VDod33A49rXvwb--