Ticket #86 (assigned defect)

Opened 2 years ago

Last modified 2 years ago

fieldstream outputs escaped bytea data

Reported by: tometzky@batory.org.pl Assigned to: jtv (accepted)
Priority: normal Milestone:
Component: other Version: 2.6
Severity: normal Keywords:
Cc:

Description (Last modified by jtv)

I was looking for a way to retrieve bytea data from a result without copying it. I tried to use fieldstream using libpqxx-2.6.8 but I got escaped data.

I don't know it it's a bug (I hope so). If it is not, how do I retrieve bytea data without using much more than bytea data size of memory?

This is roughly code I used: 

result R;
...
pqxx::fieldstream file_data(R.at(0)["file_data"]);
#define CACHESIZE (0x7fff) /* minimum POSIX limit for read cache size */
char data_cache[CACHESIZE];
ofstream out;
out.exceptions(ofstream::failbit | ofstream::badbit);
out.open("filename", ios::binary);
while ( file_data.good() && out.good() ) {
    file_data.read(data_cache, CACHESIZE);
    out.write(data_cache, file_data.gcount());
}
out.close();

Attachments

Change History

10/15/06 04:07:50 changed by jtv

  • description changed.

10/15/06 04:37:29 changed by jtv

  • status changed from new to assigned.

To get the original binary data back, you'll need to un-escape it using binarystring. Yes, come to think of it that is a bit odd. I'm not sure I should change that now--especially since, as you point out, the un-escaping process involves allocating more memory. Also to be considered in this case is whether the output stream supports binary data in the first place.

The allocation of extra memory is, I'm afraid, unavoidable when un-escaping data. That's just how the underlying libpq API does it. We could have tried to hide it in libpqxx, but that might actually make things worse instead of better.

If you're dealing with so much binary data that the extra allocation becomes a problem, you may be better of using a cursor (to reduce the number of rows per result set) and/or large objects (to access large binary fields in a more file-like manner).

10/15/06 09:11:27 changed by anonymous

1. A cursor does not help - I'll be retrieving one row at a time anyway. 2. Large objects also do not help - I need access control and I would like to have data integrity warranty in schema, which is not possible with lo.

libpq do have a way of retrieving binary data without escaping - using parameter resultFormat=1 of PQexecParams and PQexecPrepared functions. But libpqxx does not support this parameter - it is always 0.

libpqxx also does not allow to use libpq functions when needed - there's no public 

PGconn* get_pgconn()

method of pqxx::connection_base. I think it should be.

I think I'll set a bytea column to storage external. It will make possible to get data in chunks using 

substring(string from [int] for [int])"

I tried to insert 100MB file (not very big for today's standards) to bytea using escaped buffer. Both client and server were on the same machine with 512MB RAM and 1GB swap. It failed - both client and server ended out of memory (client first, then server). I think retrieving would not be much better.

10/23/06 12:06:32 changed by jtv

This really shouldn't crash the client, let alone the server.

There are requests for binary result sets from time to time, but this is perhaps the first compelling argument I've seen. Unfortunately that's a bigger job than I have time for at the moment. :-(

Add/Change #86 (fieldstream outputs escaped bytea data)




Change Properties
Action