Orange is my favorite color
December 16, 2008

ColdFusion - PostgreSQL - Web/Internet | Trackback | del.icio.us | digg

I’ve been reading through a handful of Jochem’s posts about Adobe’s (incompatible with the standard 35-character) UUID and the rest of the worlds UUID/GUID (36-character). Since PostgreSQL introduced UUIDs in 8.3, I have been thinking about trying to convert my UUID primary keys to the native type in order to gain speed and reduce the size of my database. My database is not particularly large but I do have some tables and queries with lots of joins where all of the relationships are UUIDs. By cutting the storage size roughly in half, the indexes would shrink by 50% and that means lookups and joins go faster. Good for everyone.

The question is, I’ve got a production application that uses char(35) for UUIDs, how am I going to “upsize” to the native type? The traditional way to change a column datatype is to rename it, create a new one and copy across the data. The problem is that these columns have extensive primary and foreign key relationships and if I rename the column, I think I will need to regenerate all of those relationships. I don’t know of any automated way of doing that… maybe dumping the schema and copying out only the keys and indexes?

In the mean time, I had a small brainstorm this morning that I thought I would throw out there and see if anyone has any advice for me:

  1. SET CONSTRAINTS ALL DEFERRED; BEGIN; – Tell postgres to temporarily ignore constraints and initiate a transaction
  2. ALTER TABLE tbl ALTER COLUMN col TYPE char(36) – make enough room to add the extra hypen that differentiates the Adobe UUID and the standard UUID/GUID
  3. UPDATE tbl SET col = regexp_replace(col, '([A-Z0-9]{4})([A-Z0-9]{12})', '\\1-\\2') – Find the 16-character end of the CF UUID and insert a hyphen at position 4 to convert the CF UUID format of 8-4-4-16 to the standard UUID/GUID of 8-4-4-4-12
  4. ALTER TABLE tbl ALTER COLUMN col TYPE uuid – This is the wildcard, will this switch from a char(36) work?
  5. COMMIT; – finish the transaction so constraints will be enabled again

Testing

I got curious while writing this and decided to actually test this, here’s the SQL:

CREATE TABLE uuidtest (
	uidTest CHAR(35) NOT NULL PRIMARY KEY
) WITHOUT OIDS;

INSERT INTO uuidtest VALUES ('C5F25CC3-1D72-822B-795706E5A2C8F6B1');
INSERT INTO uuidtest VALUES ('A5F01CC3-1D72-822B-795706E5A2C8F6B1');
INSERT INTO uuidtest VALUES ('D6F25CC3-1D72-822B-795706E5A2C8F6B1');
INSERT INTO uuidtest VALUES ('C5FC2CC3-1D72-822B-795706E5A2C8F6B1');
INSERT INTO uuidtest VALUES ('E5F25CC3-1D72-822B-795706E5A2C8F6B1');

ALTER TABLE uuidtest ALTER COLUMN uidTest TYPE char(36);

SELECT * FROM uuidtest;  // yup, those are UUIDs

UPDATE uuidtest SET uidTest = regexp_replace(uidTest, '([A-Z0-9]{4})([A-Z0-9]{12})', '\\1-\\2');

SELECT * FROM uuidtest; // now we have GUIDs

ALTER TABLE uuidtest ALTER COLUMN uidTest TYPE uuid;

Everything was going swimmingly until the last command which resulted in ERROR: column "uidtest" cannot be cast to type "uuid". Ouch. So close and yet so far.

Now what? It looks like the only option is to do the copy-delete-rename dance and then recreate all of the indices, foreign keys and views. I can get a lot of that data from the system tables but what a pain in the butt.

And then I need to deal with Transfer. Hrmm… how much is a 15%+ lookup performance and 50% storage reduction worth? :)

Any other ideas?

7 Comments

  1. Andrew said:

    on December 16, 2008 at 10:31 am

    Have you tired?

    ALTER TABLE uuidtest ALTER COLUMN uidTest TYPE uuid USING uidTest::uuid;

    (I just found out about the USING keyword just recently: http://archives.postgresql.org/pgsql-sql/2008-11/msg00047.php)

  2. brian said:

    on December 16, 2008 at 11:01 am

    @Andrew – that worked! Awesome suggestion! I do relatively little column altering so I never looked to see if there were additional options. Here’s the results:


    SELECT * from uuidtest;
    "c5f25cc3-1d72-822b-7957-06e5a2c8f6b1"
    "a5f01cc3-1d72-822b-7957-06e5a2c8f6b1"
    "d6f25cc3-1d72-822b-7957-06e5a2c8f6b1"
    "c5fc2cc3-1d72-822b-7957-06e5a2c8f6b1"
    "e5f25cc3-1d72-822b-7957-06e5a2c8f6b1"

    And this test returned Yes:

    #yesNoFormat(isValid(‘guid’, “c5f25cc3-1d72-822b-7957-06e5a2c8f6b1″))#

    There are some Transfer issues to solve but otherwise this makes it feasible. Thanks again!

  3. Jochem van Dieten said:

    on December 16, 2008 at 11:20 am

    You should be able to wrap the whole conversion in one statement which should of course be faster. And instead of deferring foreign key constraints you can temporarily disable them:

    ALTER TABLE uuidTest DISABLE TRIGGER ALL;

    ALTER TABLE uuidtest ALTER COLUMN uidTest TYPE uuid USING CAST(regexp_replace(uidTest, ‘([A-Z0-9]{4})([A-Z0-9]{12})’, ‘\\1-\\2′) AS uuid);

    ALTER TABLE uuidTest ENABLE TRIGGER ALL;

  4. brian said:

    on December 16, 2008 at 11:58 am

    I got a little further and tried with a foreign key:

    
CREATE TABLE uuidtest (
	uidTest CHAR(35) NOT NULL PRIMARY KEY
) WITHOUT OIDS;

INSERT INTO uuidtest VALUES ('C5F25CC3-1D72-822B-795706E5A2C8F6B1');
INSERT INTO uuidtest VALUES ('A5F01CC3-1D72-822B-795706E5A2C8F6B1');
INSERT INTO uuidtest VALUES ('D6F25CC3-1D72-822B-795706E5A2C8F6B1');
INSERT INTO uuidtest VALUES ('C5FC2CC3-1D72-822B-795706E5A2C8F6B1');
INSERT INTO uuidtest VALUES ('E5F25CC3-1D72-822B-795706E5A2C8F6B1');

create table uuidtest_ref (
	uidTest char(35) not null references uuidtest(uidTest) on update cascade on delete cascade
) WITHOUT OIDS;

INSERT INTO uuidtest_ref VALUES ('C5F25CC3-1D72-822B-795706E5A2C8F6B1');
INSERT INTO uuidtest_ref VALUES ('A5F01CC3-1D72-822B-795706E5A2C8F6B1');

    But trying to run the ALTER TYPE doesn’t work:


    ERROR: foreign key constraint "uuidtest_ref_uidtest_fkey" cannot be implemented
    SQL state: 42804
    Detail: Key columns "uidtest" and "uidtest" are of incompatible types: character and uuid.

    I tried this with a transaction block, with deferred constraints, with disabled triggers, etc. I think the only option is to eliminate the FK and recreate it?

  5. Jochem van Dieten said:

    on December 16, 2008 at 1:53 pm

    Did you alter both the uuidtest and uuidtest_ref tables? The source of the alter table command says:

    /*
    * Foreign key constraints are checked in a final pass, since (a) it’s
    * generally best to examine each one separately, and (b) it’s at least
    * theoretically possible that we have changed both relations of the
    * foreign key, and we’d better have finished both rewrites before we try
    * to read the tables.
    */

    But the alter table commands were refactored significantly in the current version so you may need to get a copy of head to get this working.

  6. brian said:

    on December 16, 2008 at 3:27 pm

    @Jochem – strange – I went looking in CVS and 8.3.0 has that same comment but it doesn’t work for me. I am trying this on 8.3.4 on Windows… so not sure what’s going on but I ran this:

    
BEGIN;

SET CONSTRAINTS ALL DEFERRED;
ALTER TABLE uuidTest DISABLE TRIGGER ALL;
ALTER TABLE uuidTest_ref DISABLE TRIGGER ALL;

ALTER TABLE uuidtest ALTER COLUMN uidTest TYPE uuid USING uidTest::uuid;
ALTER TABLE uuidtest_ref ALTER COLUMN uidTest TYPE uuid USING uidTest::uuid;

ALTER TABLE uuidTest ENABLE TRIGGER ALL;
ALTER TABLE uuidTest_ref ENABLE TRIGGER ALL;

COMMIT;

    No combination of the transaction, constraint or trigger syntax seems to fix this.

  7. Orange is my favorite color » Blog Archive » Converting from UUIDs to GUIDs - Solution said:

    on December 20, 2008 at 3:32 pm

    [...] my previous post, I detailed my desire to convert from char(35) to Postgres’ native uuid datatype for storing [...]

{ RSS feed for comments on this post}