Re: SUBSTRING performance for large BYTEA

On Sat, Aug 18, 2007 at 10:23:42AM -0700, Joshua D. Drake wrote:

> >> SET STORAGE EXTERNAL (before storing anything in it...) See the
> >> ALTER TABLE reference page.

> > Now, to convert an existing bytea column I would need to add
> > a new bytea column with "set storage external", move the
> > data from the old column to the new column, remove the old
> > column, and give the new column the original name, correct ?
>
> Set existing column to storage external
> update existing column with existing data:
>
> UPDATE foo SET bar = bar;
>
> Now the down side to this is you are going to create a dead row for
> every update which means a vacuum (probably full) afterward, but the way
> you describe above will do the same thing as well.
Sure.

I was a bit uneasy about the docs saying

"set storage doesn't affect existing data but only sets the
strategy on new inserts/updates"

and hence thought using a wholy new column would somehow be
safer. But maybe this can be nefariously interpreted such
that I could sort-of implement cutoff-based
extended/external switching by prepending "alter table ...
set storage external/extended ..." to INSERTs/UPDATEs based
on bytea parameter size. Or even writing a trigger issuing
ALTER TABLE depending on size of insert ?

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346