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, so you 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