On Sun, Dec 5, 2010 at 7:58 PM, Andres Freund <and...@anarazel.de> wrote:
> On Sunday 05 December 2010 13:29:35 Andres Freund wrote: > > On Sunday 05 December 2010 13:07:23 Martin Atukunda wrote: > > > > Due to the wonders of MVCC the old row is still available in the > heap. > > > > Best read the docs about what MVCC means. And as pg's indexes don't > > > > care about visibility it will still try to index the "old" row. > > > > > > Thanks andreas, > > > > > > so, basically, the only way out of this would be to: > > > > > > 1. copy out all the rows > > > 2. truncate the Tables > > > 3. then create the index > > > 4. copy in the rows > > > > Something like: > > > > ALTER TABLE t ALTER apps TYPE text[]; > > ALTER TABLE t ALTER apps TYPE bigint[] USING apps::bigint[]; > On further thought the second one ought to be enough. > Actually on my tests here both are required, though for the large tables - writing them twice makes the process very long. The copy out, truncate, create index, copy in approach seems to work best in this case. - Martin -