T1: A B C and T2 A D (to avoid the updates)
works very well with a simple query
Insert into T2 (A, D)
select A, functionToGetD from T1 left join T2 on T1.A = T2.A
where T2.A is null
The above gives me the new records for those where D was not filled yet.
Since they are all new records, I have no trouble with the MVCC
On 21 Aug 2005, at 21:06, Jeffrey W. Baker wrote:
On Sun, 2005-08-21 at 20:32 +0200, Yves Vindevogel wrote:Met vriendelijke groeten,
______________________________________________________________________
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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Bien à vous,
Kind regards,
Yves Vindevogel
Implements
Mail: [EMAIL PROTECTED] - Mobile: +32 (478) 80 82 91
Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76
Web: http://www.implements.be
<x-tad-smaller>
First they ignore you. Then they laugh at you. Then they fight you. Then you win.
Mahatma Ghandi.</x-tad-smaller>
---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
