Re: serialization errors when inserting new records

Ralph van Etten wrote:
> Ok, but then I have the same problem. The "display" field has to be
> unique and it needs to use a SELECT MAX in an INSERT query and this gives
> the same problem. Only not with the primary key but with possible
> duplicate values in the "display" field.

If you absolutely need the display field to be unique and it has to be a
sequence starting from 1 -- it's simplicity itself. Use the
serialization errors to your advantage. If you try to insert a record
and you get a dupe key violation, just increment your seq counter by 1
and try again. Keep trying until you succeed. No need to lock the tables
at all.

If the display field is just an arbitrary reference number that doesn't
require 1..n, you've got even more ways of making it work. Make it a
compound field consisting of something that's impossible do duplicate --
the server's process IDs. Since I primarily use perl, I myself use the
perl process ID but I'm pretty sure you can get the connection's process
ID from querying the information schema. Ie, postmaster pid 5673 can
only run 1 statement at a time so if you insert 05-5673-15, you are
guaranteed success.