public inbox for [email protected]
help / color / mirror / Atom feedFrom: Mark Wong <[email protected]>
To: Greg Stark <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: Chapman Flack <[email protected]>
Cc: Ashutosh Bapat <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: real/float example for testlibpq3
Date: Tue, 14 Jun 2022 10:40:06 -0700
Message-ID: <YqjH9qGaIixfZXQo@workstation-mark-wong> (raw)
In-Reply-To: <CAM-w4HM2t7TonV7bz+V27Vvoo0j6hN9-omDo9SHmQ7O58UFYQg@mail.gmail.com>
References: <Yhf70G9OYIKueYZi@workstation-mark-wong>
<CAExHW5tm1FyoLZXdTjdrfRO7ObuoL_p2krc65C5s8D3eWFB50g@mail.gmail.com>
<[email protected]>
<[email protected]>
<[email protected]>
<CAM-w4HM2t7TonV7bz+V27Vvoo0j6hN9-omDo9SHmQ7O58UFYQg@mail.gmail.com>
On Wed, Mar 30, 2022 at 01:16:37PM -0400, Greg Stark wrote:
> On Mon, 28 Feb 2022 at 17:50, Tom Lane <[email protected]> wrote:
> >
> > Chapman Flack <[email protected]> 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
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'
+);
Attachments:
[text/plain] testlibpq3-v2.diff (7.9K, ../YqjH9qGaIixfZXQo@workstation-mark-wong/2-testlibpq3-v2.diff)
download | inline 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'
+);
view thread (19+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: real/float example for testlibpq3
In-Reply-To: <YqjH9qGaIixfZXQo@workstation-mark-wong>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox