rserv/doc/tracking_row_count.txt

This text is taken from PostgreSQL General Bits 27-Oct-2003 Issue: 49 column at
http://www.varlena.com/varlena/GeneralBits/49.php

-------------------------------------------------------------------------------


   Tracking the row count
   [GENERAL] create triggers 20-Oct-2003

   A trigger to update the row count is needed. The actual row count is
   important for the application and count(*) by nature is too slow.

   The way to implement a proper row counter is to create a trigger on
   the table which needs to be counted. This trigger will increment or
   decrement the count on insert or delete.

   There are a couple of interesting issues with the row counting
   implementation. The first is that the counter trigger function can
   work for both insert and delete and with any table by using trigger
   information in plpgsql. The second is the issue of initializing the
   rowcount table.

   Suppose you had the table mystuff and set up the table rowcount to
   hold the row count of many tables.
        CREATE TABLE mystuff (
                name  text NOT NULL,
                description text,
                PRIMARY KEY (name));

        CREATE TABLE rowcount (
                table_name  text NOT NULL,
                total_rows  bigint,
                PRIMARY KEY (table_name));

   Most people are familiar with the use of OLD and NEW variables in
   trigger functions, but there are several other informational variables
   available.

             Name                      Meaning
          TG_NAME    Name of the trigger
          TG_WHEN    BEFORE or AFTER
          TG_LEVEL   ROW or STATEMENT
          TG_OP      INSERT, UPDATE or DELETE
          TG_RELID   Relation OID of table with trigger
          TG_RELNAME Table name of table with trigger
          TG_NARGS   Number of arguments in row being updated
          TG_ARGV[]  Text array of datatypes in row being updated

    Note that Statement level triggers are available in 7.4 and forward.

   The count_rows() trigger function uses TG_OP and TG_RELNAME. TG_OP
   indicates whether it is a DELETE or INSERT and TG_RELNAME is used to
   store the count in the countrows table by table name. This is the
   function:
        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;

   There are many ways the various TG variables can be used to write
   generic triggers. Different courses of action can be taken based on
   the exact definition of the trigger. The data in the row can be
   accessed via NEW and OLD and information about their datatypes is
   available if decisions or actions need to be done based on the data
   type.

   Now we will look at what is necessary to initialize the trigger. If
   the trigger function and the tables are all defined in the same
   transaction, then initialization is not necessary. However, most of
   the time if row counting functionality is being added to an existing
   table, the base row count must be ascertained.

   The initialization must be done in a single transaction. The target
   table is locked to prevent updates during this initialization. Then
   the trigger is created and the rowcount is updated with the current
   row count. Once the initialization transaction is committed, then your
   counter is operational. Don't forget to test it!
        BEGIN;
           -- Make sure no rows can be added to mystuff until we have finished
           LOCK TABLE mystuff IN SHARE ROW EXCLUSIVE MODE;

           create TRIGGER countrows
              AFTER INSERT OR DELETE on mystuff
              FOR EACH ROW EXECUTE PROCEDURE count_rows();

           -- Initialise the row count record
           DELETE FROM rowcount WHERE table_name = 'mystuff';

           INSERT INTO rowcount (table_name, total_rows)
           VALUES  ('mystuff',  (SELECT COUNT(*) FROM mystuff));

        COMMIT;

        --
        -- Testing
        --
        insert into mystuff values ('abacus','mathmatics');
        insert into mystuff values ('bee','insect');
        select * from rowcount;
        insert into mystuff values ('dog','pet');
        insert into mystuff values ('cathedral','building');
        select * from rowcount;
        select * from mystuff;
        delete from mystuff where name='abacus';
        select * from rowcount;
        select * from mystuff;

   Contributors: Ling Xiaoyu cdu_lx at yahoo.com Oliver Elphick olly at
   lfix.co.uk elein at varlena.com

1	dpavlin	1.1	This text is taken from PostgreSQL General Bits 27-Oct-2003 Issue: 49 column at
2			http://www.varlena.com/varlena/GeneralBits/49.php
3
4			-------------------------------------------------------------------------------
5
6
7			Tracking the row count
8			[GENERAL] create triggers 20-Oct-2003
9
10			A trigger to update the row count is needed. The actual row count is
11			important for the application and count(*) by nature is too slow.
12
13			The way to implement a proper row counter is to create a trigger on
14			the table which needs to be counted. This trigger will increment or
15			decrement the count on insert or delete.
16
17			There are a couple of interesting issues with the row counting
18			implementation. The first is that the counter trigger function can
19			work for both insert and delete and with any table by using trigger
20			information in plpgsql. The second is the issue of initializing the
21			rowcount table.
22
23			Suppose you had the table mystuff and set up the table rowcount to
24			hold the row count of many tables.
25			CREATE TABLE mystuff (
26			name text NOT NULL,
27			description text,
28			PRIMARY KEY (name));
29
30			CREATE TABLE rowcount (
31			table_name text NOT NULL,
32			total_rows bigint,
33			PRIMARY KEY (table_name));
34
35			Most people are familiar with the use of OLD and NEW variables in
36			trigger functions, but there are several other informational variables
37			available.
38
39			Name Meaning
40			TG_NAME Name of the trigger
41			TG_WHEN BEFORE or AFTER
42			TG_LEVEL ROW or STATEMENT
43			TG_OP INSERT, UPDATE or DELETE
44			TG_RELID Relation OID of table with trigger
45			TG_RELNAME Table name of table with trigger
46			TG_NARGS Number of arguments in row being updated
47			TG_ARGV[] Text array of datatypes in row being updated
48
49			Note that Statement level triggers are available in 7.4 and forward.
50
51			The count_rows() trigger function uses TG_OP and TG_RELNAME. TG_OP
52			indicates whether it is a DELETE or INSERT and TG_RELNAME is used to
53			store the count in the countrows table by table name. This is the
54			function:
55			CREATE OR REPLACE FUNCTION count_rows()
56			RETURNS TRIGGER AS
57			'
58			BEGIN
59			IF TG_OP = ''INSERT'' THEN
60			UPDATE rowcount
61			SET total_rows = total_rows + 1
62			WHERE table_name = TG_RELNAME;
63			ELSIF TG_OP = ''DELETE'' THEN
64			UPDATE rowcount
65			SET total_rows = total_rows - 1
66			WHERE table_name = TG_RELNAME;
67			END IF;
68			RETURN NULL;
69			END;
70			' LANGUAGE plpgsql;
71
72			There are many ways the various TG variables can be used to write
73			generic triggers. Different courses of action can be taken based on
74			the exact definition of the trigger. The data in the row can be
75			accessed via NEW and OLD and information about their datatypes is
76			available if decisions or actions need to be done based on the data
77			type.
78
79			Now we will look at what is necessary to initialize the trigger. If
80			the trigger function and the tables are all defined in the same
81			transaction, then initialization is not necessary. However, most of
82			the time if row counting functionality is being added to an existing
83			table, the base row count must be ascertained.
84
85			The initialization must be done in a single transaction. The target
86			table is locked to prevent updates during this initialization. Then
87			the trigger is created and the rowcount is updated with the current
88			row count. Once the initialization transaction is committed, then your
89			counter is operational. Don't forget to test it!
90			BEGIN;
91			-- Make sure no rows can be added to mystuff until we have finished
92			LOCK TABLE mystuff IN SHARE ROW EXCLUSIVE MODE;
93
94			create TRIGGER countrows
95			AFTER INSERT OR DELETE on mystuff
96			FOR EACH ROW EXECUTE PROCEDURE count_rows();
97
98			-- Initialise the row count record
99			DELETE FROM rowcount WHERE table_name = 'mystuff';
100
101			INSERT INTO rowcount (table_name, total_rows)
102			VALUES ('mystuff', (SELECT COUNT(*) FROM mystuff));
103
104			COMMIT;
105
106			--
107			-- Testing
108			--
109			insert into mystuff values ('abacus','mathmatics');
110			insert into mystuff values ('bee','insect');
111			select * from rowcount;
112			insert into mystuff values ('dog','pet');
113			insert into mystuff values ('cathedral','building');
114			select * from rowcount;
115			select * from mystuff;
116			delete from mystuff where name='abacus';
117			select * from rowcount;
118			select * from mystuff;
119
120			Contributors: Ling Xiaoyu cdu_lx at yahoo.com Oliver Elphick olly at
121			lfix.co.uk elein at varlena.com
122