Обсуждение: help with design of the 'COUNT(*) in separate table schema
Regarding the previously discussed triggers on tables to keep track of count: http://www.varlena.com/GeneralBits/120.php http://www.varlena.com/GeneralBits/49.php <from article> CREATE OR REPLACE FUNCTION count_rows() RETURNS TRIGGER AS ' BEGIN IF TG_OP = ''INSERT'' THEN UPDATE rowcount SET total_rows = total_rows + 1 WHERE table_name = TG_RELNAME; ELSIF TG_OP = ''DELETE'' THEN UPDATE rowcount SET total_rows = total_rows - 1 WHERE table_name = TG_RELNAME; END IF; RETURN NULL; END; ' LANGUAGE plpgsql; </from article> Wouldn't something like this need row-locking (SELECT for UPDATE) in order to serialize the execution of all triggers? Dennis Gearon Signature Warning ---------------- It is always a good idea to learn from your own mistakes. It is usually a better idea to learn from others’ mistakes, soyou do not have to make them yourself. from 'http://blogs.techrepublic.com.com/security/?p=4501&tag=nl.e036' EARTH has a Right To Life, otherwise we all die.
On 20 October 2010 23:52, Dennis Gearon <gearond@sbcglobal.net> wrote: > Regarding the previously discussed triggers on tables to keep track of count: > http://www.varlena.com/GeneralBits/120.php > http://www.varlena.com/GeneralBits/49.php > <from article> > CREATE OR REPLACE FUNCTION count_rows() > RETURNS TRIGGER AS > ' > BEGIN > IF TG_OP = ''INSERT'' THEN > UPDATE rowcount > SET total_rows = total_rows + 1 > WHERE table_name = TG_RELNAME; > ELSIF TG_OP = ''DELETE'' THEN > UPDATE rowcount > SET total_rows = total_rows - 1 > WHERE table_name = TG_RELNAME; > END IF; > RETURN NULL; > END; > ' LANGUAGE plpgsql; > </from article> > > Wouldn't something like this need row-locking (SELECT for UPDATE) in order to serialize the execution of all triggers? > The update will acquire a row level lock on rowcount for the TG_RELNAME tuple without you doing anything else. -- Regards, Peter Geoghegan