Re: [GENERAL] OID as Primary Key
Jonas Bengtsson wrote: > > Can't you do a dump with the oid's? > > But when I want to know the primary key of the inserted row > I have to do an extra select query. If I use oid I just use > pg_getlastoid() in php. > And it is redundant data to store another integer. > > Comments? I have often used OID as primary key, usually for the reason's you cite. IMHO, in the long run, the two major extra worries are often not worth it: 1) You must explicitly dump and restore OIDs all the time 2) You loose generic SQL compatibility On the other hand, we've never had a failure from theses causes. In short, using OIDs seems to work well enough for us, but the downsides in practice have been larger than we expected, and the upsides smaller. In our case, the extra serial would be a small part of the overall data, so I kind of wish I had not used OIDs. Just my $0.02 worth. -- Karl ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] OID as Primary Key
On Thu, 22 Mar 2001, Jonas Bengtsson wrote: > Hello, > I have problems with using OID as PK. I have created a > UNIQUE INDEX on the oid on a relation called CourseEvents. > The relation that is supposed to have a FK to CourseEvents > is CourseEventsForums and it has a field called ceid which > has oid as dtatype. > But when I try to create a FK to that table I get following > error: > --- > PostgreSQL said: ERROR: UNIQUE constraint matching given > keys for referenced table "courseevents" not found > Your query: > ALTER TABLE CourseEventForums ADD CONSTRAINT > RefCourseEvents43 > FOREIGN KEY (ceid) > REFERENCES CourseEvents(oid) > --- > What is wrong? The message is a bit misleading. Oid isn't a "user" column, and currently you can only create references to user columns. Given the issues involved with oid (since it's system wide it rolls over faster than a serial, you have to make sure to dump with oids, the fact that you have much less control over its value if you need to do something), I'm not 100% sure that's a bad thing, although IIRC references to oid are on the todo list. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
RE: Re: [GENERAL] OID as Primary Key
Title: RE: Re: [GENERAL] OID as Primary Key Isn’t is better to use nextval(). If there are many simultaneous users vurrval might give the value of another insert. /Jonas B -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Michael Ansley Sent: Thursday, March 22, 2001 5:50 PM To: [EMAIL PROTECTED] Subject: RE: Re: [GENERAL] OID as Primary Key And you can get the last SERIAL inserted using currval. MikeA >> -Original Message- >> From: Brent R. Matzelle [mailto:[EMAIL PROTECTED]] >> Sent: 22 March 2001 16:36 >> To: [EMAIL PROTECTED] >> Subject: Re: Re: [GENERAL] OID as Primary Key >> >> >> --- Jonas Bengtsson <[EMAIL PROTECTED]> wrote: >> > Can't you do a dump with the oid's? >> >> Yes, but I would never suggest it. >> >> > But when I want to know the primary key of the inserted row >> > I have to do an extra select query. If I use oid I just use >> > pg_getlastoid() in php. >> > And it is redundant data to store another integer. >> >> You should not use oid's as primary keys just because you want >> to save a little HD space. >> >> I contributed a some code on PHPBuilder for a function that >> returns the insert id of a SERIAL primary key. You can view it >> here: >> http://www.phpbuilder.com/snippet/download.php?type=snippet&id=304 >> . It works well for me and it does not kludge my code like >> using the oid does. >> >> Brent >> >> __ >> Do You Yahoo!? >> Get email at your own domain with Yahoo! Mail. >> http://personal.mail.yahoo.com/ >> >> ---(end of >> broadcast)--- >> TIP 1: subscribe and unsubscribe commands go to >> [EMAIL PROTECTED] >> _ This e-mail and any attachments are confidential and may also be privileged and/or copyright material of Intec Telecom Systems PLC (or its affiliated companies). If you are not an intended or authorised recipient of this e-mail or have received it in error, please delete it immediately and notify the sender by e-mail. In such a case, reading, reproducing, printing or further dissemination of this e-mail is strictly prohibited and may be unlawful. Intec Telecom Systems PLC. does not represent or warrant that an attachment hereto is free from computer viruses or other defects. The opinions expressed in this e-mail and any attachments may be those of the author and are not necessarily those of Intec Telecom Systems PLC. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. __
RE: Re: [GENERAL] OID as Primary Key
Title: RE: Re: [GENERAL] OID as Primary Key And you can get the last SERIAL inserted using currval. MikeA >> -Original Message- >> From: Brent R. Matzelle [mailto:[EMAIL PROTECTED]] >> Sent: 22 March 2001 16:36 >> To: [EMAIL PROTECTED] >> Subject: Re: Re: [GENERAL] OID as Primary Key >> >> >> --- Jonas Bengtsson <[EMAIL PROTECTED]> wrote: >> > Can't you do a dump with the oid's? >> >> Yes, but I would never suggest it. >> >> > But when I want to know the primary key of the inserted row >> > I have to do an extra select query. If I use oid I just use >> > pg_getlastoid() in php. >> > And it is redundant data to store another integer. >> >> You should not use oid's as primary keys just because you want >> to save a little HD space. >> >> I contributed a some code on PHPBuilder for a function that >> returns the insert id of a SERIAL primary key. You can view it >> here: >> http://www.phpbuilder.com/snippet/download.php?type=snippet&id=304 >> . It works well for me and it does not kludge my code like >> using the oid does. >> >> Brent >> >> __ >> Do You Yahoo!? >> Get email at your own domain with Yahoo! Mail. >> http://personal.mail.yahoo.com/ >> >> ---(end of >> broadcast)--- >> TIP 1: subscribe and unsubscribe commands go to >> [EMAIL PROTECTED] >> _ This e-mail and any attachments are confidential and may also be privileged and/or copyright material of Intec Telecom Systems PLC (or its affiliated companies). If you are not an intended or authorised recipient of this e-mail or have received it in error, please delete it immediately and notify the sender by e-mail. In such a case, reading, reproducing, printing or further dissemination of this e-mail is strictly prohibited and may be unlawful. Intec Telecom Systems PLC. does not represent or warrant that an attachment hereto is free from computer viruses or other defects. The opinions expressed in this e-mail and any attachments may be those of the author and are not necessarily those of Intec Telecom Systems PLC. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. __
Re: Re: [GENERAL] OID as Primary Key
--- Jonas Bengtsson <[EMAIL PROTECTED]> wrote: > Can't you do a dump with the oid's? Yes, but I would never suggest it. > But when I want to know the primary key of the inserted row > I have to do an extra select query. If I use oid I just use > pg_getlastoid() in php. > And it is redundant data to store another integer. You should not use oid's as primary keys just because you want to save a little HD space. I contributed a some code on PHPBuilder for a function that returns the insert id of a SERIAL primary key. You can view it here: http://www.phpbuilder.com/snippet/download.php?type=snippet&id=304 . It works well for me and it does not kludge my code like using the oid does. Brent __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: Re: [GENERAL] OID as Primary Key
Can't you do a dump with the oid's? But when I want to know the primary key of the inserted row I have to do an extra select query. If I use oid I just use pg_getlastoid() in php. And it is redundant data to store another integer. Comments? /Jonas B > Jonas Bengtsson <[EMAIL PROTECTED]> writes: > > I have problems with using OID as PK. > > This strikes me as a fairly bad idea in any case. What happens if you > must dump and reload your database? Better to use a SERIAL column as > primary key. > > regards, tom lane > Mvh, Jonas Bengtsson ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] OID as Primary Key
Jonas Bengtsson <[EMAIL PROTECTED]> writes: > I have problems with using OID as PK. This strikes me as a fairly bad idea in any case. What happens if you must dump and reload your database? Better to use a SERIAL column as primary key. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
