Yes, the ID is generated before the database is contacted, in my case anyway.
The type of UUID that I want is a hybrid, so I would have to write a stored
procedure and then a trigger upon insert to get the UUID. Not rocket science, I
just have more on my plate than I can handle. So PHP is my 'main thing', so I
quickly wrote it in that. Plus, it makes it more database agnostic.
I don't get next/currval behavior, but I don't think I'll need it.
BTW, Switching from Postgres? Not likely anytime soon. I'd have to be up in the
500M+ rows and be in the data warehousing/map reducing arena before I'd
consider THAT. And there's 'flavors' of Postgres that will do that, anyway.
Dennis Gearon
Signature Warning
----------------
It is always a good idea to learn from your own mistakes. It is usually a better
idea to learn from others’ mistakes, so you do not have to make them yourself.
from 'http://blogs.techrepublic.com.com/security/?p=4501&tag=nl.e036'
EARTH has a Right To Life,
otherwise we all die.
----- Original Message ----
From: Craig Ringer <craig@postnewspapers.com.au>
To: Radosław Smogura <rsmogura@softperience.eu>
Cc: Dennis Gearon <gearond@sbcglobal.net>; pgsql-general@postgresql.org
Sent: Wed, January 5, 2011 2:50:11 AM
Subject: Re: [GENERAL] UUID column as pimrary key?
On 01/05/2011 07:31 PM, Radosław Smogura wrote:
> * you have your id, before executing query, (in contrast to all this
> autoincrement) so you may put it in dependant rows
Do you mean that with a UUID, you don't need to talk to the database at all, you
can generate an ID with no interaction with / involvement with the database at
all? Because other than that, there's not much difference in how you normally
work with them.
With a sequence, you might:
CREATE SEQUENCE x_id_seq;
CREATE TABLE x (
id integer PRIMIARY KEY DEFAULT nextval('x_id_seq'),
y integer
);
INSERT INTO x(y) VALUES (1);
With a uuid, you'd:
CREATE TABLE x (
id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
y integer
);
INSERT INTO x(y) VALUES (1);
In either case, you can explicitly call the generator function for seq/uuid -
nextval(seqname) or uuid_generate_v4() respectively - or you can omit the PK
column in your inserts and let the database generate it.
> Personally I prefer pooled incremental id's. Fast, unique, you have Id
> before query - but you need to write "code" by self.
Many libraries / ORMs / etc that interact with Pg will happily take care of this
for you. In fact, I had to fight to convince Hibernate that I *didn't* want it
to increment all my counters in steps of 50.
--
Craig Ringer