Re: Schema for caching message-count in folders using triggers

On 03/05/2015 12:04 PM, Andreas Joseph Krogh wrote:
> På torsdag 05. mars 2015 kl. 20:59:28, skrev Adrian Klaver
> <adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>>:

> >
> > The problem with this is locking (waiting for another TX to
> commit when
> > updating the same folder) and deadlock issues when trying to
> > simultaneously insert/delete/update messages in a folder.
> > Does anyone have any better ideas for safely caching the
> message-count
> > in each folder without locking and deadlock issues?
>
> How accurate does this have to be?
>
> Not exactly following what is folder?
> Is it a table that contains the messages?
>
> A top of the head idea would be to use sequences. Create a sequence for
> each folder starting at current count and then use nextval, setval to
> change the value:
>
> http://www.postgresql.org/docs/9.4/interactive/functions-sequence.html
>
> It is not transactional, so it would probably not be spot on, which is
> why I asked about accuracy earlier.
>
> Yes, 'folder' is a table which contains 'message':
>
> create tablefolder(
> idserial PRIMARY KEY,
> namevarchar not null unique,
> message_countinteger not null default0
> );
>
> create table message(
> idserial PRIMARY KEY,
> folder_idINTEGER NOT NULL REFERENCESfolder(id),
> messagevarchar not null
> );
>
> The count has to be exact, no estimate from EXPLAIN or such...

Well there goes my idea. Seems the way to go is partitioning:

http://www.postgresql.org/docs/9.4/static/ddl-partitioning.html

Break the data into smaller units

> --
> *Andreas Joseph Krogh*
> CTO / Partner - Visena AS
> Mobile: +47 909 56 963
> andreas(at)visena(dot)com <mailto:andreas(at)visena(dot)com>
> www.visena.com <https://www.visena.com>
> <https://www.visena.com>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com