-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Karsten Hilbert wrote:
> On Sat, Aug 18, 2007 at 12:20:42PM -0400, Tom Lane wrote:
>
>> "Vance Maverick" <vmaverick@xxxxxxx> writes:
>>> My question is about performance in the postgres server. When I execute
>>> "SELECT SUBSTRING (my_bytea FROM ? FOR ?) FROM my_table WHERE id =3D ?",
>>> does it fetch the whole BYTEA into memory? Or does it access only the
>>> pages that contain the requested substring?
>> Recent releases will do what you want if the column has been marked
>> SET STORAGE EXTERNAL (before storing anything in it...) See the
>> ALTER TABLE reference page.
> Ah, thanks, good to know !
>
> "Recent releases" seems to mean at least as far back as 8.1
> going by the docs.
>
> 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 ?
>
> Or is the an easier way ?
Well this is a guess, but:
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.
Sincerely,
Joshua D. Drake
>
> Karsten
- --
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997 http://www.commandprompt.com/
UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQFGxyseATb/zqfZUUQRAn7AAJ9jzhsOb8xoy9QWoI6yfNV4cO9Z3gCeJG6W
n3Z0uaYp5d6QGoFP3O8QJUI=
=fIqx
-----END PGP SIGNATURE-----
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
