From: | Martijn van Oosterhout <kleptog(at)svana(dot)org> |
---|---|
To: | Bo Lorentsen <bl(at)netgroup(dot)dk> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: mysql's last_insert_id |
Date: | 2003-08-29 07:25:19 |
Message-ID: | 20030829072519.GC23834@svana.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Fri, Aug 29, 2003 at 08:36:50AM +0200, Bo Lorentsen wrote:
> I made a general insert (executeUpdate) function to my general DB layer,
> that returned the newly inserted row (to be able to use the default
> initealized elements). This code will stop working when oid is disabled,
> very sad thing.
>
> I will have to push this row awareness to a higher level in the future.
Not really, the sequences have very predicatble names. For tables X with
primary key Y, the sequence is usually called X_Y_seq.
> But, how does PG refer to rows internally, and why can't we get this
> reference in the application layer. I don't care if its an integer, long
> or even a string, but I like to know the unique id of the currently and
> newly inserted row, without knowing the datamodel (table layout).
Oh, there's a CTID but that doesn't keep over an update and isn't
transaction safe anyway. Mind, you could always just call your sequences
seq_<TABLENAME>, then you wouldn't have to guess. This is how DCL does it.
> > There are various solutions. Some people use functions to do inserts, I just
> > use the string "currval(whatever)" in the application layer which the
> > database replaces with the appropriate value.
> Hmm, but what happends if more than one connection does this at the same
> time ? Then, only one of the concurrent connections will have a insert
> on the returned value, and the rest will get a wrong row reference
> returned.
Wrong. What gave you that impression? nextval() and currval() work fine with
transactions, look through the docs.
> The only safe methode would be to do a "select nextval(whatever)", and
> aply this number by "hand" to the insert, but that remove the
> possibility to make general code even more, but it will be safe.
Like I said, currval() will do what you want. Make predicatable names for
your sequences and you won't need to worry about it anymore.
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> "All that is needed for the forces of evil to triumph is for enough good
> men to do nothing." - Edmond Burke
> "The penalty good people pay for not being interested in politics is to be
> governed by people worse than themselves." - Plato
From | Date | Subject | |
---|---|---|---|
Next Message | Bo Lorentsen | 2003-08-29 07:27:09 | Re: mysql's last_insert_id |
Previous Message | Dennis Björklund | 2003-08-29 07:05:52 | Re: mysql's last_insert_id |