Re: Moving from MySQL to PGSQL....some questions (multilevel

On 2004.03.04 09:27 Bruno Wolff III wrote:
> On Thu, Mar 04, 2004 at 08:48:40 -0600,
> "Karl O. Pinc" <kop(at)meme(dot)com> wrote:
> >
> > So it sounds like this would be a concurrency safe way to perform
> > the operation. It also sounds like it might be a good idea
> > to do SELECT FOR UPDATE on the table/row keyed by id1 to keep
> > the serialized transactions from stepping on each other's toes.
>
> This won't always work since SELECT FOR UPDATE only locks tuples
> visible to the current transaction. It won't keep another transaction
> from inserting new tuples that would meet the critera. I think the
> current general solution is to lock the table.

That would be the general solution.
In this case though you're only interested in the one row, keyed by
the value on which you're sequencing, which holds the current
maximum sequence number.
Karl <kop(at)meme(dot)com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein