Re: multiple UNIQUE indices for FK - Mailing list pgsql-general

Hi Rafal:

On Mon, Mar 7, 2016 at 9:29 AM, Rafal Pietrak <rafal@ztk-rp.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.