/[rserv]/doc/tracking_row_count.txt
This is repository of my old source code which isn't updated any more. Go to git.rot13.org for current projects!
ViewVC logotype

Contents of /doc/tracking_row_count.txt

Parent Directory Parent Directory | Revision Log Revision Log


Revision 1.1 - (show annotations)
Mon Oct 27 20:55:54 2003 UTC (20 years, 5 months ago) by dpavlin
Branch: MAIN
CVS Tags: HEAD
File MIME type: text/plain
Fast tracking of row count without using count(*). This might be usefull
for CleanLog (after some benchmarking)

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

  ViewVC Help
Powered by ViewVC 1.1.26