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

Bruno Wolff III <bruno@wolff.to> writes:
> 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.

If I understood the requirements correctly, it might be sufficient to
put a unique index on (id1,id2).  If two transactions simultaneously try
to insert for the same id1, one would get a duplicate-index-entry
failure, and it would have to retry.  The advantage is you take no
table-wide lock.  So if the normal usage pattern involves lots of
concurrent inserts for different id1 values, you'd come out ahead.
Whether that applies, or is worth the hassle of a retry loop in the
application, I can't tell from the info we've been given.

            regards, tom lane