Binary Input/Output Functions

[David E. Wheeler]

Hello PGXNers,

I recently got a request to add binary in/out parameters to the semver data type:

https://github.com/theory/pg-semver/issues/59

Seems like it’d probably be pretty straightforward, but I’m not familiar with binary in/out functions. Could anyone point me at an example/precedent? I mean semver_in and semver_out return cstrings:

https://github.com/theory/pg-semver/blob/f480d475a655ec9fe88e22ac66e23fc263769e4a/src/semver.c#L321-L340

Might it be as simple as passing them to the RECEIVE and SEND params to CREATE TYPE? And how does one test them, anyway?

Thanks,

David

[Daniele Varrazzo]

Hi David,

On Wed, 20 Oct 2021 at 02:11, David E. Wheeler <da...@justatheory.com> wrote:
>
> Hello PGXNers,
>
> I recently got a request to add binary in/out parameters to the semver data type:
>
> https://github.com/theory/pg-semver/issues/59

That's a pretty opinionated driver :D Many extension libraries don't
have a binary format (ltree, for instance).

> Seems like it’d probably be pretty straightforward, but I’m not familiar with binary in/out functions. Could anyone point me at an example/precedent? I mean semver_in and semver_out return cstrings:
>
> https://github.com/theory/pg-semver/blob/f480d475a655ec9fe88e22ac66e23fc263769e4a/src/semver.c#L321-L340
>
> Might it be as simple as passing them to the RECEIVE and SEND params to CREATE TYPE? And how does one test them, anyway?

Usually in send/receive you can take the binary format of your struct
and convert it in some hardware-independent way. Your datum seems to
be:

typedef struct semver {
int32 vl_len_; /* varlena header */
vernum numbers[3];
char prerel[]; /* pre-release, including the null byte for
convenience */
} semver;

so you might send out and receive a buffer with the three hton'd
numbers and the string as length/data.

I think the best source of examples is the postgres source itself, as
pretty much all data types have a binary format. They should be all in
the `src/backend/utils/adt/` directory. For instance this is int2
https://github.com/postgres/postgres/blob/41f30ecc29c89285d3eecd435906c4e9cb048be4/src/backend/utils/adt/int.c#L86-L106

Things you might want to consider is whether to version the binary format.

As for testing, I am not aware of a way to do so from sql, as you
can't access the "internal" data type in SQL. Maybe you can cheat and
CREATE FUNCTION a test function with the same C function but different
types to make them available to SQL. Another way would be to test
using Psycopg 3, which can use binary parameters and results
(https://www.psycopg.org/psycopg3/docs/basic/params.html#binary-data).

Hope this helps

-- Daniele

[David E. Wheeler]

On Oct 20, 2021, at 07:02, Daniele Varrazzo <daniele....@gmail.com> wrote:

> Hi David,

HI Daniele. This is super helpful, thank you.

> That's a pretty opinionated driver :D Many extension libraries don't
> have a binary format (ltree, for instance).

Will likely tell them that pull requests are welcome.

> Usually in send/receive you can take the binary format of your struct
> and convert it in some hardware-independent way. Your datum seems to
> be:
>
> typedef struct semver {
> int32 vl_len_; /* varlena header */
> vernum numbers[3];
> char prerel[]; /* pre-release, including the null byte for
> convenience */
> } semver;
>
> so you might send out and receive a buffer with the three hton'd
> numbers and the string as length/data.
>
> I think the best source of examples is the postgres source itself, as
> pretty much all data types have a binary format. They should be all in
> the `src/backend/utils/adt/` directory. For instance this is int2
> https://github.com/postgres/postgres/blob/41f30ecc29c89285d3eecd435906c4e9cb048be4/src/backend/utils/adt/int.c#L86-L106

Thanks, just what I needed to know.

> Things you might want to consider is whether to version the binary format.
>
> As for testing, I am not aware of a way to do so from sql, as you
> can't access the "internal" data type in SQL. Maybe you can cheat and
> CREATE FUNCTION a test function with the same C function but different
> types to make them available to SQL. Another way would be to test
> using Psycopg 3, which can use binary parameters and results
> (https://www.psycopg.org/psycopg3/docs/basic/params.html#binary-data).

Yeah, might be able to CREATE FUNCTIONs that outputs hex or something.

Best,

David