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

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

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

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

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