From: | Mark Dilger <pgsql(at)markdilger(dot)com> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org, pgsql-general(at)postgresql(dot)org |
Subject: | Avoiding io penalty when updating large objects |
Date: | 2005-06-29 02:38:43 |
Message-ID: | 42C209B3.5010901@markdilger.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
I would like to write a postgres extension type which represents a btree of data
and allows me to access and modify elements within that logical btree. Assume
the type is named btree_extension, and I have the table:
CREATE TABLE example (
a TEXT,
b TEXT,
c BTREE_EXTENSION,
UNIQUE(a,b)
);
If, for a given row, the value of c is, say, approximately 2^30 bytes large,
then I would expect it to be divided up into 8K chunks in an external table, and
I should be able to fetch individual chunks of that object (by offset) rather
than having to detoast the whole thing.
But what if I want to update a single chunk, or only a couple chunks? How can I
go about loading chunks, modifying them, and writing them back to disk, without
incurring the overhead of writing 2^30 bytes back out to disk? And if I can do
this in a hand coded c function, what does the corresponding SQL statement look
like to call the function? Is it an update statement?
Also, is it possible that only the rows in the *external* table get marked as
updated during my transaction, or will the row in the "example" table be marked
as updated?
I expect this is not possible, but it would be really great if it were, and I
haven't found a definitive "No, you can't do this" in the documentation yet.
The idea is to store the first and second level entries of a tree directly in
columns "a" and "b", but then to store arbitrarily deep children in a btree type
stored in column "c". It doesn't make sense to have a really wide table to
represent the tree for multiple reasons, mostly involving data duplication in
the leftward columns but also because you can't know ahead of time how wide to
make the table.
I look forward to any useful responses.
Thanks,
Mark Dilger
From | Date | Subject | |
---|---|---|---|
Next Message | Alvaro Herrera | 2005-06-29 02:53:17 | Re: [HACKERS] Avoiding io penalty when updating large objects |
Previous Message | Steve - DND | 2005-06-29 01:39:16 | Connection local variables? |
From | Date | Subject | |
---|---|---|---|
Next Message | Stephen Frost | 2005-06-29 02:47:11 | Re: Open items |
Previous Message | Bruce Momjian | 2005-06-29 02:02:53 | Open items |