Skip site navigation (1) Skip section navigation (2)

Re: 8.1, OID's and plpgsql

From: "Uwe C(dot) Schroeder" <uwe(at)oss4u(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: 8.1, OID's and plpgsql
Date: 2005-12-02 03:18:10
Message-ID: 200512011918.10810.uwe@oss4u.com (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-hackers
On Thursday 01 December 2005 10:24, Jaime Casanova wrote:
> On 12/1/05, Uwe C. Schroeder <uwe(at)oss4u(dot)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


In response to

Responses

pgsql-hackers by date

Next:From: Pollard, MikeDate: 2005-12-02 03:21:23
Subject: Re: generalizing the planner knobs
Previous:From: Tom LaneDate: 2005-12-02 03:14:12
Subject: Re: [HACKERS] Should libedit be preferred to libreadline?

pgsql-general by date

Next:From: Tom LaneDate: 2005-12-02 03:19:01
Subject: Re: postmaster / resolv.conf / dns problem
Previous:From: Rodrigo GonzalezDate: 2005-12-02 03:08:33
Subject: Re: Slow COUNT

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group