Re: pg_migrator and an 8.3-compatible tsvector data type

Bruce Momjian wrote:
> Tom Lane wrote:
> > Josh Berkus <josh(at)agliodbs(dot)com> writes:
> > > It would be nice to have pg_migrator handle this, especially if we could
> > > do it in parallel. Then we just have to warn users that migrating a
> > > database with tsvector columns takes significantly longer. That is,
> >
> > > 1) do rest of catalog swap and link/copy of objects.
> > > 2) mark all tsvector columns as 83_tsvector and add new tsvector type
> > > (these columns will be unusable for queries)
> > > 3) bring up database
> > > 4) search for all 83_tsvector columns
> > > 5) do ALTER TABLE on each of these columns, in parallel, up to a
> > > configuration setting (default 3).
> >
> > pg_migrator is already emitting a script that is intended to be run
> > after conversion, to handle REINDEXing of incompatible indexes. That
> > could easily be made to do ALTER TYPE on old tsvector columns too, no?
>
> Hmmm. Well, the problem right now with v8_3_tsvector is that it fails
> on index create, even when the index is empty, because I can't figure
> out how to simply set up the proper index catalog entries. Once that is
> fixed and I can bind tsvector to v8_3_tsvector on schema creation, I can
> easily emit ALTER TABLE to fix the issue. And, at that point the
> tsvector GIN indexes would be automatically created so I can skip that
> part.

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.

--
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. +