Message-ID: <54F8BD24.6070101@aklaver.com>
Date: Thu, 05 Mar 2015 12:31:32 -0800
From: Adrian Klaver <adrian.klaver@aklaver.com>
User-Agent: Mozilla/5.0 (X11; Linux i686;
 rv:31.0) Gecko/20100101 Thunderbird/31.5.0
MIME-Version: 1.0
To: Andreas Joseph Krogh <andreas@visena.com>, pgsql-sql@postgresql.org
Subject: Re: Schema for caching message-count in folders using triggers
References: <VisenaEmail.a.dd389169d0fb6df0.14beb890fcb@tc7-visena>
In-Reply-To: <VisenaEmail.a.dd389169d0fb6df0.14beb890fcb@tc7-visena>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

On 03/05/2015 12:04 PM, Andreas Joseph Krogh wrote:
> På torsdag 05. mars 2015 kl. 20:59:28, skrev Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.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@visena.com <mailto:andreas@visena.com>
> www.visena.com <https://www.visena.com>
> <https://www.visena.com>


-- 
Adrian Klaver
adrian.klaver@aklaver.com


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql