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 so
