Re: How would you store read/unread topic status?

Mathieu Nebra wrote:
> Alexander Staubo a écrit :
>
>> On Tue, Jun 23, 2009 at 1:12 PM, Mathieu Nebra<mateo21(at)siteduzero(dot)com> wrote:
>>
>>> This "flags" table has more or less the following fields:
>>>
>>> UserID - TopicID - LastReadAnswerID
>>>
>> We are doing pretty much same thing.
>>
>>
>>> My problem is that everytime a user READS a topic, it UPDATES this flags
>>> table to remember he has read it. This leads to multiple updates at the
>>> same time on the same table, and an update can take a few seconds. This
>>> is not acceptable for my users.
>>>
>> First of all, and I'm sure you thought of this, an update isn't needed
>> every time a user reads a topic; only when there are new answers that
>> need to be marked as read. So an "update ... where last_read_answer_id
>> < ?" should avoid the need for an update.
>>
>
> We don't work that way. We just "remember" he has read these answers and
> then we can tell him "there are no new messages for you to read".
> So we just need to write what he has read when he reads it.
>
>
>> (That said, I believe PostgreSQL diffs tuple updates, so in practice
>> PostgreSQL might not be writing anything if you run an "update" with
>> the same value. I will let someone more intimate with the internal
>> details of updates to comment on this.)
>>
>> Secondly, an update should not take "a few seconds". You might want to
>> investigate this part before you turn to further optimizations.
>>
>
> Yes, I know there is a problem but I don't know if I am competent enough
> to tune PostgreSQL for that. It can take a while to understand the
> problem, and I'm not sure I'll have the time for that.
>
> I am, however, opened to suggestions. Maybe I'm doing something wrong
> somewhere.
>
>
>> In our application we defer the updates to a separate asynchronous
>> process using a simple queue mechanism, but in our case, we found that
>> the updates are fast enough (in the order of a few milliseconds) not
>> to warrant batching them into single transactions.
>>
>
> A few milliseconds would be cool.
> In fact, defering to another process is a good idea, but I'm not sure if
> it is easy to implement. It would be great to have some sort of UPDATE
> ... LOW PRIORITY to make the request non blocking.
>
> Thanks.
>
>
I use pg_send_query()
<http://ca2.php.net/manual/en/function.pg-send-query.php> in php to
achieve this for a views counter. "Script execution is not blocked while
the queries are executing."

It looks like this may just be a direct translation of PQsendQuery()
from libpq. Your preferred language may have a function like this.