Thread: OID's
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.
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.
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).
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.
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');
Will insert the record and return the inserted sequence number
regardless as to what is happening concurrently.
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
Eddy Macnaghten <eddy@edlsystems.com> writes: > 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. Not the case. If you use currval(), it will always be the last value the sequence took *in your session*, so it's immune to other sessions inserting at the same time. See the docs. -Doug
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 majordomo@postgresql.org) > > _____________________________________ New things are always on the horizon.
On Sat, Oct 23, 2004 at 14:52:31 +0200,
Leen Besselink <leen@wirehub.nl> wrote:
>
> 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.
In 8.0 (currently in beta) there is a supplied function to return this
string reliably.
On Sat, 23 Oct 2004, Leen Besselink wrote: > 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 Actually it's the book that's mistaken. The counter can roll over, and will on large databases (oids are only 4 bytes).
On Sat, 2004-10-23 at 17:46 +0200, Leen Besselink wrote: > 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 That is dated 2002. It is now possible to create a table without oids, and oids are not guaranteed always to exist in all future releases. It is likely that the default table creation will switch to being without oids soon; that can already be specified as the default (in 8.0beta3). Oids are not guaranteed to be unique, since they wrap round when they reach the end of their range. If you wanted to use an oid as a guaranteed unique id, you would need to add a unique index on the oid column for that table; that could then cause an insertion to fail if an oid in the table were to be reused. If it were a very large table, that would cause the application to fail, because many insertions would be likely to fail after the wrap-around. > > 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. Reliance on a database feature, such as oids, as a key is a sign of bad design; a table row ought to have a unique key of some kind, and if you insert that row, you must know what that key is. If there is no other way to distinguish it, you can add a serial column for the sole purpose of providing a primary key. That would be part of the data rather than a side-effect of the implementation. I can't see how the use of oids would help you with a database of someone else's design, unless the designer used that feature already. > > 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..) You are correct. nextval() is guaranteed never to give the same number (unless setval() were used to reset the sequence value). A lot of people seem not to understand that. The trade-off is that sequences are not rolled back if a transaction is aborted. -- Oliver Elphick olly@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA ======================================== "I press toward the mark for the prize of the high calling of God in Christ Jesus." Philippians 3:14
> You are correct. nextval() is guaranteed never to give the same number > (unless setval() were used to reset the sequence value). Or unless the sequence wraps around. That's less likely (and less dangerous) than having the OID wrap around, but not impossible. I personally believe that there is value in a database-generated unique value like Oracle's ROWID. (Part of what I like about it is that since it is a system column it simplifies some application issues, since the app never has to worry about that column unless it chooses to.) Making the OID sufficiently large to avoid virtually all wraparound issues would probably mean going to a 64 bit field, which would certainly be a non-trivial task. -- Mike Nolan
On Sat, 23 Oct 2004, Mike Nolan wrote: > I personally believe that there is value in a database-generated unique > value like Oracle's ROWID. (Part of what I like about it is that since > it is a system column it simplifies some application issues, since the > app never has to worry about that column unless it chooses to.) If an application needs a column called oid in each table it could very well just define the tables like that. You could also make a script that checks all tables and make sure there is a oid if you want to. -- /Dennis Björklund
Mike Nolan <nolan@gw.tssi.com> writes:
>> You are correct. nextval() is guaranteed never to give the same number
>> (unless setval() were used to reset the sequence value).
> Or unless the sequence wraps around. That's less likely (and less
> dangerous) than having the OID wrap around, but not impossible.
Sequences do not wrap by default (only if you use the CYCLE option).
Anyway, if you use a bigint sequence field you are pretty safe from ever
running out of values...
regards, tom lane