Skip site navigation (1) Skip section navigation (2)

Re: pg_migrator and an 8.3-compatible tsvector data type

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: Bruce Momjian <bruce(at)momjian(dot)us>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Josh Berkus <josh(at)agliodbs(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>, Oleg Bartunov <oleg(at)sai(dot)msu(dot)su>, Teodor Sigaev <teodor(at)sigaev(dot)ru>
Subject: Re: pg_migrator and an 8.3-compatible tsvector data type
Date: 2009-05-30 12:11:47
Message-ID: 200905301211.n4UCBlK03718@momjian.us (view raw or flat)
Thread:
Lists: pgsql-hackers
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. +

In response to

Responses

pgsql-hackers by date

Next:From: Bernd HelmleDate: 2009-05-30 13:48:14
Subject: Re: bytea vs. pg_dump
Previous:From: David E. WheelerDate: 2009-05-30 06:19:00
Subject: Re: search_path improvements WAS: search_path vs extensions

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group