Re: PQfformat question and retrieving bytea data in C

I have a question regarding the return value of PQfformat()

I have a 'data' column in my table, type bytea (postgresql 9.1.5).

In postgresql.conf:
bytea_output = 'escape'

When I execute the query:
PGresult *res = PQexec(db, "SELECT data::bytea FROM data_table WHERE id='xxx'")

And I run through the results:

int i, j;
for (i = 0; i < PQntuples(res); i++) {
for (j = 0; j < PQnfields(res); j++) {
printf("Format %d: %d\n", j, PQfformat(res, j));
printf("Type %d: %d\n", j, PQftype(res, j));
}
}

This prints that the format is type 0, and the type is 17.

Shouldn't the format be 1 (binary data)?

I am getting a discrepancy between data that I put into the table and
data I retrieve.
When I dump the data, using:

int di;
char *val = PQgetvalue(res, i, j);
for (di = 0; di < 16; di++) fprintf(stderr, "%2x ", val[di]);

I see the following:
30 5c 33 33 32 5c 30 30 30 5c 30 31 31 5c 30 30

But when I look at the same data in the database:

psql> select encode(substr(data, 0, 16), 'hex') from data_table where id='xxx';
encode
--------------------------------
30da00090132420520203137323030

This is the data I'm expecting to get back. Is the '00' (third byte)
causing the problem?

The data looks the same at a certain place (ie it starts with the same
byte 30, then the C code has 22 bytes whereas the db hex dump has 7
bytes, then the data is the same again. The 7/22 number of bytes isn't
always the same, across the different data values).

--
Jason Armstrong

Hey Jason,

2012/8/29 Jason Armstrong <ja(at)riverdrums(dot)com>

> I have a question regarding the return value of PQfformat()
>
> I have a 'data' column in my table, type bytea (postgresql 9.1.5).
>
> In postgresql.conf:
> bytea_output = 'escape'
>
> When I execute the query:
> PGresult *res = PQexec(db, "SELECT data::bytea FROM data_table WHERE
> id='xxx'")
>
PQexec() always returns data in the text format. You should use
PQexecParams() to obtain the data as binary.

>
> And I run through the results:
>
> int i, j;
> for (i = 0; i < PQntuples(res); i++) {
> for (j = 0; j < PQnfields(res); j++) {
> printf("Format %d: %d\n", j, PQfformat(res, j));
> printf("Type %d: %d\n", j, PQftype(res, j));
> }
> }
>
> This prints that the format is type 0, and the type is 17.
>
> Shouldn't the format be 1 (binary data)?
>
> I am getting a discrepancy between data that I put into the table and
> data I retrieve.
> When I dump the data, using:
>
> int di;
> char *val = PQgetvalue(res, i, j);
> for (di = 0; di < 16; di++) fprintf(stderr, "%2x ", val[di]);
>
> I see the following:
> 30 5c 33 33 32 5c 30 30 30 5c 30 31 31 5c 30 30
>
> But when I look at the same data in the database:
>
> psql> select encode(substr(data, 0, 16), 'hex') from data_table where
> id='xxx';
> encode
> --------------------------------
> 30da00090132420520203137323030
>
> This is the data I'm expecting to get back. Is the '00' (third byte)
> causing the problem?
>
> The data looks the same at a certain place (ie it starts with the same
> byte 30, then the C code has 22 bytes whereas the db hex dump has 7
> bytes, then the data is the same again. The 7/22 number of bytes isn't
> always the same, across the different data values).
>
> --
> Jason Armstrong
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
// Dmitriy.

On Wed, Aug 29, 2012 at 8:05 AM, Dmitriy Igrishin <dmitigr(at)gmail(dot)com> wrote:
> Hey Jason,
>
> 2012/8/29 Jason Armstrong <ja(at)riverdrums(dot)com>
>>
>> I have a question regarding the return value of PQfformat()
>>
>> I have a 'data' column in my table, type bytea (postgresql 9.1.5).
>>
>> In postgresql.conf:
>> bytea_output = 'escape'
>>
>> When I execute the query:
>> PGresult *res = PQexec(db, "SELECT data::bytea FROM data_table WHERE
>> id='xxx'")
>
> PQexec() always returns data in the text format. You should use
> PQexecParams() to obtain the data as binary.

Also see libpqtypes. It abstracts you from the wire format and
returns data in a regular way:

int success;
PGint4 i4;
PGtext text;
PGbytea bytea;
PGpoint pt;
PGresult *res = PQexec(conn, "SELECT i,t,b,p FROM tbl");

/* Get some field values from the result (order doesn't matter) */
success = PQgetf(res,
0, /* get field values from tuple 0 */
"%int4 #text %bytea %point",
/* type format specifiers (get text by name '#') */
0, &i4, /* get an int4 from field num 0 */
"t", &text, /* get a text from field name "t" */
2, &bytea, /* get a bytea from field num 2 */
3, &pt); /* get a point from field num 3 */

/* Output an error message using PQgeterror(3). */
if(!success)
fprintf(stderr, "*ERROR: %s\n", PQgeterror());

/* Output the values, do this before PQclear() */
else
printf("int4=%d, text=%s, bytea=%d bytes, point=(%f,%f)\n",
i4, text, bytea.len, pt.x, pt.y);

PQclear(res);

merlin

On Wed, Aug 29, 2012 at 10:30 PM, Jason Armstrong <ja(at)riverdrums(dot)com> wrote:
> I see the following:
> 30 5c 33 33 32 5c 30 30 30 5c 30 31 31 5c 30 30
>
> But when I look at the same data in the database:
>
> psql> select encode(substr(data, 0, 16), 'hex') from data_table where id='xxx';
> encode
> --------------------------------
> 30da00090132420520203137323030

Here's what you're seeing:

0\332\000\011\00

5c is a backslash, the rest are all digits. The backslashes introduce
octal escape codes - that's what bytea_output = 'escape' means. 0332
is 0xda, 011 is 0x09, etc. You're seeing the same values come up in
the cases where they don't need to be escaped, like the 0x30 at the
beginning.

ChrisA

Thank-you for the thoughtful answers.

I have updated my C library to return the binary data correctly. I
note the restriction on not being able to retrieve different columns
in different formats.

I found that my perl DBI interface wasn't happy either with the
'escape' output format, so I changed bytea_output to 'hex', and
DBD::Pg is also happy now (I also read that the hex format is more
efficient).

--
Jason Armstrong

Jason Armstrong wrote:
> I have updated my C library to return the binary data correctly. I
> note the restriction on not being able to retrieve different columns
> in different formats.

Actually, PostgreSQL supports that if you use the line protocol
to talk to the server (see the description of "Bind (F)" in
http://www.postgresql.org/docs/9.1/static/protocol-message-formats.html)
.

Alas, this is not supported by the C API.
Maybe that would be a useful extension to libpq.

Yours,
Laurenz Albe

2012/8/30 Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>

> Jason Armstrong wrote:
> > I have updated my C library to return the binary data correctly. I
> > note the restriction on not being able to retrieve different columns
> > in different formats.
>
> Actually, PostgreSQL supports that if you use the line protocol
> to talk to the server (see the description of "Bind (F)" in
> http://www.postgresql.org/docs/9.1/static/protocol-message-formats.html)
> .
>
> Alas, this is not supported by the C API.
> Maybe that would be a useful extension to libpq.
>
+1

--
// Dmitriy.