"Eric Ridge" <ebr@tcdi.com> writes:
>> Hmm. I think you should think hard about why you believe that the
>> default has to work that way and you can't just use a sequence.
>> You're paying a high price to conform to what seems a very
>> questionable set of assumptions.
> I use the field for sorting, and since the client application has the
> ability to change the value of this field, it is possible they could set
> it to a number larger than the next value of the sequence. then an
> insert of a new record would appear before the changed record, instead
> of after it.
But if you allow applications to change the field, then all bets about
sort order are off anyway, no? It's far from clear exactly what
semantics you think you are guaranteeing here.
In any case it seems like changing the initially-assigned field value
is an infrequent operation, and that you ought to make *that* be the
expensive and not-safe-for-parallelism case, not row insertion.
(Perhaps this is a wrong assumption on my part, in which case ignore
what follows.) I'd still be inclined to use a sequence for insertion,
and to enforce consistency on update with an AFTER UPDATE trigger that
does something like
if old.fld != new.fld then
begin
lock mytable;
select setval('seq', max(fld)) from table;
end;
(Untested, but I hope the idea is clear.)
regards, tom lane