From: | Francisco Olarte <folarte(at)peoplecall(dot)com> |
---|---|
To: | Rafal Pietrak <rafal(at)ztk-rp(dot)eu> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: multiple UNIQUE indices for FK |
Date: | 2016-03-07 19:11:48 |
Message-ID: | CA+bJJbxhvo52oouF4Qpn-2ktYinqLKThL=nZBk32UdkQwRvWUw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi Rafal:
On Mon, Mar 7, 2016 at 9:29 AM, Rafal Pietrak <rafal(at)ztk-rp(dot)eu> wrote:
.....
>> be slower ). And you introduce several problems, the naming ones, a
>> very strange foreign-key relationship between kids, the possibility of
>> having a row inserted in the parent.
> No, no. It was OK. the parent table was appropriately "ruled on insert"
> for inherited tables to work as partitions.
But you have to rule a lot, to avoid people inserting into the kids,
anyway, without seeing the whole lot I'm not gonna comment more.
> and the table was called "messages" :)
But it did not contain messages, it contained message-persons relations.
..... sniped, too complex without seeing the whole dessign.
>> http://xyproblem.info/ .
> :) this is a good one!!!
> Actually I'm very, very acquainted with this "XY problem". i.e quite
> often when "end-users" ask me for additional functionality, they (like
> in the XY case) suggest a "technical solution". And (just like you :) I
> always ask: pls tell me what you do "traditionally", e.g "when you are
> currently doing this on paper: how does it go - step by step", then I'll
> find a good IT solution for you.
Well, now you have an url to mail them.
> In case of a complex inter-mangled systems, where a well defined
> "critical point" shows up, it's more efficient to extract the "show
> case" that causes the problem and focus on this, instead of digressing
> on overall design. (which may be flowed, but cannot be rewritten at this
> point).
May be, but for me your solutions are so complex I cannot follow them.
> BTW: using timestamp instead of FK to message_id does not work quite so
> well. To see the problem, ponder a question: what time resolution should
> such timestamp have, to be as robust as FK ... irrespectively of the
> transaction load?
That's irrelevant. Timestamp is a concept, as I told you, it's just a
value whcich defines a full order. Normally the system has a timestamp
source which insures it. If you have not one you can use a cache=1
sequence.
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.
... 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 ).
Francisco Olarte.
From | Date | Subject | |
---|---|---|---|
Next Message | Melvin Davidson | 2016-03-07 20:18:57 | Re: pg_restore man page question |
Previous Message | Adrian Klaver | 2016-03-07 19:08:11 | Re: pg_restore man page question |