From dineshp@newgen.co.in Wed Oct 24 05:58:47 2001 Received: from nh.newgen.co.in (203-195-148-19.now-india.net.in [203.195.148.19] (may be forged)) by postgresql.org (8.11.3/8.11.4) with ESMTP id f9O5wjP19254; Wed, 24 Oct 2001 01:58:47 -0400 (EDT) (envelope-from dineshp@newgen.co.in) Received: from dinesh (ws240.newgen.co.in [192.168.5.240]) by nh.newgen.co.in (8.9.3/8.8.7) with SMTP id LAA28438; Wed, 24 Oct 2001 11:29:37 +0530 Message-ID: <002701c15c50$8e6dbca0$f005a8c0@dinesh> From: "Dinesh Parikh" To: , Subject: GUID in postgres Date: Wed, 24 Oct 2001 11:25:59 +0530 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0024_01C15C7E.A808D3B0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4133.2400 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 X-Archive-Number: 200110/829 X-Sequence-Number: 16636 This is a multi-part message in MIME format. ------=_NextPart_000_0024_01C15C7E.A808D3B0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi,=20 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 p= ostgres. It is urgent. Thanks in advance Bye Dinesh Parikh ------=_NextPart_000_0024_01C15C7E.A808D3B0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi,
Is there any concept of sys_Guid in=20 postgres.
If yes what is that ??
As i am using GUID in sql and oracle,= then=20 what is counterpart of this in postgres. It is urgent.
 
Thanks in advance
Bye
Dinesh Parikh
 
 
------=_NextPart_000_0024_01C15C7E.A808D3B0-- From josh@agliodbs.com Wed Oct 24 15:31:56 2001 Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f9OFVuP53040 for ; Wed, 24 Oct 2001 11:31:56 -0400 (EDT) (envelope-from josh@agliodbs.com) Received: from [209.10.40.250] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.4.7) with HTTP id 491315; Wed, 24 Oct 2001 08:31:51 -0700 From: "Josh Berkus" Subject: Re: GUID in postgres To: "Dinesh Parikh" , X-Mailer: CommuniGate Pro Web Mailer v.3.4.7 Date: Wed, 24 Oct 2001 08:31:51 -0700 Message-ID: In-Reply-To: <002701c15c50$8e6dbca0$f005a8c0@dinesh> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="_===491315====davinci.ethosmedia.com===_" X-Archive-Number: 200110/343 X-Sequence-Number: 5296 This is a multi-part MIME message --_===491315====davinci.ethosmedia.com===_ Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit 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 --_===491315====davinci.ethosmedia.com===_ Content-Type: text/plain Content-Disposition: attachment; filename="" Content-Transfer-Encoding: base64 DQo= --_===491315====davinci.ethosmedia.com===_ Content-Type: text/plain Content-Disposition: attachment; filename="" Content-Transfer-Encoding: base64 DQo= --_===491315====davinci.ethosmedia.com===_ Content-Type: text/plain Content-Disposition: attachment; filename="" Content-Transfer-Encoding: base64 DQo= --_===491315====davinci.ethosmedia.com===_-- From john@dhh.gt.org Wed Oct 24 17:42:07 2001 Received: from forward.airstreamcomm.net (forward.airstreamcomm.net [64.33.128.12]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f9OHg7P81507 for ; Wed, 24 Oct 2001 13:42:07 -0400 (EDT) (envelope-from jghasler@svtel.net) Received: from hasler.dhh.gt.org (elk-ras1-cs-77.dial.airstreamcomm.net [64.33.137.79]) by forward.airstreamcomm.net (8.12.0/8.12.0) with ESMTP id f9OHfxkI007184 for ; Wed, 24 Oct 2001 12:41:59 -0500 (CDT) Received: from [192.168.1.3] (helo=toncho.dhh.gt.org ident=mail) by hasler.dhh.gt.org with esmtp (Exim 3.12 #1 (Debian)) id 15wS2R-0005Dh-00 for ; Wed, 24 Oct 2001 12:41:59 -0500 Received: from john by toncho.dhh.gt.org with local (Exim 3.12 #1 (Debian)) id 15wS1f-0006Ui-00; Wed, 24 Oct 2001 12:41:11 -0500 To: pgsql-sql@postgresql.org Subject: Re: GUID in postgres References: From: John Hasler Date: 24 Oct 2001 12:41:11 -0500 In-Reply-To: Message-ID: <87adyhx7ew.fsf@toncho.dhh.gt.org> Lines: 10 User-Agent: Gnus/5.0808 (Gnus v5.8.8) Emacs/21.1 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Archive-Number: 200110/348 X-Sequence-Number: 5301 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 From hherb@malleenet.net.au Thu Oct 25 03:49:46 2001 Received: from gnumed.dhs.org (modem59.malleenet.net.au [203.143.67.123] (may be forged)) by postgresql.org (8.11.3/8.11.4) with SMTP id f9P3nhP52521 for ; Wed, 24 Oct 2001 23:49:46 -0400 (EDT) (envelope-from hherb@malleenet.net.au) Received: (qmail 30848 invoked by uid 8); 25 Oct 2001 03:49:26 -0000 Message-ID: <20011025034926.30847.qmail@gnumed.dhs.org> Received: from pc-00071.e-smith.gnumed.dhs.org (192.168.1.71, claiming to be "there") by bifrost.e-smith.gnumed.dhs.org with SMTP id smtpdsHOyzU; Thu, 25 Oct 2001 13:49:17 EST Content-Type: text/plain; charset="iso-8859-1" From: Horst Herb To: pgsql-sql@postgresql.org Subject: Re: GUID in postgres Date: Thu, 25 Oct 2001 13:53:27 +1000 X-Mailer: KMail [version 1.3.1] References: <87adyhx7ew.fsf@toncho.dhh.gt.org> In-Reply-To: <87adyhx7ew.fsf@toncho.dhh.gt.org> MIME-Version: 1.0 Content-Transfer-Encoding: 8bit X-Archive-Number: 200110/351 X-Sequence-Number: 5304 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 From john@dhh.gt.org Thu Oct 25 12:35:06 2001 Received: from forward.airstreamcomm.net (forward.airstreamcomm.net [64.33.128.12]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f9PCZ5P60759 for ; Thu, 25 Oct 2001 08:35:06 -0400 (EDT) (envelope-from jghasler@svtel.net) Received: from hasler.dhh.gt.org (elk-ras1-cs-116.dial.airstreamcomm.net [64.33.137.118]) by forward.airstreamcomm.net (8.12.0/8.12.0) with ESMTP id f9PCYu4R020484 for ; Thu, 25 Oct 2001 07:34:59 -0500 (CDT) Received: from [192.168.1.3] (helo=toncho.dhh.gt.org ident=mail) by hasler.dhh.gt.org with esmtp (Exim 3.12 #1 (Debian)) id 15wjiq-0000MH-00 for ; Thu, 25 Oct 2001 07:34:56 -0500 Received: from john by toncho.dhh.gt.org with local (Exim 3.12 #1 (Debian)) id 15wjh2-0006yD-00; Thu, 25 Oct 2001 07:33:04 -0500 To: pgsql-sql@postgresql.org Subject: Re: GUID in postgres References: <87adyhx7ew.fsf@toncho.dhh.gt.org> <20011025034926.30847.qmail@gnumed.dhs.org> From: John Hasler Date: 25 Oct 2001 07:33:04 -0500 In-Reply-To: <20011025034926.30847.qmail@gnumed.dhs.org> Message-ID: <87snc7x5kv.fsf@toncho.dhh.gt.org> Lines: 28 User-Agent: Gnus/5.0808 (Gnus v5.8.8) Emacs/21.1 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Archive-Number: 200110/354 X-Sequence-Number: 5307 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 From josh@agliodbs.com Thu Oct 25 15:34:15 2001 Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f9PFYFP09884 for ; Thu, 25 Oct 2001 11:34:15 -0400 (EDT) (envelope-from josh@agliodbs.com) Received: from [209.10.40.250] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.4.7) with HTTP id 492038; Thu, 25 Oct 2001 08:34:09 -0700 From: "Josh Berkus" Subject: Re: GUID in postgres To: Horst Herb , pgsql-sql@postgresql.org X-Mailer: CommuniGate Pro Web Mailer v.3.4.7 Date: Thu, 25 Oct 2001 08:34:09 -0700 Message-ID: In-Reply-To: <20011025034926.30847.qmail@gnumed.dhs.org> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="_===492038====davinci.ethosmedia.com===_" X-Archive-Number: 200110/357 X-Sequence-Number: 5310 This is a multi-part MIME message --_===492038====davinci.ethosmedia.com===_ Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit 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 --_===492038====davinci.ethosmedia.com===_ Content-Type: text/plain Content-Disposition: attachment; filename="" Content-Transfer-Encoding: base64 DQo= --_===492038====davinci.ethosmedia.com===_ Content-Type: text/plain Content-Disposition: attachment; filename="" Content-Transfer-Encoding: base64 DQo= --_===492038====davinci.ethosmedia.com===_ Content-Type: text/plain Content-Disposition: attachment; filename="" Content-Transfer-Encoding: base64 DQo= --_===492038====davinci.ethosmedia.com===_-- From dmitry@taurussoft.org Fri Oct 26 08:13:40 2001 Received: from ajax2.sovam.com (ajax2.sovam.com [194.67.1.173]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f9Q8DeP21198 for ; Fri, 26 Oct 2001 04:13:40 -0400 (EDT) (envelope-from dmitry@taurussoft.org) Received: from pm14-a29.dial.sovam.com ([195.218.132.29]:4569 "HELO hq.taurussoft.org" ident: "TIMEDOUT2" whoson: "tttt@online.ru" smtp-auth: TLS-CIPHER: TLS-PEER: ) by ajax2.sovam.com with SMTP id convert rfc822-to-8bit; Fri, 26 Oct 2001 12:13:15 +0400 Received: (qmail 21470 invoked from network); 26 Oct 2001 06:58:26 -0000 Received: from flame-in-night.hq.taurussoft.org (192.168.107.1) by kitezh.hq.taurussoft.org with SMTP; 26 Oct 2001 06:58:26 -0000 Received: (qmail 1652 invoked from network); 26 Oct 2001 06:58:06 -0000 Received: from localhost (127.0.0.1) by localhost with SMTP; 26 Oct 2001 06:58:06 -0000 Subject: Re: GUID in postgres From: "Dmitry G. Mastrukov" =?koi8-r?Q?=E4=CD=C9=D4=D2=C9=CA_?= =?koi8-r?Q?=E7=C5=CE=CE=C1=C4=D8=C5=D7=C9=DE_?= =?koi8-r?Q?=ED=C1=D3=D4=D2=C0=CB=CF=D7?= To: Dinesh Parikh Cc: pgsql-sql@postgresql.org In-Reply-To: <002701c15c50$8e6dbca0$f005a8c0@dinesh> References: <002701c15c50$8e6dbca0$f005a8c0@dinesh> Content-Type: text/plain; charset=KOI8-R Content-Transfer-Encoding: 8BIT X-Mailer: Evolution/0.15 (Preview Release) Message-Id: <1004078745.1363.5.camel@flame-in-night> Mime-Version: 1.0 X-Mailer: Evolution/0.15 (Preview Release) Date: 26 Oct 2001 10:57:36 +0400 X-Archive-Number: 200110/368 X-Sequence-Number: 5321 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 From postgresql_sql@kaiserdigital.com Fri Oct 26 17:14:10 2001 Received: from morpheus.kaiserdigital.com (82.31.novustelecom.net [64.69.82.31]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f9QHE9P59794 for ; Fri, 26 Oct 2001 13:14:10 -0400 (EDT) (envelope-from bkejser@kaiserdigital.com) Received: from mouse (mouse.kaiserdigital.com [192.168.0.12]) by morpheus.kaiserdigital.com with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2650.21) id VSGWD2S3; Fri, 26 Oct 2001 10:14:06 -0700 From: To: Subject: Re: GUID in postgres Date: Fri, 26 Oct 2001 10:14:19 -0700 Message-ID: <003501c15e41$a643a220$0c00a8c0@mouse> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.2616 Importance: Normal In-Reply-To: <1004078745.1363.5.camel@flame-in-night> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 X-Archive-Number: 200110/373 X-Sequence-Number: 5325 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 From josh@agliodbs.com Fri Oct 26 18:31:18 2001 Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f9QIVIP77144 for ; Fri, 26 Oct 2001 14:31:18 -0400 (EDT) (envelope-from josh@agliodbs.com) Received: from [209.10.40.250] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.4.7) with HTTP id 492905; Fri, 26 Oct 2001 11:31:16 -0700 From: "Josh Berkus" Subject: Re: GUID in postgres To: , X-Mailer: CommuniGate Pro Web Mailer v.3.4.7 Date: Fri, 26 Oct 2001 11:31:16 -0700 Message-ID: In-Reply-To: <003501c15e41$a643a220$0c00a8c0@mouse> MIME-Version: 1.0 Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit X-Archive-Number: 200110/375 X-Sequence-Number: 5327 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 From postgresql_sql@kaiserdigital.com Fri Oct 26 19:04:02 2001 Received: from morpheus.kaiserdigital.com (82.31.novustelecom.net [64.69.82.31]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f9QJ42P87183 for ; Fri, 26 Oct 2001 15:04:02 -0400 (EDT) (envelope-from bkejser@kaiserdigital.com) Received: from mouse (mouse.kaiserdigital.com [192.168.0.12]) by morpheus.kaiserdigital.com with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2650.21) id VSGWD2T3; Fri, 26 Oct 2001 12:03:58 -0700 From: To: Subject: Re: GUID in postgres Date: Fri, 26 Oct 2001 12:04:12 -0700 Message-ID: <004501c15e50$ffd4df20$0c00a8c0@mouse> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.2616 Importance: Normal In-Reply-To: X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 X-Archive-Number: 200110/376 X-Sequence-Number: 5328 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 From josh@agliodbs.com Fri Oct 26 20:00:07 2001 Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f9QK07P99222 for ; Fri, 26 Oct 2001 16:00:07 -0400 (EDT) (envelope-from josh@agliodbs.com) Received: from [209.10.40.250] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.4.7) with HTTP id 492993; Fri, 26 Oct 2001 13:00:04 -0700 From: "Josh Berkus" Subject: Re: GUID in postgres To: , X-Mailer: CommuniGate Pro Web Mailer v.3.4.7 Date: Fri, 26 Oct 2001 13:00:04 -0700 Message-ID: In-Reply-To: <004501c15e50$ffd4df20$0c00a8c0@mouse> MIME-Version: 1.0 Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit X-Archive-Number: 200110/377 X-Sequence-Number: 5329 "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 From dmitry@taurussoft.org Fri Oct 26 23:32:20 2001 Received: from ajax2.sovam.com (ajax2.sovam.com [194.67.1.173]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f9QNWKP41902 for ; Fri, 26 Oct 2001 19:32:20 -0400 (EDT) (envelope-from dmitry@taurussoft.org) Received: from ts18-a384.dial.sovam.com ([195.239.6.130]:4704 "HELO hq.taurussoft.org" ident: "TIMEDOUT2" whoson: "tttt@online.ru" smtp-auth: TLS-CIPHER: TLS-PEER: ) by ajax2.sovam.com with SMTP id convert rfc822-to-8bit; Sat, 27 Oct 2001 03:32:06 +0400 Received: (qmail 22831 invoked from network); 26 Oct 2001 23:31:26 -0000 Received: from flame-in-night.hq.taurussoft.org (192.168.107.1) by kitezh.hq.taurussoft.org with SMTP; 26 Oct 2001 23:31:26 -0000 Received: (qmail 1074 invoked from network); 26 Oct 2001 23:31:34 -0000 Received: from localhost (127.0.0.1) by localhost with SMTP; 26 Oct 2001 23:31:34 -0000 Subject: Re: GUID in postgres From: "Dmitry G. Mastrukov" =?koi8-r?Q?=E4=CD=C9=D4=D2=C9=CA_?= =?koi8-r?Q?=E7=C5=CE=CE=C1=C4=D8=C5=D7=C9=DE_?= =?koi8-r?Q?=ED=C1=D3=D4=D2=C0=CB=CF=D7?= To: postgresql_sql@kaiserdigital.com Cc: pgsql-sql@postgresql.org In-Reply-To: <003501c15e41$a643a220$0c00a8c0@mouse> References: <003501c15e41$a643a220$0c00a8c0@mouse> Content-Type: text/plain; charset=KOI8-R Content-Transfer-Encoding: 8BIT X-Mailer: Evolution/0.15 (Preview Release) Date: 27 Oct 2001 03:31:04 +0400 Message-Id: <1004139094.781.21.camel@flame-in-night> Mime-Version: 1.0 X-Archive-Number: 200110/378 X-Sequence-Number: 5330 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