Jean-Christophe Boggio <cat@thefreecat.org> writes:
> I have a before-ins-row trigger that is supposed to find a unique
> value for one of the fields but it seems sometimes there are
> non-unique values that try to be created. They are rejected but I
> don't see the bug.
Your trigger will obviously fail to generate unique keys if two
transactions are running concurrently, since any two transactions
started in the same minute will generate the same initial key,
and if they are running concurrently then neither will see the other's
entry in the table. Given that, I don't know why you're bothering.
Why don't you use a sequence object to generate the unique keys?
The fact that concurrency is not obvious from the log doesn't prove
a lot, since we can't see very much of the trigger's operation there.
(The internal queries of the trigger will appear in the log only when
compiled, ie, first time through that line in a particular backend.)
I think you should just assume that you're seeing failure in the
concurrency case.
regards, tom lane