Re: oid's and primary keys on insert

On 10 Aug 2002, Rob Brown-Bayliss wrote:

> The primary key on these tables are
> inserted via a trigger, and are in the form of "location-sequence" so
> that this 46-23456 represents the primary key for location 46, sequence
> 23456 on that machine.
>
> I cant just use a sequence at each site otherwise I could end up with 46
> identical primary keys (23456) refering to different data.

Your problem appears to be confusion about what your primary key is. If
"46-23456" uniquely identifies a particular datum, and "23456" does not,
you should never use "23456" as a primary key, but always "46-23456".

You can do this either by combining the two into one column in the
table, and creating a function to concatenate the site code and ID when
doing an insert, or by having the codes in two separate columns and
making your primary key the concatenation of the two columns.

Note that object IDs being present or not is really irrelevant; object
IDs don't do anything that you can't do explicitly with an integer
column and a sequence.

> Doesnt' PostgreSQL use the OID's for it's own internal use? So that
> every item has a unique identifier for postgresql to play with, not
> necessarily the user)

It does for some things, yes. But not for every table.

That's a good reason to remove them; it's an extra four bytes of
data that ought to be made explicit if the data are being used, or
just dropped if the data are not being used.

cjs
--
Curt Sampson <cjs(at)cynic(dot)net> +81 90 7737 2974 http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light. --XTC