Hi list!
My issue is as follows :
I have to do some calculations 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.
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 (a or b)
ident | numeric(10,0) |
----the
count | integer
| ----the count
The problem of course is the locking issues while changing a and b
tables.What i am doing now is to
select 1 from counter where counter_type='a' and ident in (1,2,3,4) for
update;
select 1 from counter where 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 a where pkey in (5,6,7) for update;
My problems:
[1] Is the for update lock anouth here?
[2] The "for update" queries HAVE 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