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 |
|