From johnny@halfahead.dk Wed Oct 17 13:27:52 2001 Received: from tina.andersenit.dk (tina.andersenit.dk [193.89.137.127] (may be forged)) by postgresql.org (8.11.3/8.11.4) with ESMTP id f9HDRpP99074 for ; Wed, 17 Oct 2001 09:27:52 -0400 (EDT) (envelope-from johnny@halfahead.dk) Received: from ([130.226.84.71]) by tina.andersenit.dk (Merak 4.00.40) with SMTP id BHB36607 for ; Wed, 17 Oct 2001 15:27:17 +0200 Message-ID: <200110171527170784.01357C60@mail.halfahead.dk> X-Mailer: Calypso Version 3.30.00.00 (4) Date: Wed, 17 Oct 2001 15:27:17 +0200 Reply-To: johnny@halfahead.dk From: =?ISO-8859-1?Q?=22Johnny_J=F8rgensen=22?= To: pgsql-php@postgresql.org Subject: last OID and connections? Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="=====_10033252379859=_" X-Archive-Number: 200110/73 X-Sequence-Number: 626 --=====_10033252379859=_ Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: quoted-printable Once I get a current project to production state, I'm going to get a heap o= f users performing updates simultaneously through the same update.php, and = that raises a few questions: 1. will pg_getlastoid() return the last oid inserted *by this user*, *on th= is page*, *on this connection*, or is there a possibility for them to get m= ixed up, because the database gets a connection from the same user (php), a= nd reuses connections or something? e.g. If a user changes his profile, and an administrator changes some other= row at the same time? 2. I find oid's pretty clever, but are they good for unique keys? It seems,= foreign key constraints won't use oid's in the current release, should i u= se serials instead? thanks, Mvh, Johnny J=F8rgensen johnny@halfahead.dk +45 6315 7328 --=====_10033252379859=_ Content-Type: text/html; charset="us-ascii"
Once I get a current project to production state, I'm going to get a heap of users performing updates simultaneously through the same update.php, and that raises a few questions:
 
1. will pg_getlastoid() return the last oid inserted *by this user*, *on this page*, *on this connection*, or is there a possibility for them to get mixed up, because the database gets a connection from the same user (php), and reuses connections or something?
 
e.g. If a user changes his profile, and an administrator changes some other row at the same time?
 
2. I find oid's pretty clever, but are they good for unique keys? It seems, foreign key constraints won't use oid's in the current release, should i use serials instead?
 
thanks,
 
 

Mvh,
 
Johnny Jørgensen
 
johnny@halfahead.dk
+45 6315 7328

--=====_10033252379859=_-- From josh@agliodbs.com Wed Oct 17 15:51:45 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 f9HFpiP57093 for ; Wed, 17 Oct 2001 11:51:45 -0400 (EDT) (envelope-from josh@agliodbs.com) Received: from [209.10.40.250] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.4.1) with HTTP id 149645; Wed, 17 Oct 2001 08:51:39 -0700 From: "Josh Berkus" Subject: Re: last OID and connections? To: johnny@halfahead.dk, pgsql-php@postgresql.org X-Mailer: CommuniGate Pro Web Mailer v.3.4.1 Date: Wed, 17 Oct 2001 08:51:39 -0700 Message-ID: In-Reply-To: <200110171527170784.01357C60@mail.halfahead.dk> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="_===149645====davinci.ethosmedia.com===_" X-Archive-Number: 200110/74 X-Sequence-Number: 627 This is a multi-part MIME message --_===149645====davinci.ethosmedia.com===_ Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit Johnny, > Once I get a current project to production state, I'm going to get a > heap of users performing updates simultaneously through the same > update.php, and that raises a few questions: > > 1. will pg_getlastoid() return the last oid inserted *by this user*, > *on this page*, *on this connection*, or is there a possibility for > them to get mixed up, because the database gets a connection from the > same user (php), and reuses connections or something? Use a more sophisticated scheme. I understand that the current OID scheme is eventually being phased out of future Postgres releases (with backwards compatibility, of course!) and replaced with something else because of problems with very large/very old databases. Or so the rumor mill goes. I handle this issue by wrapping all adds and edits in functions; each function returns the primary key of the record as its result. NEXTVAL and CURVAL are your friends! > 2. I find oid's pretty clever, but are they good for unique keys? It > seems, foreign key constraints won't use oid's in the current > release, should i use serials instead? Yes, you should use SERIALs instead. OIDs are for system use only, and will cause problems with backup/restore as well as databases with more than 2 billion objects. -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 --_===149645====davinci.ethosmedia.com===_ Content-Type: text/plain Content-Disposition: attachment; filename="" Content-Transfer-Encoding: base64 DQo= --_===149645====davinci.ethosmedia.com===_ Content-Type: text/plain Content-Disposition: attachment; filename="" Content-Transfer-Encoding: base64 DQo= --_===149645====davinci.ethosmedia.com===_ Content-Type: text/plain Content-Disposition: attachment; filename="" Content-Transfer-Encoding: base64 DQo= --_===149645====davinci.ethosmedia.com===_-- From adam@newsnipple.com Wed Oct 17 19:18:31 2001 Received: from snatch.newsnipple.com ([64.71.188.74]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f9HJIUP38502 for ; Wed, 17 Oct 2001 15:18:31 -0400 (EDT) (envelope-from adam@snatch.newsnipple.com) Received: by snatch.newsnipple.com (Postfix, from userid 100) id 8DB3C4B3AB; Wed, 17 Oct 2001 12:18:23 -0700 (PDT) Date: Wed, 17 Oct 2001 12:18:23 -0700 From: Adam Haberlach To: =?iso-8859-1?Q?Johnny_J=F8rgensen?= Cc: pgsql-php@postgresql.org Subject: Re: last OID and connections? Message-ID: <20011017121823.A8332@newsnipple.com> References: <200110171527170784.01357C60@mail.halfahead.dk> Mime-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: 8bit User-Agent: Mutt/1.2.5i In-Reply-To: <200110171527170784.01357C60@mail.halfahead.dk>; from johnny@halfahead.dk on Wed, Oct 17, 2001 at 03:27:17PM +0200 X-Archive-Number: 200110/75 X-Sequence-Number: 628 On Wed, Oct 17, 2001 at 03:27:17PM +0200, "Johnny Jørgensen" wrote: > Once I get a current project to production state, I'm going to get a heap of users performing updates simultaneously through the same update.php, and that raises a few questions: > > 1. will pg_getlastoid() return the last oid inserted *by this user*, *on this page*, *on this connection*, or is there a possibility for them to get mixed up, because the database gets a connection from the same user (php), and reuses connections or something? If you pass it the result object from your last insert, it should give an oid unique to that insert. I usually do the insert, the immediately read the row back with a select for the pg_lastoid() in order to get the pkey. A little wanky, but it seems to be the way to do it. > e.g. If a user changes his profile, and an administrator changes some other row at the same time? If they are through seperate connections, they should be completely different. > 2. I find oid's pretty clever, but are they good for unique keys? It seems, foreign key constraints won't use oid's in the current release, should i use serials instead? Don't use them for anything persistent except for large objects. Use serials as primary keys. -- Adam Haberlach | Computer science has the unfortunate characteristic adam@newsnipple.com| that many of the pioneers of the field are still | alive and doing research. | -- Adam Rifkin