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

Re: 8.1, OID's and plpgsql

From: Richard Huxton <dev(at)archonet(dot)com>
To: "Uwe C(dot) Schroeder" <uwe(at)oss4u(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: 8.1, OID's and plpgsql
Date: 2005-12-01 17:25:22
Message-ID: 438F3202.6070806@archonet.com (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-hackers
Uwe C. Schroeder 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.

There we disagree. That's what the primary-key is for. Of course that 
means we want a last_primary_key_from_insert() system-function.

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

Then you have a bad primary key - the timestamps add nothing to the 
serial (or vice-versa).

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

Yes - add a SERIAL column with UNIQUE and fetch on that if you really 
need to. This effectively gives you your OID back.

-- 
   Richard Huxton
   Archonet Ltd

In response to

pgsql-hackers by date

Next:From: Qingqing ZhouDate: 2005-12-01 17:32:12
Subject: Re: generalizing the planner knobs
Previous:From: Andrew DunstanDate: 2005-12-01 17:21:46
Subject: Re: [HACKERS] Upcoming PG re-releases

pgsql-general by date

Next:From: Terry Lee TuckerDate: 2005-12-01 17:32:02
Subject: Function returning SETOF
Previous:From: Medora SchauerDate: 2005-12-01 17:18:14
Subject: Re: fatal error in pg.log

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