Re: Best approach for a "gap-less" sequence

Jorge Godoy wrote:
> Berend Tober <btober(at)seaworthysys(dot)com> writes:
>
>>A business requirement is to generate table rows that have uniformly
>>increasing, whole number sequences, i.e., the "gap-less" sequence.
>
> This is something that I'll also have to code ;-) But the sequence for
> "employees" would also be a sequential and gapless sequence here (yep, it's
> composed of two gapless sequences, being one "fixed" and the other varying for
> each new record inside the first sequence).

Shouldn't be a problem to implement the same approach one level deeper
like that.

> Is the performance degradation here too big?

That is where my empirical evidence is somewhat deficient. The
application from which that example was drawn is used routinely by less
than five persons, so any performance degradation is not evident.

> I think that it might be lower
> than with approaches that used some kind of locking...

Your comment is confusing. The example DOES use locking -- the UPDATE
statement inside the trigger locks the modified employee row until the
trigger function completes -- I'm pretty sure I pointed out that. This
is the minimally sufficient locking that has to happen to satisfy this
business requirement. The original poster (was that you?) was using
table-level locking, which is unnecessary in this approach.

> I'm not sure, on this approach of yours, how's the probability of having
> several records inserted on different transactions for one employee. In my
> cases I see that when I have one unique "filter" (employee, invoice, etc.) the
> operation is done only by one person and consequently in one transaction
> only, what makes it possible to adopt more complex -- and badly performant --
> solutions (not that I want them, it's just that it wouldn't be noticeable in
> the application as far as the user is concerned). Do you have this
> concurrence on the real application?

The design was intended to withstand concurrent use. As explained in

"http://www.postgresql.org/docs/7.4/static/explicit-locking.html#LOCKING-ROWS",

"A row-level lock on a specific row is automatically acquired when the
row is updated (or deleted or marked for update). The lock is held until
the transaction commits or rolls back. Row-level locks do not affect
data querying; they block writers to the same row only."

This is why you always UPDATE before SELECT. Since the trigger locks the
row first, a second transaction initiated before completion of the first
transaction is blocked until the first transaction commits.

Regards,
Berend Tober