Thread: DataRow message for Integer(int4) returns result as text?
I think this makes sense but I wanted to get confirmation:
-Tyler
I created a table with a column having the type int4 (integer). When I insert a row with a number into that column and get it back out I've observed a discrepancy:
The DataRow message has the field encoded as an ASCII ‘7’ with a column length of 1 despite the RowDescription having a column length 4. I assume that this is because it’s a simple query (Q) and therefore the format code for all columns is 0 (for text format).
It makes sense that at the time the RowDescription is written out that it can’t possibly know how many bytes the textual representation of each int will take so it just uses the length of the underlying type.
Is this accurate?
-Tyler
On Wed, Apr 20, 2022 at 4:39 PM Tyler Brock <tyler.brock@gmail.com> wrote:
I think this makes sense but I wanted to get confirmation:I created a table with a column having the type int4 (integer). When I insert a row with a number into that column and get it back out I've observed a discrepancy:The DataRow message has the field encoded as an ASCII ‘7’ with a column length of 1 despite the RowDescription having a column length 4. I assume that this is because it’s a simple query (Q) and therefore the format code for all columns is 0 (for text format).It makes sense that at the time the RowDescription is written out that it can’t possibly know how many bytes the textual representation of each int will take so it just uses the length of the underlying type.Is this accurate?
You probably shouldn't think of DataRow as giving you a "column length" - it is simply giving you the number of bytes you need to read to retrieve all of the bytes for the column and thus position your read pointer at the data length Int32 for the subsequent column (which you do iteratively Int16 column count times).
You now have bytes for columnN - which you need to interpret via RowDescription to transform the raw protocol bytes into a meaningful datum.
You don't care whether the source API was simple or not - RowDescription will tell you what you need to know to interpret the value - it is all self-contained. But yes, because it is a simple query the RowDescription meta-data will inform you that all of the bytes represent (in aggregate ?) the textual representation of the data.
David J.
For sure, I’m thinking of it that way. Thanks for confirming.
What I don’t understand is that if I respond to psql with the RowDescription indicating the format code is 1 for binary (and encode it that way, with 4 bytes, in the DataRow) it doesn’t render the number in the results.
-Tyler
-Tyler
On Apr 20, 2022 at 8:00:18 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Wed, Apr 20, 2022 at 4:39 PM Tyler Brock <tyler.brock@gmail.com> wrote:I think this makes sense but I wanted to get confirmation:I created a table with a column having the type int4 (integer). When I insert a row with a number into that column and get it back out I've observed a discrepancy:The DataRow message has the field encoded as an ASCII ‘7’ with a column length of 1 despite the RowDescription having a column length 4. I assume that this is because it’s a simple query (Q) and therefore the format code for all columns is 0 (for text format).It makes sense that at the time the RowDescription is written out that it can’t possibly know how many bytes the textual representation of each int will take so it just uses the length of the underlying type.Is this accurate?You probably shouldn't think of DataRow as giving you a "column length" - it is simply giving you the number of bytes you need to read to retrieve all of the bytes for the column and thus position your read pointer at the data length Int32 for the subsequent column (which you do iteratively Int16 column count times).You now have bytes for columnN - which you need to interpret via RowDescription to transform the raw protocol bytes into a meaningful datum.You don't care whether the source API was simple or not - RowDescription will tell you what you need to know to interpret the value - it is all self-contained. But yes, because it is a simple query the RowDescription meta-data will inform you that all of the bytes represent (in aggregate ?) the textual representation of the data.David J.
On Wed, Apr 20, 2022 at 5:11 PM Tyler Brock <tyler.brock@gmail.com> wrote:
For sure, I’m thinking of it that way. Thanks for confirming.What I don’t understand is that if I respond to psql with the RowDescription indicating the format code is 1 for binary (and encode it that way, with 4 bytes, in the DataRow) it doesn’t render the number in the results.
Please don't top-post.
psql is a command line program, the server is PostgreSQL or postgres.
I'm not familiar with interacting with the server in C or at the protocol level; I have no idea what that sentence is supposed to mean. But RowDescription seems to be strictly informative so how would you "respond to psql with [it]"?
David J.
I’m not sure what top-posting is?
I’m talking about responding to psql the command line program.
-Tyler
On Apr 20, 2022 at 8:16:28 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Wed, Apr 20, 2022 at 5:11 PM Tyler Brock <tyler.brock@gmail.com> wrote:For sure, I’m thinking of it that way. Thanks for confirming.What I don’t understand is that if I respond to psql with the RowDescription indicating the format code is 1 for binary (and encode it that way, with 4 bytes, in the DataRow) it doesn’t render the number in the results.Please don't top-post.psql is a command line program, the server is PostgreSQL or postgres.I'm not familiar with interacting with the server in C or at the protocol level; I have no idea what that sentence is supposed to mean. But RowDescription seems to be strictly informative so how would you "respond to psql with [it]"?David J.
On Wed, Apr 20, 2022 at 5:21 PM Tyler Brock <tyler.brock@gmail.com> wrote:
I’m not sure what top-posting is?
It's when you place your replies before what you are replying to.
Unlike mine, which is inline-posting, where the reply is after the thing being replied to, trimming unneeded context as appropriate.
I’m talking about responding to psql the command line program.
Ok. I'm outside my league then.
David J.
Tyler Brock <tyler.brock@gmail.com> writes:
> I think this makes sense but I wanted to get confirmation:
> I created a table with a column having the type int4 (integer). When I
> insert a row with a number into that column and get it back out I've
> observed a discrepancy:
> The DataRow message has the field encoded as an ASCII ‘7’ with a column
> length of 1 despite the RowDescription having a column length 4. I assume
> that this is because it’s a simple query (Q) and therefore the format code
> for all columns is 0 (for text format).
If you mean the "data type size" (typlen) field of RowDescription, that
is arguably completely irrelevant; it's there for historical reasons,
I think. The contents of a DataRow field will either be a textual
conversion of the value or the on-the-wire binary representation defined
by the type's typsend routine. In either case, the actual length of
the value as it appears in DataRow is given right there in the DataRow
message. And in either case, the typlen value doesn't necessarily have
anything to do with the length of the DataRow representation. typlen
does happen to match up with the length that'd appear in DataRow for
simple integral types sent in binary format ... but for other cases,
not so much.
regards, tom lane
On 21.04.22 02:11, Tyler Brock wrote: > What I don’t understand is that if I respond to psql with the > RowDescription indicating the format code is 1 for binary (and encode it > that way, with 4 bytes, in the DataRow) it doesn’t render the number in > the results. psql only handles results in text format.