Eddy Macnaghten zei: > I think you are correct in not using OIDs, as, firstly, as you point out > they are optional, also that they are not neccessarily unique. >
I'm sorry Eddy, but you most be mistaken: Every row in POSTGRESQL is assigned a unique, normally invisible number called an object identification number (OID). When the software is initialized with initdb , 12.1 a counter is created and set to approximately seventeen-thousand. The counter is used to uniquely number every row. Although databases may be created and destroyed, the counter continues to increase. http://www.postgresql.org/docs/aw_pgsql_book/node71.html > The use of sequences is an idea, however, why the complication? Why not > simply use a sequence called "mytable_sequence", or "mytable_id" where > "mytable" is the name of the table? (or some other such standard). > Because a lot of the time we query databases we did not create our selfs, we were looking for a general way, to handle it. > The other thing to be aware of is if a large number of people are > writing to the database concurrently it can go wrong (any method). That > is if you insert a record (using nextval for the sequence), then someone > else quickly inserts a row too before you have a chance to get the > sequence number at the next statement then the sequence number you get > will be wrong (it would be of the new one, not yours). This would be > the case regardless of how the records are committed. > I thought that was the whole idea of sequences, each call to nextval () will actually give you a unique number for that sequence (unless ofcourse it it wraps..) > A way around this is to create a function like.... > > -------------------------------------------------------------------- > > create function mytable_insert (varchar(50), varchar(50)) returns > integer as ' > declare > > wseq integer; > > begin > > select nextval(''mytable_seq'') into wseq; > > insert into mytable(id, a, b) > values (wseq, $1, $2); > > return wseq; > > end' language 'plpgsql'; > > -------------------------------------------------------- > > Then, executing select mytable_insert('xx', 'yy'); > That just uses a plpgsql function to do what I suggested (other then you need to know the sequence name) > Will insert the record and return the inserted sequence number > regardless as to what is happening concurrently. > > Ohh, now I know what you mean, no we don't write concurrently, but a nextval should be unique for that sequence anyway (otherwise, why even have them ?). > > On Sat, 2004-10-23 at 13:52, Leen Besselink wrote: >> Hi pgsql-general, >> >> (all examples are pseudo-code) >> >> We really love PostgreSQL, it's getting better and better, there is just >> one thing, something that has always led to some dislike: OID's >> >> I understand why they did it and all, but still. >> >> To make life easier, it's always good to find a general way of doing >> things. >> >> But sometimes it just takes a lot more time and effort to find something >> you feel even mildly comvertable with. >> >> This is one of those times. >> >> Some people use this way of getting the real insertID: >> >> insert into whatever (text) values ('something'); >> >> oid = insertID (); >> >> select id from whatever where whatever.oid = oid; >> >> you get the general idea. >> >> But OID's are optional now... so, not terrible great. >> >> Or with the use of PG's nextval () (which is the preferred/intended >> PostgreSQL-way and I agree): >> >> id = nextval ("whatever_id_seq"); >> insert into whatever (id, text) values (id, 'something'); >> >> Something that works always... better, but you need to know the name of >> the sequence, bummer. >> >> So we constructed this query: >> >> SELECT >> pg_attrdef.adsrc >> FROM >> pg_attrdef, >> pg_class, >> pg_attribute >> WHERE >> pg_attrdef.adnum = pg_attribute.attnum >> AND pg_attrdef.adrelid = pg_class.oid >> AND pg_attribute.attrelid = pg_class.oid >> AND pg_attribute.attname = 'id' >> AND pg_class.relname = 'whatever' >> >> (pg_class is a table that holds for instance table-names, etc., >> pg_attribute + pg_attrdef are table's with field-information) >> >> it will result in the default-value of a field of a table..., which >> means >> you get something like this: >> >> nextval('whatever_id_seq'::text) >> >> so, now you have the sequence..., or atleast a way to get to the >> nextval. >> >> All you have to do is this: >> >> SELECT nextval('whatever_id_seq'::text); >> >> done. >> >> So, now all you have to know is: >> - table >> - field with ID + default-value >> - insert query >> >> Well, maybe that's crazy too, but atleast it's something that'll work. >> >> Probably not the best way, but it's a way. >> >> We're just wondering what people think about such an approach. >> >> Have a nice day, >> Lennie. >> >> PS This has been tested with: >> - 6.5.3 (Debian Linux Package) >> - 8.0 Beta 3 Win32 (msi-install) >> >> _____________________________________ >> New things are always on the horizon. >> >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 9: the planner will ignore your desire to choose an index scan if >> your >> joining column's datatypes do not match > -- > Edward A. Macnaghten > http://www.edlsystems.com > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > > _____________________________________ New things are always on the horizon. ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
