Re: pg_migrator and an 8.3-compatible tsvector data type

Greg Stark wrote:
> On Sat, May 30, 2009 at 1:11 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> > I have discovered a simpler solution using ALTER TABLE and calling a
> > conversion function:
> >
> > ? ? ? ?test=> CREATE TABLE tsvector_test(x tsvector);
> > ? ? ? ?CREATE TABLE
> > ? ? ? ?test=> ALTER TABLE tsvector_test ALTER COLUMN x TYPE tsvector
> > ? ? ? ?test-> USING conversion_func(x);
> > ? ? ? ?ALTER TABLE
> >
> > No need for a fake data type and the required index infrastructure.
>
> I assume you're putting this in the list of commands to run
> post-migration along with any reindex commands etc? Because it will
> take a while (still faster than dump/reload i think).

Yes, current CVS now does this. I added logic so if you rebuilt the
table, the index will not be rebuilt again because it is a GIN index.

> For this case, assuming the new tsvector's output function doesn't get
> confused by the old ordering, I think you can just use USING
> x::text::tsvector as your conversion expression. For more complex
> cases you might need to package up the old output function.

Wow, cool idea. I will test that.

> Also note that you'll want to do any other conversions in the same
> table at the same time rather than doing multiple conversions.

Wow, I didn't know we supported that too:

ALTER TABLE t ALTER COLUMN x TYPE tsvector USING x, ALTER
COLUMN y TYPE tsvector USING y;

I will work on that also.

> Also, one gotcha to note is that tsvector data can appear inside
> composite data types or arrays. I don't think that's common so perhaps
> just a warning in the readme would suffice, but it's something to note
> at least.

Wow, not sure how I would even find that.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +