node-postgres icon indicating copy to clipboard operation
node-postgres copied to clipboard

pg 8.16.2 in binary mode produces incorrect row values

Open faulpeltz opened this issue 6 months ago • 0 comments

I have recently have been experimenting with pg's binary mode for performance reasons and tried the latest change, and I found that it delivers incorrect results sometimes for various non-string fields. For example, a "int32" column with value "1000" is incorrectly returned as 1007.

This is caused by the parser converting the buffer slice into an utf8 string: https://github.com/brianc/node-postgres/blob/1a25d128177dd7c54dc4652bbb92ac7986306e3f/packages/pg-protocol/src/parser.ts#L287

and then the result converts it back into a buffer in binary mode https://github.com/brianc/node-postgres/blob/1a25d128177dd7c54dc4652bbb92ac7986306e3f/packages/pg/lib/result.js#L69-L70

This is incorrect, however, because roundtrips with random binary data from and to utf8, does not work all the time - for example the int32 value "1000" (0x3e8)

> Buffer.from(Buffer.from([0,0,0x03,0xe8]).toString())
<Buffer 00 00 03 ef bf bd>
> readInt32BE()
< 1007

I was able to fix this by performing the conversion the other way around, but this does not seem great performance-wise.

faulpeltz avatar Jun 20 '25 14:06 faulpeltz