Обсуждение: best practice transitioning from one datatype to another
Another UUID question... I was hoping to put this off for a while, but it looks like it's going to need to happen sooner than later. Rats! I keep telling myself transitioning to core datatypes is good for the soul.
While transitioning from 8.1 to 8.4, I need to transition to the internal UUID type in place of the contrib/uniqueidentifier module. I've built the database around uniqueidentifier, so nearly every table has one column of that data type. It's going to be tedious to
ALTER TABLE foo ALTER COLUMN bar SET DATA TYPE uuid;
...repeat 600 times...
I'll also have to drop and reload the views and the rules on tables. It'll be tedious even if the tables have no data in them.
Can anyone recommend a better/faster way to make the transition?
CG <cgg007@yahoo.com> writes:
> While transitioning from 8.1 to 8.4, I need to�transition to�the internal UUID type in place of the
contrib/uniqueidentifiermodule. I've built the database around uniqueidentifier, so nearly every table has one column
ofthat data type. It's going to be tedious to
> ALTER TABLE foo ALTER COLUMN bar SET DATA TYPE uuid;
> ...repeat 600 times...
> I'll�also have to drop�and reload the�views and the rules on tables. It'll be tedious even if the tables have no data
inthem.
> Can anyone recommend�a better/faster way to make the transition?
Couldn't you rename the type to uuid in the 8.1 database before you
dump?
regards, tom lane
On Jul 16, 8:05 am, t...@sss.pgh.pa.us (Tom Lane) wrote: > CG <cgg...@yahoo.com> writes: > > While transitioning from 8.1 to 8.4, I need to transition to the internal UUID type in place of the contrib/uniqueidentifiermodule. I've built the database around uniqueidentifier, so nearly every table has one column ofthat data type. It's going to be tedious to > > ALTER TABLE foo ALTER COLUMN bar SET DATA TYPE uuid; > > ...repeat 600 times... > > I'll also have to drop and reload the views and the rules on tables. It'll be tedious even if the tables have no datain them. > > Can anyone recommend a better/faster way to make the transition? > > Couldn't you rename the type to uuid in the 8.1 database before you > dump? > > regards, tom lane > > -- > Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) > To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general Just an idea - don't know if it works, first try on a test server: 1. Dump the complete database into text format (pg_dump --format=t) 2. Do a search and replace from "uniqueidentifier" to "uuid" 3. Reimport Best Regards, Arndt Lehmann
Arndt Lehmann schrieb: > On Jul 16, 8:05 am, t...@sss.pgh.pa.us (Tom Lane) wrote: >> CG <cgg...@yahoo.com> writes: >>> While transitioning from 8.1 to 8.4, I need to transition to the internal UUID type in place of the contrib/uniqueidentifiermodule. I've built the database around uniqueidentifier, so nearly every table has one column ofthat data type. It's going to be tedious to >>> ALTER TABLE foo ALTER COLUMN bar SET DATA TYPE uuid; >>> ...repeat 600 times... >>> I'll also have to drop and reload the views and the rules on tables. It'll be tedious even if the tables have no datain them. >>> Can anyone recommend a better/faster way to make the transition? >> Couldn't you rename the type to uuid in the 8.1 database before you >> dump? >> >> regards, tom lane >> >> -- >> Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) >> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general > > Just an idea - don't know if it works, first try on a test server: > 1. Dump the complete database into text format (pg_dump --format=t) > 2. Do a search and replace from "uniqueidentifier" to "uuid" > 3. Reimport > > Best Regards, > Arndt Lehmann > uh - --format=t means tar format. --format=p means plain text ... Or am I missing something? $pg_dump --help Usage: pg_dump [OPTION]... [DBNAME] General options: ... -F, --format=c|t|p output file format (custom, tar, plain text) ... Cheers Andy
On Jul 16, 5:46 pm, a.w...@netzmeister-st-pauli.de (Andreas Wenk) wrote: > Arndt Lehmann schrieb: > > > On Jul 16, 8:05 am, t...@sss.pgh.pa.us (Tom Lane) wrote: > >> CG <cgg...@yahoo.com> writes: > >>> While transitioning from 8.1 to 8.4, I need to transition to the internal UUID type in place of the contrib/uniqueidentifiermodule. I've built the database around uniqueidentifier, so nearly every table has one column ofthat data type. It's going to be tedious to > >>> ALTER TABLE foo ALTER COLUMN bar SET DATA TYPE uuid; > >>> ...repeat 600 times... > >>> I'll also have to drop and reload the views and the rules on tables. It'll be tedious even if the tables have no datain them. > >>> Can anyone recommend a better/faster way to make the transition? > >> Couldn't you rename the type to uuid in the 8.1 database before you > >> dump? > > >> regards, tom lane > > >> -- > >> Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) > >> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general > > > Just an idea - don't know if it works, first try on a test server: > > 1. Dump the complete database into text format (pg_dump --format=t) > > 2. Do a search and replace from "uniqueidentifier" to "uuid" > > 3. Reimport > > > Best Regards, > > Arndt Lehmann > > uh - --format=t means tar format. --format=p means plain text ... > > Or am I missing something? > > $pg_dump --help > Usage: > pg_dump [OPTION]... [DBNAME] > > General options: > ... > -F, --format=c|t|p output file format (custom, tar, plain text) > ... > > Cheers > > Andy > > -- > Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) > To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general Hi Andy, You are perfectly right. My oversight. Best Regards, Arndt Lehmannn
Trying to fix a dump file ...
cat backup.sql | sed -e "s/uniqueidentifier/uuid/g" > backup_fixed.sql
... gives me a dump that won't import. It is hard digging through 30+ gigs of text data to find where sed ate a field delimiter, so I'm going to give Tom's idea a try. I didn't even know the ALTER TYPE x RENAME TO y; was even available, and I probably wouldn't have tried it if Tom hadn't suggested it. It takes a certan amount of chutzpah to make that kind of change before diving into the lengthy process of database upgrading.
From: Arndt Lehmann <arndt.lehmann@gmail.com>
To: pgsql-general@postgresql.org
Sent: Thursday, July 16, 2009 5:22:26 AM
Subject: Re: [GENERAL] best practice transitioning from one datatype to another
On Jul 16, 5:46 pm, a.w...@netzmeister-st-pauli.de (Andreas Wenk)
wrote:
> Arndt Lehmann schrieb:
>
> > On Jul 16, 8:05 am, t...@sss.pgh.pa.us (Tom Lane) wrote:
> >> CG <cgg...@yahoo.com> writes:
> >>> While transitioning from 8.1 to 8.4, I need to transition to the internal UUID type in place of the contrib/uniqueidentifier module. I've built the database around uniqueidentifier, so nearly every table has one column of that data type. It's going to be tedious to
> >>> ALTER TABLE foo ALTER COLUMN bar SET DATA TYPE uuid;
> >>> ...repeat 600 times...
> >>> I'll also have to drop and reload the views and the rules on tables. It'll be tedious even if the tables have no data in them.
> >>> Can anyone recommend a better/faster way to make the transition?
> >> Couldn't you rename the type to uuid in the 8.1 database before you
> >> dump?
>
> >> regards, tom lane
>
> >> --
> >> Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org)
> >> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general
>
> > Just an idea - don't know if it works, first try on a test server:
> > 1. Dump the complete database into text format (pg_dump --format=t)
> > 2. Do a search and replace from "uniqueidentifier" to "uuid"
> > 3. Reimport
>
> > Best Regards,
> > Arndt Lehmann
>
> uh - --format=t means tar format. --format=p means plain text ...
>
> Or am I missing something?
>
> $pg_dump --help
> Usage:
> pg_dump [OPTION]... [DBNAME]
>
> General options:
> ...
> -F, --format=c|t|p output file format (custom, tar, plain text)
> ...
>
> Cheers
>
> Andy
>
> --
> Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general
Hi Andy,
You are perfectly right. My oversight.
Best Regards,
Arndt Lehmannn
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
I'm stumped-- at least for an easy way to do this.
When referencing the uniqueidentifier data type in PostgreSQL 8.1 (now uuid, because of an UPDATE to the pg_ tables) in function definitions in schemas not in the search path, one must reference the data type as "public.uniqueidentifier" (ahem. "public.uuid"). This was done because the query planner couldn't with 100% certainty determine type equality (i.e. for foreign key constraints, comparisons in WHERE clauses...), so it decided to use sequential scans where index scans would have been most appropriate.
When I reload to PostgreSQL 8.4, it doesn't understand public.uuid. So, the only way I can think of to get those functions back into the database is to use a restore list, and keep them from being inserted in the first place. Then, one-at-a-time, re-create them manually. There must be a better way, though!
I'm sure this only my second of several more hurdles to overcome before I'm finished with the transition.
Your wisdom will be appreciated!
CG
From: Tom Lane <tgl@sss.pgh.pa.us>
To: CG <cgg007@yahoo.com>
Cc: pgsql-general@postgresql.org
Sent: Wednesday, July 15, 2009 7:05:47 PM
Subject: Re: [GENERAL] best practice transitioning from one datatype to another
CG <cgg007@yahoo.com> writes:
> While transitioning from 8.1 to 8.4, I need to transition to the internal UUID type in place of the contrib/uniqueidentifier module. I've built the database around uniqueidentifier, so nearly every table has one column of that data type. It's going to be tedious to
> ALTER TABLE foo ALTER COLUMN bar SET DATA TYPE uuid;
> ...repeat 600 times...
> I'll also have to drop and reload the views and the rules on tables. It'll be tedious even if the tables have no data in them.
> Can anyone recommend a better/faster way to make the transition?
Couldn't you rename the type to uuid in the 8.1 database before you
dump?
regards, tom lane