random access - bytea
There have been (in the past at least) plans on adding a random access
interface to bytea fields. I don't find it in the todo, maybe it should be
added?
What kind of syntax have people thought about, to let the client read a
bytea field in a random access way? I'm thinking of something like:
BEGIN;
SELECT id, open_bytea(bytea_field, READ_ACCESS) FROM table;
... and then the client has to access the data in the same transaction,
using the ID returned by open_bytea() ...
COMMIT;
Would that be feasible? I think it looks like a clean solution. To me
it seems that accessing the data outside a transaction would not be
something we want anyway, right?
Sematically it's easier to implement read access then write access. For
write access to work with transactions one need for example the blocks in
a bytea field to be stored with the transaction id's an stuff (I don't
know these parts of pg internals well enough yet).
One other thing I don't know well enough yet is the new wire protocol. To
support something like above, would it need to be changed? I guess so if
it wasn't designed with this in mind.
--
/Dennis
Dennis Bjorklund wrote:
There have been (in the past at least) plans on adding a random access
interface to bytea fields. I don't find it in the todo, maybe it should be
added?
First do this:
ALTER TABLE foo ALTER COLUMN foo_bytea SET STORAGE EXTERNAL;
see:
http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=sql-altertable.html
Then do:
UPDATE foo SET foo_bytea = foo_bytea || '';
That will modify the bytea column so that it is stored uncompressed in
the TOAST table.
Now, simply use substr() to grab any random chunk:
SELECT substr(foo_bytea, 20000, 100) from foo where foo_id = 42;
When TOASTed columns are stored uncompressed, substring will grab just
the needed chunks from the TOAST table directly. In contrast, when the
field is stored compressed, it grabs the entire thing, uncompresses it,
then gets the piece you asked for.
HTH,
Joe
On Sat, 25 Oct 2003, Joe Conway wrote:
That will modify the bytea column so that it is stored uncompressed in
the TOAST table.Now, simply use substr() to grab any random chunk:
SELECT substr(foo_bytea, 20000, 100) from foo where foo_id = 42;
This would imply that every little read would have to do a scan on a table
to find the row and then to perform the substr. An open command can
be optimized a lot more, for example to cache entries that have been
opened so that it's fast to read the next 1kb or whatever you want.
Also, the above does not solve writes at all which can also be made
transaction safe and fast with a better api where you can update a part
of a field.
The above is not really a replacement of the current lo-objects.
--
/Dennis
This would imply that every little read would have to do a scan on a table
to find the row and then to perform the substr. An open command can
be optimized a lot more, for example to cache entries that have been
opened so that it's fast to read the next 1kb or whatever you want.
It's an index scan, so it's pretty fast...
Chris
On Sun, 26 Oct 2003, Christopher Kings-Lynne wrote:
to find the row and then to perform the substr. An open command can
be optimized a lot more, for example to cache entries that have been
opened so that it's fast to read the next 1kb or whatever you want.It's an index scan, so it's pretty fast...
And if you read a lot it will be cached, but it's still a little extra
overhead and the write case it does not solve at all.
--
/Dennis
Dennis Bjorklund kirjutas P, 26.10.2003 kell 07:30:
On Sat, 25 Oct 2003, Joe Conway wrote:
That will modify the bytea column so that it is stored uncompressed in
the TOAST table.Now, simply use substr() to grab any random chunk:
SELECT substr(foo_bytea, 20000, 100) from foo where foo_id = 42;This would imply that every little read would have to do a scan on a table
to find the row and then to perform the substr. An open command can
be optimized a lot more, for example to cache entries that have been
opened so that it's fast to read the next 1kb or whatever you want.Also, the above does not solve writes at all which can also be made
transaction safe and fast with a better api where you can update a part
of a field.
I brought it up once and Tom answered that TOAST tables are not
transaction aware, so you can't update just some parts of toasted
entities - you must always write the whole fields. So this will not be
just an api change.
The above is not really a replacement of the current lo-objects.
True.
-------------
Hannu
On Sun, 26 Oct 2003, Hannu Krosing wrote:
I brought it up once and Tom answered that TOAST tables are not
transaction aware, so you can't update just some parts of toasted
entities - you must always write the whole fields. So this will not be
just an api change.
Yes, the blocks (or what one selects to work with) in a toasted field have
to have transaction stuff stored, just like tuples. That is clear if
different transactions shall have different views of the "file".
--
/Dennis