From: Jim C. Nasby <jnasby@pervasive.com>
To: Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
Cc: Bruno Wolff III <bruno@wolff.to>
Cc: Tsirkin Evgeny <tsurkin@jct.ac.il>
Cc: pgsql-admin@postgresql.org
Subject: Re: preventing deadlocks
Date: Tue, 3 Jan 2006 18:30:01 -0600
Message-ID: <20060104003001.GY82560@pervasive.com> (raw)
In-Reply-To: <Pine.GSO.4.58_heb2.09.0601021131080.10840@ketubot.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>
On Mon, Jan 02, 2006 at 11:36:11AM +0200, Tsirkin Evgeny wrote:
> Thanks for answer.However i have already searched for a way to make count
> faster and didn't find anything.
> Any pointers will be appreciated.
What you want to do in the trigger is insert 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). 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.
> On Wed, 28 Dec 2005, Bruno Wolff III wrote:
>
> > On Tue, Dec 27, 2005 at 11:48:55 +0200,
> > Tsirkin Evgeny <tsurkin@mail.jct.ac.il> wrote:
> > >
> > > Hi list!
> > > My issue is as follows :
> > > I have to do somecalculations based on *exact* number of rows in 2
> > > tables (with a filter) meaning:
> > > SELECT count(*) FROM a WHERE a.row1 in (1,2,3,4);
> > > SELECT count(*) FROM b WHERE b.row1 in (1,2,3,4);
> > > However i couldn't use the count(*) since it is too slow beacause of the
> > > table size.
> >
> > You should look through the past archives on this subject. There is a way to
> > do this that uses MVCC for incremental changes. You do want to make a sweep
> > through the delta table periodically which will need stronger locking, but
> > this won't block reads on a and b.
> >
> > > So,i created a trigger that on insert increments and on delete
> > > decriments special "counter" table
> > > that contains
> > > \d counter
> > > Column | Type | Modifiers
> > > --------------+-----------------------+-----------
> > > counter_type | character varying(30) | ---- the table name
> > > ident | numeric(10,0) |
> > > count | integer
> > >
> > >
> > > The problem of course is thelocking issues while changing a and b
> > > tables.What i am doing now is to
> > > select 1 from counterwhere counter_type='a' and ident in (1,2,3,4)
> > > for update;
> > > select 1 from counterwhere counter_type='b' and ident in (5,6,7) for
> > > update;
> > > Befor changing anything in tables "a" and "b"in transaction .I am also
> > > doing for update select on the
> > > "a" and "b" tables itself ,that is:
> > > select b from awhere pkey in (5,6,7) for update;
> > >
> > > My problems:
> > > [1] Is the for update lock anouthhere?
> > > [2] The "for update" queriesHAVE to be done in the same order in all
> > > application which is pretty error prone -
> > > it is very easy to forget in one place and get a deadlock.
> > > [3] Can i make a trigger that automatically locks the counter_type='b'
> > > if a for update select was done on table b?
> > > something like (pseudo):
> > > trigger on select for update table b{
> > > select for update where ident = OLD.pkey ;
> > > }
> > > [4] Can i combine queries for multiple tables to make locking atomic:
> > > select 1 from b,counter where b.pkey in (1,2,3,4) and counter.ident in
> > > (1,2,3,4);
> > > Hope for help and sorry for long message.
> > > evgeny
> > >
> > >
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 4: Have you searched our list archives?
> > >
> > > http://archives.postgresql.org
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: don't forget to increase your free space map settings
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
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: jnasby@pervasive.com, tsurkin@mail.jct.ac.il, bruno@wolff.to, tsurkin@jct.ac.il
Subject: Re: preventing deadlocks
In-Reply-To: <20060104003001.GY82560@pervasive.com>
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>
* 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