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.
create table xxx (
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?
In response to
pgsql-hackers by date
|Next:||From: Pollard, Mike||Date: 2005-12-02 03:21:23|
|Subject: Re: generalizing the planner knobs|
|Previous:||From: Tom Lane||Date: 2005-12-02 03:14:12|
|Subject: Re: [HACKERS] Should libedit be preferred to libreadline? |
pgsql-general by date
|Next:||From: Tom Lane||Date: 2005-12-02 03:19:01|
|Subject: Re: postmaster / resolv.conf / dns problem |
|Previous:||From: Rodrigo Gonzalez||Date: 2005-12-02 03:08:33|
|Subject: Re: Slow COUNT|