[PATCH] Add get_bytes() and set_bytes() functions

On Wed, Aug 14, 2024, at 13:01, Aleksander Alekseev wrote:

The proposed patch adds get_bytes() and set_bytes() functions. The
semantics is similar to get_byte() and set_byte() we already have but
the functions operate with bigints rather than bytes and the user can
specify the size of the integer. This allows working with int2s,
int4s, int8s or even int5s if needed.

+1

I wanted this myself many times.

I wonder if get_bytes() and set_bytes() will behave differently
on little-endian vs big-endian systems?

If so, then I think it would be nice to enforce a consistent byte order
(e.g., big-endian), to ensure consistent behavior across platforms.

Regards,
Joel

Hi,

+1

I wanted this myself many times.

I wonder if get_bytes() and set_bytes() will behave differently
on little-endian vs big-endian systems?

If so, then I think it would be nice to enforce a consistent byte order
(e.g., big-endian), to ensure consistent behavior across platforms.

No, the returned value will not depend on the CPU endiness. Current
implementation uses big-endian / network order which in my humble
opinion is what most users would expect.

I believe we also need reverse(bytea) and repeat(bytea, integer)
functions e.g. for those who want little-endian. However I want to
propose them separately when we are done with this patch.

--
Best regards,
Aleksander Alekseev

On Wed, Aug 14, 2024, at 13:31, Aleksander Alekseev wrote:

I wonder if get_bytes() and set_bytes() will behave differently
on little-endian vs big-endian systems?

No, the returned value will not depend on the CPU endiness. Current
implementation uses big-endian / network order which in my humble
opinion is what most users would expect.

Nice.

I've reviewed and tested the patch.
It looks straight-forward to me.
I don't see any potential problems.
I've marked it Ready for Committer.

I believe we also need reverse(bytea) and repeat(bytea, integer)
functions e.g. for those who want little-endian. However I want to
propose them separately when we are done with this patch.

I agree those functions would be nice too.

I also think it would be nice to provide these convenience functions:
to_bytes(bigint) -> bytea
from_bytes(bytea) -> bigint

Since if not having a current bytea value,
and just wanting to convert a bigint to bytea,
then one would need to construct an zeroed bytea
of the proper size first, to then use set_bytes().

And if just wanting to convert the entire bytea to a bigint,
then one would need to pass 0 as offset and the length
of the bytea as size.

Regards,

Joel

On Wed, Aug 14, 2024 at 02:34:06PM +0200, Joel Jacobson wrote:

On Wed, Aug 14, 2024, at 13:31, Aleksander Alekseev wrote:

I wonder if get_bytes() and set_bytes() will behave differently
on little-endian vs big-endian systems?

No, the returned value will not depend on the CPU endiness. Current
implementation uses big-endian / network order which in my humble
opinion is what most users would expect.

Nice.

Indeed!

I've reviewed and tested the patch.
It looks straight-forward to me.
I don't see any potential problems.
I've marked it Ready for Committer.

I believe we also need reverse(bytea) and repeat(bytea, integer)
functions e.g. for those who want little-endian. However I want to
propose them separately when we are done with this patch.

I agree those functions would be nice too.

I also think it would be nice to provide these convenience functions:
to_bytes(bigint) -> bytea
from_bytes(bytea) -> bigint

Along with these, would it make sense to have other forms of these
that won't choke at 63 bits, e.g. NUMERIC or TEXT?

Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778

On Wed, Aug 14, 2024, at 16:43, David Fetter wrote:

I also think it would be nice to provide these convenience functions:
to_bytes(bigint) -> bytea
from_bytes(bytea) -> bigint

Along with these, would it make sense to have other forms of these
that won't choke at 63 bits, e.g. NUMERIC or TEXT?

I wonder what would be good names for such functions though?

Since NUMERIC can have decimal digits, and since BYTEA can already be casted to
TEXT, which will just be \x followed by the hex digits, maybe such names should
include "integer" or some other word, to indicate what is being returned?

It's already quite easy to convert to NUMERIC though,
for users who are aware of tricks like this:
SELECT ('0x'||encode('\xCAFEBABEDEADBEEF'::bytea,'hex'))::numeric;
numeric
----------------------
14627333968688430831
(1 row)

But, I think it would be better to provide functions,
since many users probably have to google+stackoverflow or gpt
to learn such tricks, which are not in the official documentation.

Regards,
Joel

On Wed, Aug 14, 2024 at 05:39:32PM +0200, Joel Jacobson wrote:

On Wed, Aug 14, 2024, at 16:43, David Fetter wrote:

I also think it would be nice to provide these convenience functions:
to_bytes(bigint) -> bytea
from_bytes(bytea) -> bigint

Along with these, would it make sense to have other forms of these
that won't choke at 63 bits, e.g. NUMERIC or TEXT?

I wonder what would be good names for such functions though?

decimal_to_bytes(numeric), decimal_to_bytes(text) on one side
decimal_from_bytes(bytea, typeoid)

Naming Things™ is one of the hard problems in computer science. Bad
joke that includes cache coherency and off-by-one included by
reference.

Since NUMERIC can have decimal digits, and since BYTEA can already be casted to
TEXT, which will just be \x followed by the hex digits, maybe such names should
include "integer" or some other word, to indicate what is being returned?

It's already quite easy to convert to NUMERIC though,
for users who are aware of tricks like this:
SELECT ('0x'||encode('\xCAFEBABEDEADBEEF'::bytea,'hex'))::numeric;
numeric
----------------------
14627333968688430831
(1 row)

But, I think it would be better to provide functions,
since many users probably have to google+stackoverflow or gpt
to learn such tricks, which are not in the official documentation.

As usual, I see "official documentation lacks helpful and/or
non-obvious examples" as a problem best approached by making good the
lack. I am aware that my ideas about pedagogy, documentation, etc. are
not shared universally, but they're widely shared by people whose main
interaction with documents is trying to get help from them.

Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778

On Wed, Aug 14, 2024, at 18:31, David Fetter wrote:

On Wed, Aug 14, 2024 at 05:39:32PM +0200, Joel Jacobson wrote:

On Wed, Aug 14, 2024, at 16:43, David Fetter wrote:

I also think it would be nice to provide these convenience functions:
to_bytes(bigint) -> bytea
from_bytes(bytea) -> bigint

Along with these, would it make sense to have other forms of these
that won't choke at 63 bits, e.g. NUMERIC or TEXT?

I wonder what would be good names for such functions though?

decimal_to_bytes(numeric), decimal_to_bytes(text) on one side
decimal_from_bytes(bytea, typeoid)

I assume decimal_to_bytes() will only accept integer numerics,
that is, that don't have a decimal digits part?
Hmm, it's perhaps then a bit counter intuitive that the name
contains "decimal", since some people might associate the word
"decimal" stronger with "decimal digits" rather than the radix/base 10.

What do we want to happen if passing a numeric with decimal digits,
to decimal_to_bytes()? It must be an error, right?

Example: SELECT decimal_to_bytes(1.23);

Naming Things™ is one of the hard problems in computer science. Bad
joke that includes cache coherency and off-by-one included by
reference.

So true :)

As usual, I see "official documentation lacks helpful and/or
non-obvious examples" as a problem best approached by making good the
lack. I am aware that my ideas about pedagogy, documentation, etc. are
not shared universally, but they're widely shared by people whose main
interaction with documents is trying to get help from them.

Well spoken.

Regards,
Joel

On Wed, Aug 14, 2024, at 19:25, Joel Jacobson wrote:

What do we want to happen if passing a numeric with decimal digits,
to decimal_to_bytes()? It must be an error, right?

Example: SELECT decimal_to_bytes(1.23);

Hmm, an error feels quite ugly on second thought.
Would be nicer if all numerics could be represented,
in a bytea representation that is meaningful to other systems.

I think we need a tuple somehow.

Example:

SELECT numeric_to_bytes(223195403574957);
(\xcafebabedeadbeef,0,false)

SELECT numeric_to_bytes(-223195403574957);
(\xcafebabedeadbeef,0,true)

SELECT numeric_to_bytes(2231954035749.57);
(\xcafebabedeadbeef,2,false)

SELECT numeric_from_bytes('\xcafebabedeadbeef'::bytea,0,false);
223195403574957

SELECT numeric_from_bytes('\xcafebabedeadbeef'::bytea,0,true);
-223195403574957

SELECT numeric_from_bytes('\xcafebabedeadbeef'::bytea,2,false);
2231954035749.57

But then what about Inf,-Inf,NaN?

Regards,
Joel

On Thu, 15 Aug 2024 at 05:20, Joel Jacobson <joel@compiler.org> wrote:

On Wed, Aug 14, 2024, at 19:25, Joel Jacobson wrote:

What do we want to happen if passing a numeric with decimal digits,
to decimal_to_bytes()? It must be an error, right?

Example: SELECT decimal_to_bytes(1.23);

Hmm, an error feels quite ugly on second thought.
Would be nicer if all numerics could be represented,

But then what about Inf,-Inf,NaN?

Perhaps we should also add casts between bytea and the integer/numeric
types. That might be easier to use than functions in some
circumstances.

When casting a numeric to an integer, the result is rounded to the
nearest integer, and NaN/Inf generate errors, so we should probably do
the same here.

Regards,
Dean

Hi,

Perhaps we should also add casts between bytea and the integer/numeric
types. That might be easier to use than functions in some
circumstances.

When casting a numeric to an integer, the result is rounded to the
nearest integer, and NaN/Inf generate errors, so we should probably do
the same here.

Yes, I was also thinking about adding NUMERIC versions of get_bytes()
/ set_bytes(). This would allow converting more than 8 bytes to/from
an integer. I dropped this idea because I thought there would be not
much practical use for it. On the flip side you never know who uses
Postgres and for what purpose.

I will add corresponding casts unless the idea will get a push-back
from the community. IMO the existence of these casts will at least not
make things worse.

--
Best regards,
Aleksander Alekseev

On Thu, 15 Aug 2024 13:58:03 +0300
Aleksander Alekseev <aleksander@timescale.com> wrote:

Hi,

Perhaps we should also add casts between bytea and the integer/numeric
types. That might be easier to use than functions in some
circumstances.

When casting a numeric to an integer, the result is rounded to the
nearest integer, and NaN/Inf generate errors, so we should probably do
the same here.

Yes, I was also thinking about adding NUMERIC versions of get_bytes()
/ set_bytes(). This would allow converting more than 8 bytes to/from
an integer. I dropped this idea because I thought there would be not
much practical use for it. On the flip side you never know who uses
Postgres and for what purpose.

I will add corresponding casts unless the idea will get a push-back
from the community. IMO the existence of these casts will at least not
make things worse.

When we add such casts between bytea and the integer/numeric types,
one of the problems mentioned the first of the thread, that is,
"we don't have a convenient way of casting a bytea to an integer / bigint
and vice versa", would seem be resolved.

On the other hand, I suppose get_bytes() and set_bytes() are still useful
for extracting bytes from byteas, etc. If casting is no longer the main
purpose of these functions, are variations that get_bytes returns bytea
instead of bigint, and set_bytes receives bytea as the newvalue argument
useful? I wonder it would eliminate the restrict that size cannot be larger
than 8.

Here are my very trivial comments on the patch.

+ * this routine treats "bytea" as an array of bytes.

Maybe, the sentence should start with "This ... ".

+ while(size)
+ {

I wonder inserting a space after "while" is the standard style.

Regards,
Yugo Nagata

--
Yugo Nagata <nagata@sraoss.co.jp>

Hi,

When we add such casts between bytea and the integer/numeric types,
one of the problems mentioned the first of the thread, that is,
"we don't have a convenient way of casting a bytea to an integer / bigint
and vice versa", would seem be resolved.

On the other hand, I suppose get_bytes() and set_bytes() are still useful
for extracting bytes from byteas, etc. If casting is no longer the main
purpose of these functions, are variations that get_bytes returns bytea
instead of bigint, and set_bytes receives bytea as the newvalue argument
useful? I wonder it would eliminate the restrict that size cannot be larger
than 8.

No, casting between bytea and numeric will not replace get_bytes() /
set_bytes() for performance reasons.

Consider the case when you want to extract an int4 from a bytea.
get_bytes() is going to be very fast while substr() -> ::numeric ->
::integer chain will require unnecessary copying and conversions.
Casting between bytea and numeric is only useful when one has to deal
with integers larger than 8 bytes. Whether this happens often is a
debatable question.

Here are my very trivial comments on the patch.

+ * this routine treats "bytea" as an array of bytes.

Maybe, the sentence should start with "This ... ".

+ while(size)
+ {

I wonder inserting a space after "while" is the standard style.

Thanks, fixed.

--
Best regards,
Aleksander Alekseev

On 14.08.24 13:01, Aleksander Alekseev wrote:

The proposed patch adds get_bytes() and set_bytes() functions. The
semantics is similar to get_byte() and set_byte() we already have but
the functions operate with bigints rather than bytes and the user can
specify the size of the integer. This allows working with int2s,
int4s, int8s or even int5s if needed.

Examples:

```
SELECT get_bytes('\x1122334455667788'::bytea, 1, 2) = 0x2233;
?column?
----------
t

SELECT set_bytes('\x1122334455667788'::bytea, 1, 2, 0xAABB);
set_bytes
--------------------
\x11aabb4455667788
```

I think these functions do about three things at once, and I don't think
they address the originally requested purpose very well.

Converting between integers and byte arrays of matching size seems like
reasonable functionality. (You can already do one half of that by
calling int2send(), int4send(), and int8send(), but the other direction
(intXrecv()) is not user-callable).

The other things are extracting that byte array from a larger byte array
and sticking it back into a larger byte array; those seem like separate
operations. There is already substr() for bytea for the first part, and
there might be another string-like operationg for the second part, or
maybe we could add one.

On Fri, 16 Aug 2024 11:41:37 +0300
Aleksander Alekseev <aleksander@timescale.com> wrote:

Hi,

When we add such casts between bytea and the integer/numeric types,
one of the problems mentioned the first of the thread, that is,
"we don't have a convenient way of casting a bytea to an integer / bigint
and vice versa", would seem be resolved.

On the other hand, I suppose get_bytes() and set_bytes() are still useful
for extracting bytes from byteas, etc. If casting is no longer the main
purpose of these functions, are variations that get_bytes returns bytea
instead of bigint, and set_bytes receives bytea as the newvalue argument
useful? I wonder it would eliminate the restrict that size cannot be larger
than 8.

No, casting between bytea and numeric will not replace get_bytes() /
set_bytes() for performance reasons.

Consider the case when you want to extract an int4 from a bytea.
get_bytes() is going to be very fast while substr() -> ::numeric ->
::integer chain will require unnecessary copying and conversions.
Casting between bytea and numeric is only useful when one has to deal
with integers larger than 8 bytes. Whether this happens often is a
debatable question.

Thank you for explanation. I understood the performance drawback.

I supposed interfaces similar to lo_get, lo_put, loread, lowrite of
large objects since they might be useful to access or modify a part of
bytea like a binary file read by pg_read_binary_file.

Here are my very trivial comments on the patch.

+ * this routine treats "bytea" as an array of bytes.

Maybe, the sentence should start with "This ... ".

+ while(size)
+ {

I wonder inserting a space after "while" is the standard style.

Thanks, fixed.

Should we fix the comment on byteaGetByte in passing, too?

Regards,
Yugo Nagata

--
Yugo Nagata <nagata@sraoss.co.jp>

Hi Peter,

Thanks for the feedback.

I think these functions do about three things at once, and I don't think
they address the originally requested purpose very well.

The amount of things that the function does is a matter of
interpretation. I can claim that it does one thing ("extracts an
integer from a bytea"), or as many things as there are lines of code.
IMO the actual question is whether this is a good user interface or
not. Since we already have get_byte() / set_byte() the interface is
arguably OK.

Converting between integers and byte arrays of matching size seems like
reasonable functionality. (You can already do one half of that by
calling int2send(), int4send(), and int8send(), but the other direction
(intXrecv()) is not user-callable).

The other things are extracting that byte array from a larger byte array
and sticking it back into a larger byte array; those seem like separate
operations. There is already substr() for bytea for the first part, and
there might be another string-like operationg for the second part, or
maybe we could add one.

If I understand correctly, you propose doing (1):

```
SELECT substr('\x1122334455667788'::bytea, 2, 2) :: int2;
```

... instead of:

```
SELECT get_bytes('\x1122334455667788'::bytea, 1, 2)
```

... and (2):

```
WITH vals AS (
SELECT '\x1122334455667788'::bytea AS x
) SELECT substr(x, 1, 1) || int2send(1234::int2) || substr(x, 4, 5) FROM vals;
```

... instead of:

```
SELECT set_bytes('\x1122334455667788'::bytea, 1, 2, 0xAABB);
```

There is nothing to do for query (2), it already works. It's not much
better than the query from my first email though.

To clarify, supporting bytea<->integer (and/or bytea<->numeric) casts
doesn't strike me as a terrible idea but it doesn't address the issue
I'm proposing to solve.

--
Best regards,
Aleksander Alekseev

On 19.08.24 16:10, Aleksander Alekseev wrote:

To clarify, supporting bytea<->integer (and/or bytea<->numeric) casts
doesn't strike me as a terrible idea but it doesn't address the issue
I'm proposing to solve.

What is the issue you are proposing to solve?

You linked to a couple of threads and stackoverflow pages, and you
concluded from that that we should add get_bytes() and set_bytes()
functions. It's not obvious how you get from the former to the latter,
and I don't think the functions you propose are well-designed in isolation.

Hi,

On 19.08.24 16:10, Aleksander Alekseev wrote:

To clarify, supporting bytea<->integer (and/or bytea<->numeric) casts
doesn't strike me as a terrible idea but it doesn't address the issue
I'm proposing to solve.

What is the issue you are proposing to solve?

You linked to a couple of threads and stackoverflow pages, and you
concluded from that that we should add get_bytes() and set_bytes()
functions. It's not obvious how you get from the former to the latter,
and I don't think the functions you propose are well-designed in isolation.

I guess there are in fact two problems, not one.

1. Converting between bytea and integer types
2. Multibyte versions of get_byte() / set_byte()

As you rightly pointed out, for (1) we just need to add missing casts.
Here is the corresponding patch, v3-0001. Note that I couldn't re-use
int{2,4,8}recv because its first argument is StringInfo, so I ended up
implementing my own bytea->int{2,4,8} functions.

I think there may be value in (2) as well. It's implemented in v3-0002
and I did my best to clarify the commit message. On the flip side the
situation when one wants something like extracting int4 from a
bytea(or vice versa) and is not happy with convenience and/or
performance of substr()+casts is arguably rare. I'll be fine with
whatever consensus the community reaches about this patch.

--
Best regards,
Aleksander Alekseev

Hi,

I guess there are in fact two problems, not one.

1. Converting between bytea and integer types
2. Multibyte versions of get_byte() / set_byte()

As you rightly pointed out, for (1) we just need to add missing casts.
Here is the corresponding patch, v3-0001. Note that I couldn't re-use
int{2,4,8}recv because its first argument is StringInfo, so I ended up
implementing my own bytea->int{2,4,8} functions.

I think there may be value in (2) as well. It's implemented in v3-0002
and I did my best to clarify the commit message. On the flip side the
situation when one wants something like extracting int4 from a
bytea(or vice versa) and is not happy with convenience and/or
performance of substr()+casts is arguably rare. I'll be fine with
whatever consensus the community reaches about this patch.

PFA the rebased patch set v4.

--
Best regards,
Aleksander Alekseev

Hi,

I guess there are in fact two problems, not one.

1. Converting between bytea and integer types
2. Multibyte versions of get_byte() / set_byte()

As you rightly pointed out, for (1) we just need to add missing casts.
Here is the corresponding patch, v3-0001. Note that I couldn't re-use
int{2,4,8}recv because its first argument is StringInfo, so I ended up
implementing my own bytea->int{2,4,8} functions.

I think there may be value in (2) as well. It's implemented in v3-0002
and I did my best to clarify the commit message. On the flip side the
situation when one wants something like extracting int4 from a
bytea(or vice versa) and is not happy with convenience and/or
performance of substr()+casts is arguably rare. I'll be fine with
whatever consensus the community reaches about this patch.

PFA the rebased patch set v4.

Rebased, v5.

--
Best regards,
Aleksander Alekseev