X-Original-To: pgsql-performance@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id D3E864758F1 for ; Wed, 26 Feb 2003 09:01:53 -0500 (EST) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with SMTP id 59748-01 for ; Wed, 26 Feb 2003 09:01:43 -0500 (EST) Received: from anchor-post-31.mail.demon.net (anchor-post-31.mail.demon.net [194.217.242.89]) by postgresql.org (Postfix) with ESMTP id 37D854758BD for ; Wed, 26 Feb 2003 08:58:53 -0500 (EST) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-31.mail.demon.net with esmtp (Exim 3.35 #1) id 18o25I-000I2p-0V; Wed, 26 Feb 2003 13:58:56 +0000 Received: from localhost (localhost.localdomain [127.0.0.1]) by mainbox.archonet.com (Postfix) with ESMTP id 73AFE17065; Wed, 26 Feb 2003 13:58:55 +0000 (GMT) Received: from client.archonet.com (client.archonet.com [192.168.1.16]) by mainbox.archonet.com (Postfix) with ESMTP id B7A84162FA; Wed, 26 Feb 2003 13:58:54 +0000 (GMT) Content-Type: text/plain; charset="iso-8859-1" From: Richard Huxton Organization: Archonet Ltd To: daniel alvarez , pgsql-performance@postgresql.org Subject: Re: OIDs as keys Date: Wed, 26 Feb 2003 13:58:53 +0000 User-Agent: KMail/1.4.3 References: <1859.1046264679@www36.gmx.net> In-Reply-To: <1859.1046264679@www36.gmx.net> MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200302261358.53730.dev@archonet.com> X-Virus-Scanned: by AMaViS snapshot-20020531 X-Virus-Scanned: by amavisd-new X-Archive-Number: 200302/210 X-Sequence-Number: 1266 On Wednesday 26 Feb 2003 1:04 pm, daniel alvarez wrote: > In some older mails in the archive I found rumors about difficulcies that > might > occur when OIDs are used as an integral part of a data model. > > I am considering the option of placing an index on the already existing o= id > and using > it as the primary key for all tables (saves some space and a sequence > lookup). This > includes saving the oid in foreign keys (virtual ones, not actually > declared references). > I read that using OID in keys is generally a bad idea. Is it really? Why > exactly? OIDs are not even guaranteed to be there any more - you can create a table= =20 WITHOUT OIDs if you want to save some space. If you want a numeric primary= =20 key, I'd recommend int4/int8 attached to a sequence - it's much clearer=20 what's going on then. > Are there any disadvantages as to reliability or performance apart from > accidentally > forgetting to use the -o option with pg_dump? If so, please give details. > > I felt especially worried by a postgres developer's statement in another > archived mail: > "As far as I know, there is no reason oid's have to be unique, especially > if they are in different tables." > (http://archives.postgresql.org/pgsql-hackers/1998-12/msg00570.php) > > How unique are oids as of version 7.3 of postgres ? OIDs are unique per object (table) I believe, no more so. See chapter 5.10 = of=20 the user guide for details. They are used to identify system objects and so= =20 the fact that a function and a table could both have the same OID should=20 cause no problems. > Is it planned to keep oids semantically the same in future releases of > postgres? Couldn't say - don't see why not. > Will the oid type be extended so that oids can be larger than 4 bytes (if > this is still > correct for 7.3) and do not rotate in large systems? Strikes me as unlikely, though I'm not a developer. Look into 8-byte=20 serial/sequences. --=20 Richard Huxton