Re: OID's - Mailing list pgsql-general

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