On Tue, Jan 25, 2005 at 08:36:53AM -0500, Alex Turner wrote:
> Actualy max() works just fine. It's not the solution I use in the
> middle tier, but it works for a functional example. both max() and
> currval() are bad because they can cause a race condition where the
> sequence has been incremented by another thread. It's always better
> to get nextval('sequence') and store it in a local var, then use it in
> the main insert and corresponding sub-inserts.
Like I said, read the docs. currval was explicitly created to avoid the
race condition. It gives you the last number handed out in *this*
connection. It's also a lot faster than max. So different connections
get a different currval() and you get an error if you've not called
nextval() in the current connection (it works across transactions).
Storing in a var works too, but currval is totally safe.
Have a nice day,
> On Tue, 25 Jan 2005 09:23:31 +0100, Martijn van Oosterhout
> <kleptog@svana.org> wrote:
> > On Mon, Jan 24, 2005 at 07:22:32PM -0500, Alex Turner wrote:
> > > I am facing the classic pgsql ORDBMS problem:
> >
> > <snip>
> >
> > Why are you using MAX()? That won't work at all. Perhaps you need to
> > look up the documentation for nextval and currval. In particular, that
> > second query should be:
> >
> > insert into entity_phone select currval('entity_id_seq'),'610 495 5000';
> >
> > Also, I'm not sure if inheritance works quite the way you think in the
> > example you give, though other people may correct me on that.
> >
> > Hope this helps,
> > --
> > Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> > > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> > > tool for doing 5% of the work and then sitting around waiting for someone
> > > else to do the other 95% so you can sue them.
> >
> >
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.
