Re: Loading binary data into the database

Tom Lane wrote:

> Adriaan Joubert <a.joubert@albourne.com> writes:
> > since 7.0.2 binary copy has been disabled, making
> > quite a few libpq functions superfluous
>
> Such as?  IIRC, the reason we disabled it was precisely that there was
> no support on the client side.  (What's worse, there's no support in
> the FE/BE protocol either.  I don't see how you could have made this
> work...)

I issued a 'copy binary <table> from stdin;' and then sent the data with
PQputnbytes (this is now obsolete, isn't it?) and as this was from a
CORBA server running on the same machine as the database this worked fine
and was very fast (not being able to update a serial was a pain, and I
ended up doing it by hand in the server). As some of the data I have to
write goes into bytea fields, i now have to convert all non-printable
characters to octet codes, which is a total pain in the neck.

> Cross-machine binary copy is a dangerous thing anyway, since it opens
> you up to all sorts of compatibility problems.  If your app is running
> on the same machine as the server, you can write data to a file and
> then send a command to do a binary copy from that file.

Yes sure, if you write from a machine with a different architecture it is
going to cause trouble. Reading and writing binary files on the host
machine seems kind-of a slow solution to the problem and leads to yet
another load of permission problems (Ok, they can be resolved, but it is
yet another place where things can go wrong).

Perhaps libpq is not the answer. I've even been thinking about writing a
SPI function that acts as a CORBA server -- but decided that that is just
too ugly to contemplate. So what is the solution?

Adriaan