Horton
Menu

Thread

  1. GUID in postgres

    Dinesh Parikh <dineshp@newgen.co.in> — 2001-10-24T05:55:59Z

    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

  2. Re: GUID in postgres

    Josh Berkus <josh@agliodbs.com> — 2001-10-24T15:31:51Z

    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

  3. Re: GUID in postgres

    John Hasler <john@dhh.gt.org> — 2001-10-24T17:41:11Z

    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

  4. Cross-posting (was Re: GUID in postgres)

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-10-24T18:43:48Z

    "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

  5. Re: GUID in postgres

    Horst Herb <hherb@malleenet.net.au> — 2001-10-25T03:53:27Z

    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

  6. Re: GUID in postgres

    John Hasler <john@dhh.gt.org> — 2001-10-25T12:33:04Z

    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

  7. Re: GUID in postgres

    Josh Berkus <josh@agliodbs.com> — 2001-10-25T15:34:09Z

    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

  8. Re: GUID in postgres

    Dmitry G. Mastrukov Дмитрий Геннадьевич Мастрюков <dmitry@taurussoft.org> — 2001-10-26T06:57:36Z

    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

  9. Re: GUID in postgres

    postgresql_sql@kaiserdigital.com — 2001-10-26T17:14:19Z

    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

  10. Re: GUID in postgres

    Josh Berkus <josh@agliodbs.com> — 2001-10-26T18:31:16Z

    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 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

  11. Re: GUID in postgres

    postgresql_sql@kaiserdigital.com — 2001-10-26T19:04:12Z

    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 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

  12. Re: GUID in postgres

    Josh Berkus <josh@agliodbs.com> — 2001-10-26T20:00:04Z

    "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 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

  13. Re: GUID in postgres

    Dmitry G. Mastrukov Дмитрий Геннадьевич Мастрюков <dmitry@taurussoft.org> — 2001-10-26T23:31:04Z

    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