W dniu 07.03.2016 o 20:11, Francisco Olarte pisze:
[---------------]
>
> When in a single process problem like this I normally use an
> XXXXsecond timestamp which I autoincrement if repeated, something
> like:
>
> get_timestamp_for_id() {
> Locked(mutex) {
> now=time();
> if (last_returned_id >= now) {
> return ++ last_returned_id;
> } else {
> return last_returned_id = now;
> }
> }
>
> This has the nice property that it eventually drops to timestamp after
> a burst, so the ID do double service as generation timestamps, but a
> single locked counter, a sequence, works as well.
OK. But in this case I really fail to see the usefulness of "timestamp
semantics" for this field. Just plain INT/BIGINT would do; particularly
if the system has a chance to grow beyond 1 message per second
sustained. If you used it, while actually meaning: "something
sequencial/monotonic like timestamp is", that was misleading to me. I'd
say: an inverse XY problem :)
... but INT/SERIAL semantics might actually be a workaround for me.
Finding a "largest but smaller then" is somewhat more expensive then
plain hit with NEXT=CURRENT ... but may be the penalty will not be so
great after all. And if there are no hidden rise conditions/locking
problems that might just do the trick.
>
> ... More snipping.
>
> I cannot recommend more things. The only thing, for easier locating of
> a message in a person, cache the last message id in the person ( which
> you can use as a lock for updtings ) and just use the next for linking
> the chain ( because, as you said, a message can be no longer the last,
> so, unless this only happens when you destructively pop the last
> message in the chain for a user, you need a full linked list to
> recover the previous one ) ( if you determine the new last message by
> other means you do not need any of these things, just cache the last
> message in each person record, then when you insert a new one you
> update each sender / recipient with the last message id at the same
> time you insert the records, preferably sorting the ids first to avoid
> deadlocks if your concurrency is high, although I suspect you'll need
> a linked-list-per-user if it has to be the previous one ).
Yes I do. The NEXT field was there for some time now, and it worked
exactly as expected. That's why I'm so desperate to keep it.
Particularly that it followed the "business model" the schema served (in
the language of XY problem, for the pop operation), literally:
"efficiently find a message, that this person send before".
But thenx for tackling the problem with me.
-R
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
