Hi Rafal: On Mon, Mar 7, 2016 at 9:29 AM, Rafal Pietrak <rafal@xxxxxxxxx> 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
