Skip site navigation (1) Skip section navigation (2)

Avoiding io penalty when updating large objects

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 (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-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

Responses

pgsql-hackers by date

Next:From: Stephen FrostDate: 2005-06-29 02:47:11
Subject: Re: Open items
Previous:From: Bruce MomjianDate: 2005-06-29 02:02:53
Subject: Open items

pgsql-general by date

Next:From: Alvaro HerreraDate: 2005-06-29 02:53:17
Subject: Re: [HACKERS] Avoiding io penalty when updating large objects
Previous:From: Steve - DNDDate: 2005-06-29 01:39:16
Subject: Connection local variables?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group