Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Jorge Godoy wrote:
Berend Tober <btober@xxxxxxxxxxxxxxxx> 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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux