From: Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
To: Bruno Wolff III <bruno@wolff.to>
Cc: pgsql-admin@postgresql.org
Subject: Re: preventing deadlocks
Date: Thu, 05 Jan 2006 10:34:31 +0200
Message-ID: <43BCDA17.6070806@mail.jct.ac.il> (raw)
In-Reply-To: <20060105065747.GA17322@wolff.to>
References: <43B10E07.7010503@mail.jct.ac.il>
<20051228202607.GA5194@wolff.to>
<Pine.GSO.4.58_heb2.09.0601021131080.10840@ketubot.jct.ac.il>
<20060104003001.GY82560@pervasive.com>
<Pine.GSO.4.58_heb2.09.0601041035520.7100@ketubot.jct.ac.il>
<20060105065747.GA17322@wolff.to>
Bruno Wolff III wrote:
>On Wed, Jan 04, 2006 at 10:51:55 +0200,
> Tsirkin Evgeny <tsurkin@mail.jct.ac.il> wrote:
>
>
>>>What you want to do in the trigger isinsert a new row into a table that
>>>contains the change in count, instead of trying to update a single row
>>>for each value/ident (btw, you'll probably get better performance if you
>>>make ident an int instead of a numeric).
>>>
>>>
>>Why?
>>
>>
>
>Because this doesn't block other processes and still gives you correct
>results.
>
>
>
I understand THAT ,I meant why int will give me more performance.
>>>So now you'll have a list of
>>>changes to the value, which you will periodically want to roll up into a
>>>table that just stores the count.
>>>
>>>
>>Interesting idea.Thanks.However it pretty complicates things ,maybe there
>>will be simpler solution.Something i did not thought about at all - i think that
>>
>>
>
>Not that avoids blocking. If you don't have a lot of concurrent queries then
>this may not be an issue for you.
>
>
>
Well i DO have a lot of concurrent queries,that is the whole point.
>>counting is something that everybody does.
>>
>>
>
>No it isn't. Smetimes they aren't needed at all, sometimes approximate values
>are good enough, and even when they are needed, it is often the case that
>it is better for count queries to run slower so that other queries run faster.
>
>
>
It was just a hope of mine.
>>[1] I have also a hope that i can create a trigger that locks counter
>>table once a 'select for update' was done on one of the tables i count.
>>However how can i say if a select that fires a trigger is a 'for update'
>>one?
>>
>>
>
>If you have a counter table and do an UPDATE that will lock that row.
>If you have multiple tables that you keep counts for you will want to lock
>the whole counter table using a LOCK command or else you can get deadlocks.
>Note this means that inserts and deletes from any of the tracked tables
>will block inserts and deletes of those tables in other concurrent queries.
>
>
>
That does not help me .What i want is :
select 1 from table a where whatever for update;
issuing this should fire a trigger that does:
select 1 from table counter where a.pkey=counter.ident or something
however this should only be done for select FOR UPDATE!
And i can't know that inside the trigger!
>>[2] Maybe there is a MVCC or something solution like Bruno suggested (that
>>i did not realy understood thought).
>>
>>
>
>The explanation given at the top is the gist of the solution that uses MVCC
>advantagesously.
>
>
OK.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: tsurkin@mail.jct.ac.il, bruno@wolff.to
Subject: Re: preventing deadlocks
In-Reply-To: <43BCDA17.6070806@mail.jct.ac.il>
References: <43B10E07.7010503@mail.jct.ac.il> <20051228202607.GA5194@wolff.to> <Pine.GSO.4.58_heb2.09.0601021131080.10840@ketubot.jct.ac.il> <20060104003001.GY82560@pervasive.com> <Pine.GSO.4.58_heb2.09.0601041035520.7100@ketubot.jct.ac.il> <20060105065747.GA17322@wolff.to> <43BCDA17.6070806@mail.jct.ac.il>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox