On Sun, Dec 5, 2010 at 7:58 PM, Andres Freund <andres@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 -
