Re: oid's and primary keys on insert - Mailing list pgsql-general

On 10 Aug 2002, Rob Brown-Bayliss wrote:

> On Sat, 2002-08-10 at 09:09, Nigel J. Andrews wrote:
> > It's sad to reply to one's own message but...
>
> I do it daily :o(
>
> > I'm equally not sure how you would get out of without knowing the data. The
> > call to a function to combine two data values to make a unique value would seem
> > to me to require that you know the data.
>
> Heres what I am doing (sort of):
>
> --
> -- TOC Entry ID 71 (OID 2429711)
> --
> -- Name: "get_loc_key" () Type: FUNCTION Owner: postgres
> --
>
> CREATE FUNCTION "get_loc_key" () RETURNS integer AS 'SELECT int_value
> FROM local_data WHERE descriptor=''sys_locations_key''
> ' LANGUAGE 'sql';
>
> --
> -- TOC Entry ID 73 (OID 2429713)
> --
> -- Name: "set_unique_key" () Type: FUNCTION Owner: rob
> --
>
> CREATE FUNCTION "set_unique_key" () RETURNS text AS '
> DECLARE
>     sequence_number    text;
>     location_number    text;
>     retval text;
> BEGIN
>     location_number := to_char(get_loc_key(),''999MI'');
>     location_number := trim(both '' '' from location_number);
>     sequence_number := to_char(nextval(''location_seq''),''999999999MI'');
>     sequence_number := trim(both '' '' from sequence_number);
>     retval := location_number || ''-'' || sequence_number;
>     RETURN retval;
> END;
> ' LANGUAGE 'plpgsql';
>
> and set_unique_key is the default value for the primary key field.  I
> could I guess not have it set as default and first call teh function
> andthen insert it to the filed in the insert statement, but then it's
> open to forgetfullness.  By default then it cant be left out.

Okay, I get it.

Now to my mind this is where you are going wrong. I think the generally
accepted method of do this sort of thing with sequences is either:

1. SELECT nextval('seqname');
2. Remember the result in variable say currid
3. INSERT INTO mytable (idcol, ...) VALUES (<value from currid>, ...)
4. Use value from variable currid whereever else it is that you require it

or

1. INSERT INTO mytable (idcol, ...) VALUES (<value from currid>, ...)
2. SELECT curval('seqname');
3. Remember the result in variable say currid
4. Use value from variable currid whereever else it is that you require it

In your case you would use set_unique_key() instead of the nextval(...) in the
first version.

Yes, you have to remember to do the insert in two stages and you could set the
set_unique_key() function as the default on your primary key column or as you
say you could not do that and rely on a NOT NULL constraint to catch
incorrectly performed inserts. Even with the default setting you have you still
have to make sure the application is doing inserts correctly, i.e. not
specifing the primary key data or using the correct function call, so not using
a default setting should be no real pain.

The advantage of trying it your way and using the returned OID from the INSERT
statement is that you do have only the one statement to run in the
backend. My view is that the extra little bit of work required doing this the
two step way is worth it becuase there's no trap waiting should you get the
situation where the OID information is no longer available.

>
> > However, I would wary of using the
> > return information from an insert unless I knew with certainty that there was
>
> Is it possible for teh oid returned to be the oid from another insert?
> I didn't know that.  If thats the case then none of this matters :o)

No, but with an ON INSERT rule you will find that there is no useful
information returned from your insert other than the standard success/failure
indicators. There has been discussion of this on the list and one of the
arguments for this behaviour is that a rule can be doing alsort of stuff, like
inserting to other tables so items such as number of rows inserted is
meaningless in a rule situation. I myself use an ON INSERT rule to take insert
data in a view's format and insert it into the appropiate base tables, so for a
single 'row inserted into my view' there is really anything between 1 and 5
rows inserted into tables. To top it off there is not even an OID column
available from a view, unless one is included from an underlying table. The
point being that the OID is _not_ the primary key and it's just luck that
some objects in a database have a column that may be unique and therefore may
be used to look up a specific row.

System tables are a slightly different beasts since they by definition are part
of the system and therefore if they depend on OIDs for associations between
tables then they will always use OIDs, until they don't, if you see what I
mean.


--
Nigel J. Andrews
Director

---
Logictree Systems Limited
Computer Consultants