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(at)edlsystems(dot)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(at)postgresql(dot)org)
>
>

_____________________________________
New things are always on the horizon.

On Sat, Oct 23, 2004 at 14:52:31 +0200,
Leen Besselink <leen(at)wirehub(dot)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(at)lfix(dot)co(dot)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(at)gw(dot)tssi(dot)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