Thread: GUID in postgres
Hi,
Is there any concept of sys_Guid in postgres.
If yes what is that ??
As i am using GUID in sql and oracle, then what is counterpart of this in postgres. It is urgent.
Thanks in advance
Bye
Dinesh Parikh
Dinesh,
Please do not cross-post to two different lists. It annoys those of us
who are subscribed to both lists, and you are less likely to get help.
> Is there any concept of sys_Guid in postgres.
> If yes what is that ??
> As i am using GUID in sql and oracle, then what is counterpart of
> this in postgres. It is urgent.
No. Frankly, I don't know what a sys_GUID is. However, PostgreSQL
allows you to create your own functions, operators, and data types, so
I'm sure you could make your own GUID, whatever one is.
-Josh
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
Attachment
"Josh Berkus" <josh@agliodbs.com> writes:
> Please do not cross-post to two different lists. It annoys those of us
> who are subscribed to both lists, and you are less likely to get help.
Just FYI, the Postgres mail server has a very nifty feature you can set
so that you get only one copy of cross-posted messages. It's a real
godsend IMHO. Set your subscription class to "unique" rather than
"each" for all lists you are on, and presto.
regards, tom lane
Josh writes: > I'm sure you could make your own GUID, whatever one is. Globally Unique IDentifier, probably. Just hash a 128 bit random number with the current date. -- John Hasler john@dhh.gt.org Dancing Horse Hill Elmwood, Wisconsin
On Thursday 25 October 2001 03:41, John Hasler wrote: > Josh writes: > > I'm sure you could make your own GUID, whatever one is. > > Globally Unique IDentifier, probably. Just hash a 128 bit random number > with the current date. That gives you no gurantee it will be unique. What we are using is the following: - All tables in need of a global ID _within_ a database inherit a globid table which contains nothing but an ID of type serial. - When we need cross-database unique IDs within the same system, the globid table contains a database identifier as well (like the OID of the pg_database entry for the database). Horst
I wrote: > Globally Unique IDentifier, probably. Just hash a 128 bit random number > with the current date. Horst writes: > That gives you no gurantee it will be unique. There is no such guarantee. The probability of a collision due to errors and bugs using a "deterministic" system is sure to be at least as large as the the probability of a chance collision using large random numbers (_random_, not pseudorandom). Stick machine, table, and database ID's in there as well if it makes you more comfortable, but even without them the risk of a collision is down there with the risk of cosmic ray induced errors. _Nothing_, however, can make it zero. > - All tables in need of a global ID _within_ a database inherit a globid > table which contains nothing but an ID of type serial. - When we need > cross-database unique IDs within the same system, the globid table > contains a database identifier as well (like the OID of the pg_database > entry for the database). And that's fine, but the GUID system uses the word "global" in a much more grandiose sense. -- John Hasler john@dhh.gt.org Dancing Horse Hill Elmwood, Wisconsin
Horst,
> What we are using is the following:
> - All tables in need of a global ID _within_ a database inherit a
> globid
> table which contains nothing but an ID of type serial.
> - When we need cross-database unique IDs within the same system, the
> globid
> table contains a database identifier as well (like the OID of the
> pg_database
> entry for the database).
Well, I think you've just answered your own question. Build the above.
In more specific:
1. PostgreSQL does not, as a design decision, support inter-database
queries. So an inter-database ID is not particularly useful.
2. If you needed an id to be unique between servers for some reason,
simply make it a two-column ID: one column for the sequence (see below)
and one for the server name/ID
3. Sequences are guarenteed unique within a database up to the limits of
INT4 (2.4 billion). Read up on them in the postgreSQL docs. Also see
my posts on pgsql-sql for the last week regarding primary keys.
-Josh Berkus
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
Attachment
On Срд, 2001-10-24 at 09:55, Dinesh Parikh wrote: > Hi, > Is there any concept of sys_Guid in postgres. > If yes what is that ?? > As i am using GUID in sql and oracle, then what is counterpart of this in postgres. It is urgent. > > Thanks in advance > Bye > Dinesh Parikh > Look at http://www.taurussoft.org/files/uniqueidentifier-0.1.9.tar.gz It uses libuuid from e2fsprogs to deal with UUIDs (GUIDs). Regards, Dmitry
Hi I think most of you are missing the point here. GUIDs are unique and they are required for Enterprise development. For those of you who have used multi master replication on Oracle or MS SQL, you know that identity columns and sequences are problematic. The only way to work with primary key columns in to seed the values of the identity column or sequence differently for each of the servers in the cluster, such that, a duplicate value would never occur. This is a very sloppy approach to dealing with the problem as it requires a considerable amount of maintenance; furthermore, anyone who takes this approach shouldn't be working with computers. In this type of environment you would use a GUID in place of an identity column. Since the value is unique on the individual server and on the cluster of servers (because the MAC addresses are different) you won't be getting errors from non-unique values being inserted in the primary key during the replication. We've have one system that has been running MS SQL for over a year now without any GUID errors. The largest table in the system has over 19 million records. Do a search for "multi master replication" and you should pick up some info on how to properly implement a GUID. The simplest approach is to use some combination of a sequence and the MAC address. > Hi, > Is there any concept of sys_Guid in postgres. > If yes what is that ?? > As i am using GUID in sql and oracle, then what is counterpart of this in postgres. It is urgent. > > Thanks in advance > Bye > Dinesh Parikh
Folks, > I think most of you are missing the point here. > > GUIDs are unique and they are required for Enterprise development. I don't know. I have yet to hear a persuasive argument as to why none of the schemes previously mentioned would not work. While a "GUID" automated by the RDBMS platform is convenient, it is by no means necessary. Any number of approaches can be substituted. For example, I have a system that requires middleware-level interaction between 3 database servers. The main data tables in these databases all have 2-column primary keys; one column for the (locally unique) sequence, one column for the (globally unique) server ID. This scheme fulfills all of the functionality that you describe, without the overhead of complex random seed mechanisms or other proprietary overhead. Also, any requests that are strictly local in nature need only query the local id without worrying about the second column. > Do a search for "multi master replication" and you should pick up > some > info on how to properly implement a GUID. The simplest approach is to > use some combination of a sequence and the MAC address. SO what happens if you swap out the network card? -Josh ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
Hi The overhead in generating GUIDs is minimal. I've never heard of a GUID implementation that uses random numbers. In most cases it is a functions of the date, a sequence, and/or the MAC. Why would you want to use two columns when you use could one? Two columns in each table would clutter your scheme as well as your procedures. It doesn't matter if you change the network card. The GUIDs will still be unique. The MAC does not make the GUID unique on any given server. The rest of the GUID generation function accomplishes this task. All that is accomplished by incorporating the MAC into the GUID is uniqueness between machines. -----Original Message----- From: Josh Berkus [mailto:josh@agliodbs.com] Sent: Friday, October 26, 2001 11:31 AM To: postgresql_sql@kaiserdigital.com; pgsql-sql@postgresql.org Subject: Re: [SQL] GUID in postgres Folks, > I think most of you are missing the point here. > > GUIDs are unique and they are required for Enterprise development. I don't know. I have yet to hear a persuasive argument as to why none of the schemes previously mentioned would not work. While a "GUID" automated by the RDBMS platform is convenient, it is by no means necessary. Any number of approaches can be substituted. For example, I have a system that requires middleware-level interaction between 3 database servers. The main data tables in these databases all have 2-column primary keys; one column for the (locally unique) sequence, one column for the (globally unique) server ID. This scheme fulfills all of the functionality that you describe, without the overhead of complex random seed mechanisms or other proprietary overhead. Also, any requests that are strictly local in nature need only query the local id without worrying about the second column. > Do a search for "multi master replication" and you should pick up some > info on how to properly implement a GUID. The simplest approach is to > use some combination of a sequence and the MAC address. SO what happens if you swap out the network card? -Josh ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
"Kaiserdigital": > Why would you want to use two columns when you use could one? Two > columns in each table would clutter your scheme as well as your > procedures. It's a relational integrity issue. A GUID, by definition, contains two pieces of information: 1. The local primary key 2. The server unique ID As such, RDBMS design principles (the Second Normal Form, I believe, correct me if I'm remebering wrong) mandates that they be kept in two columns. This is a pet peeve of mine, as DB vendors and beginner DBA's today seem to be in a rush to embrase "non-atomic" fields willy-nilly, abandoning 20 years of accumulated RDBMS wisdom. > It doesn't matter if you change the network card. The GUIDs will > still > be unique. The MAC does not make the GUID unique on any given server. > The rest of the GUID generation function accomplishes this task. All > that is accomplished by incorporating the MAC into the GUID is > uniqueness between machines. Makes sense if you have an open-ended network of machines so that a simple numbering sequence won't work. With 3 servers, "1", "2", "3" work just as well, and don't have the 12-byte overhead of a MAC address. -Josh ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
On Птн, 2001-10-26 at 21:14, postgresql_sql@kaiserdigital.com wrote: > Hi > > I think most of you are missing the point here. > I'm missing nothing :) "uniqueidentifier" (128-bit unique value) data type fot PostgresSQL exists and is used at least by me. It utilizes functionality of uuid library from widely used in Linux e2fsprogs package. Uuid library itself can generate UUIDs with MAC/time but if /dev/urandom was detected the true random 16 bytes will be used by default. And yes, this type is great for primary keys. Regards, Dmitry