Re: OID's - Mailing list pgsql-general

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.