Re: bytea vs. pg_dump

On Wed, 2009-05-06 at 18:33 +0300, Peter Eisentraut wrote:
> On Tuesday 05 May 2009 17:38:33 Tom Lane wrote:
> > "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
> > > Bernd Helmle <mailings(at)oopsware(dot)de> wrote:
> > >> Another approach would be to just dump bytea columns in binary
> > >> format only (not sure how doable that is, though).
> > >
> > > If that's not doable, perhaps a base64 option for bytea COPY?
> >
> > I'm thinking plain old pairs-of-hex-digits might be the best
> > tradeoff if conversion speed is the criterion. The main problem
> > in any case would be to decide how to control the format option.
>
> The output format can be controlled by a GUC parameter. And while we are at
> it, we can also make bytea understand the new output format on input, so we
> can offer an end-to-end alternative to the amazingly confusing current bytea
> format and also make byteain() equally faster at the same time.
>
> For distinguishing various input formats, we could use the backslash to escape
> the format specification without breaking backward compatibilty, e.g.,
>
> '\hexd41d8cd98f00b204e9800998ecf8427e'
>
> With a bit of extra work we can wrap this up to be a more or less SQL-
> conforming blob type, which would also make a lot of people very happy.

And we can also escape the need to uncompress TOAST'ed fields - just
markup the compression as another \c at the beginning of data.

--
Hannu Krosing http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability
Services, Consulting and Training