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

On 12/1/05, Uwe C. Schroeder <uwe@oss4u.com> wrote:
> 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.

you don't need valid_from to be part of the PK, just the serial...

> 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.
>

No. because tables with OIDs are not the default anymore and is not
recomended to use OIDs as PK

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

currval() identifies the last value you inserted... that's one of the
reason to prefer SERIAL over OIDs... an API for manage them...

> 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.
>

obviously you are using wrong the datatype serial if you let the
serial column insert always its default then there won't be several
record with the same id

> 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)
>

and? you still don't need valid_from as part of the PK if id is a serial...

i think what you really want is to make id an integer and then let
valid_from as part of PK...

and make a select to retrieve the valid one

SELECT * FROM xxx WHERE id = ??? ORDER BY valid_from DESC LIMIT 1

>
> 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
>




--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)