Replacing an index item

From: Carsten Kropf <ckropf2(at)fh-hof(dot)de>
To: pgsql-general(at)postgresql(dot)org
Subject: Replacing an index item
Date: 2010-03-11 11:24:41
Message-ID: E9250A28-80E3-41B9-BCF7-2E2B84621904@fh-hof.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello everybody,
I have a question about the replacement of an item in an index page. I want to overwrite an existing item inside an index page. However, I noticed that each time, I replaced an item, the free space in the given page was decreasing. I didn't want to delete the existing item and insert a new one properly, however this seems to be the only option I have (based on this observation).
My code (in C) was the following before:

// key tuple of child element
iid
= PageGetItemId(state->stack->parent->page, state->stack->parent->childoffnum);
ItemIdSetUnused(iid);
PageIndexTupleDelete(state->stack->parent->page,
state->stack->parent->childoffnum);
// key tuple for parent is first tuple of children entry
keyTup
= (IndexTuple) PageGetItem(state->stack->page, PageGetItemId(state->stack->page, FirstOffsetNumber));

PageAddItem(state->stack->parent->page, (Item) keyTup,
IndexTupleSize(keyTup), state->stack->parent->childoffnum,
true, false);

However, this resulted (as I already mentioned) in an ever decreasing free space in the page which after that resulted in a page overflow at some point (resulting in splits etc).
The code, I now use looks like the following:
PageIndexTupleDelete(state->stack->parent->page,
state->stack->parent->childoffnum);
//PageIndexTupleDelete(state->stack->parent->page, state->stack->parent->childoffnum);
// key tuple for parent is first tuple of children entry
keyTup
= (IndexTuple) PageGetItem(state->stack->page, PageGetItemId(state->stack->page, FirstOffsetNumber));

itupvec[0] = (IndexTuple) palloc0(IndexTupleSize(keyTup));
memcpy(itupvec[0], keyTup, IndexTupleSize(keyTup));

putTuple(state->r, state->stack->parent->page, state->stack->parent->buffer, itupvec, 1, state->stack->parent->childoffnum, btreestate);

Whereas the putTuple refers to a function that puts the tuple and shifts existing tuples, if necessary. However, I want to avoid this, because it still requires a little bit of overhead to reorganize it properly. I would prefer the first option, but it does not seem to have good results based on the "waste" of space. Could anyone please give me some help according to this issue?

Best regards
Carsten Kropf

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Filip Rembiałkowski 2010-03-11 12:03:07 Re: how to remove super user
Previous Message Greg Smith 2010-03-11 08:45:02 Re: SAS Raid10 vs SATA II Raid10 - many small reads and writes