Re: 8.1, OID's and plpgsql - Mailing list pgsql-general

On Thursday 01 December 2005 10:24, Jaime Casanova wrote:
> On 12/1/05, Uwe C. Schroeder <uwe@oss4u.com> wrote:
> > Hi everyone,
> >
> > in 8.1 by default tables have no OID's anymore. Since OID's are 4 byte
> > it's probably a good idea to discourage the use of them (they produced a
> > lot of trouble in the past anyways, particularly with backup/restores
> > etc)
> >
> > Now there's the issue with stored procs. A usual construct would be to
> > ...
> > ...
> > INSERT xxxxxx;
> > GET DIAGNOSTICS lastoid=RESULT_OID;
> > SELECT .... oid=lastoid;
> > ....
> > ....
> >
> > Is there anything one could sanely replace this construct with?
> > I personally don't think that using the full primary key is really a good
> > option. Say you have a 3 column primary key - one being a "serial", the
> > others for example being timestamps, one of them generated with "default"
> > options. In order to retrieve the record I just inserted (where I don't
> > know the "serial" value or the timestamp) I'd have to
> >
> > 1) store the "nextval" of the sequence into a variable
> > 2) generate the timestamp and store it to a variable
> > 3) generate the full insert statement and retain the other values of the
> > primary key
> > 4) issue a select to get the record.
> >
> > Personally I think this adds unneccessary overhead. IMHO this diminishes
> > the use of defaults and sequences unless there is some easier way to
> > retrieve the last record. I must be missing something here - am I ?
> >
> >        UC
>
> If you are using a SERIAL in your PK, why you need the other two
> fields? The serial will undoubtly identify a record?
>
> you just retrieve the current value you inserted with currval
>

No it doesn't. the serial identifies the record, the timestamp identifies the
version/time-validity of the record.
If a primary key needs to be something as simple as a serial then we could
just keep the OID's as well and pump them up to 32 bytes.

curval() doesn't do it, since that will only identify a group of records since
my PK is not just a simple int4.

sample:

create table xxx (
  id serial,
  field varchar,
 ...
 ...
 valid_from timestamptz
)

PK is id,valid_from
There may be several records with the same id but different valid_from dates.
I'm storing a full timestamp, but the application only uses the date part -
the timestamp is just to correct for timezones.

From the application logic a record is considered valid until a record with a
newer valid_from is found. From that point on the records are referenced
depending on several legal factors (this is commercial insurance, lots of
lawyers and state/fed regulations)


I guess I either stick to the OID's which work fine, or I just have to store
the whole PK in variables and forget about defaults.

Why not have something like the rowid in oracle?


    UC