I always forget that this goes to the writer itself and not to the
group.
>
>
> Ok, this is a major setback in some of my procedures.
> From time to time, I must update one field in about 10% of the records.
> So this will take time.
>
> How can I work around that ?
>
> Some personal opinions ...
> 1) Drop indexes, run update, create indexes, vacuum
> 2) Move the field to another table and use joins ? I could delete the
> records when needed and add them again
>
>
> This mechanism, of inserting a new record and marking the old one, is
> that data kept somewhere where I can "see" it ?
> I need for one app a trace of all my changes in the database. I have
> a set of triggers to do that for the moment on each table.
> Could I use that mechanism somehow to avoid my triggers ?
> Any documentation on that mechanism (hacker stuff like what tables are
> used) ?
> Any good books on stuff like this ? I love to read and know how the
> inside mechanics work.
>
> Tnx
>
>
>
> On 21 Aug 2005, at 21:06, Jeffrey W. Baker wrote:
>
>> On Sun, 2005-08-21 at 20:32 +0200, Yves Vindevogel wrote:
>>>
>>>
>>> _____________________________________________________________________
>>> _
>>>
>>> Hi,
>>>
>>> Say I have a table with column A, B, C, D
>>> A has a unique index on it (primary key)
>>> B and C have a normal index on it
>>> D has no index
>>>
>>> If I perform a query like update tbl set D = 'whatever' ;
>>> that should make no difference on the indexes on the other columns,
>>> right ?
>>
>> What postgresql does on update is to make a new record, so there will
>> be
>> two records in your table and two records in your index. You would
>> need
>> to vacuum the table to mark the space for the old record free, and you
>> would need to reindex the table to shrink the index.
>>
>>>
>>> Or is there some kind of mechanism that does create a sort of new
>>> record, thus makes the indexes go wild.
>>
>> Yes.
>>
>> -jwb
>>
>>
>>
> Met vriendelijke groeten,
> Bien à vous,
> Kind regards,
>
> Yves Vindevogel
> Implements
>