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: Greg Stark <stark(at)enterprisedb(dot)com>
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-31 13:47:21
Message-ID: 200905311347.n4VDlLg26731@momjian.us (view raw or flat)
Thread:
Lists: pgsql-hackers
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).
> 
> 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.
> 
> Also note that you'll want to do any other conversions in the same
> table at the same time rather than doing multiple conversions.

I have implemented Greg's ideas using ::text::tsvector casting and it
worked:

	$ psql test
	psql (8.4beta2)
	Type "help" for help.
	
	test=> select * from tsvector_test;
	       x
	----------------
	 'c' 'bb' 'aaa'
	(1 row)
	
	test=>
	test=> \q

	$ psql test < /u/postgres/rebuilt_tsvector_tables.txt
	ALTER TABLE
	ALTER TABLE
	ALTER TABLE
	$ psql test
	psql (8.4beta2)
	Type "help" for help.
	
	test=> select * from tsvector_test;
	       x
	----------------
	 'aaa' 'bb' 'c'
	(1 row)

Here is the script that groups multiple tsvector columns into a single
ALTER TABLE:

	ALTER TABLE "public"."tsvector_test"
	  ALTER COLUMN "x" TYPE tsvector USING "x"::text::tsvector;
	ALTER TABLE "public"."tsvector_test2"
	  ALTER COLUMN "x" TYPE tsvector USING "x"::text::tsvector,
	  ALTER COLUMN "y" TYPE tsvector USING "y"::text::tsvector;
	ALTER TABLE "public"."tsvector_test3"
	  ALTER COLUMN "x" TYPE tsvector USING "x"::text::tsvector,
	  ALTER COLUMN "y" TYPE tsvector USING "y"::text::tsvector,
	  ALTER COLUMN "z" TYPE tsvector USING "z"::text::tsvector;

I knew I was going to have to deal with this optimization but didn't
realize ALTER TABLE already supported multiple TYPE changes.

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

pgsql-hackers by date

Next:From: Bruce MomjianDate: 2009-05-31 14:04:29
Subject: Re: pg_migrator and an 8.3-compatible tsvector data type
Previous:From: Nikhil SontakkeDate: 2009-05-31 07:37:20
Subject: Re: ruby connect

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