BUG #4866: ECPG and BYTEA

The following bug has been logged online:

Bug reference: 4866
Logged by: Rick Levine
Email address: Richard_D_Levine(at)raytheon(dot)com
PostgreSQL version: 8.3.7
Operating system: Windows Vista
Description: ECPG and BYTEA
Details:

ECPG does not handle BYTEA columns properly. When I encode a unsigned char
array using PQescapeByteaConn and send it to the server, it is not stored as
the original bytes, but rather is stored as the escaped string (much
larger).

//This doesn't work. Stored encoded.

bytea_var =
PQescapeByteaConn(connection, bytea_hostvar,
bytea_len, &new_len);
EXEC SQL AT :connection INSERT INTO Btable
(index, bytea_col)
VALUES
(:index_var, :bytea_var);

//This doesn't work. Stored encoded.

bytea_var =
PQescapeByteaConn(connection, bytea_hostvar,
bytea_len, new_len);
EXEC SQL AT :connection INSERT INTO Btable
(index, bytea_col)
VALUES
(:index_var, decode(:bytea_var, 'escape'));

//This doesn't work. Error.
bytea_var =
PQescapeByteaConn(connection, bytea_hostvar,
bytea_len, new_len);
EXEC SQL AT :connection INSERT INTO Btable
(index, bytea_col)
VALUES
(:index_var, :bytea_var::BYTEA);

Furthermore, when I fetch the BYTEA column value back, I have to decode it
twice (using PQunescapeBytea) to get back the original array of bytes.

I see three problems. I have to use functions from the C interface, not
documented as part of ECPG, to get this to work at all; my storage size is
quadrupled on disk; and the data communicated between the client and server
is even bigger than that.

The problem, as I see it, is that there's no way for the ECPG parser to
unequivocally determine the size of the byte array pointed to by the host
variable. Sure, if it's declared as

EXEC SQL BEGIN DECLARE SECTION;
unsigned char bytea_hostvar[1024];
EXEC SQL END DECLARE SECTION;

you can figure it out, but otherwise not. That is how I declared it BTW.
This causes the need to create a null terminated string to send to the
server, rather than just sending the original bytes.

We know the coder knows the size of the buffer, but ECPG doesn't, so the
best solution (to my mind) would be to allow the coder to tell ECPG the
buffer size directly. A clean way to do this would be to allow an indicator
variable containing the size, e.g.

EXEC SQL BEGIN DECLARE SECTION;
unsigned char bytea_hostvar[1024];
int hostvar_ind = 1024;
EXEC SQL END DECLARE SECTION;

EXEC SQL AT :connection INSERT INTO Btable
(index, bytea_col)
VALUES
(:index_var, :bytea_hostvar:hostvar_ind);

I'm just sayin... ;)