On 12/1/05, Uwe C. Schroeder <uwe@xxxxxxxxx> wrote: > On Thursday 01 December 2005 10:24, Jaime Casanova wrote: > > On 12/1/05, Uwe C. Schroeder <uwe@xxxxxxxxx> 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 ;)
