7wP1fD3T

1DROP FUNCTION IF EXISTS easydb_count__insert_trigger() CASCADE;
2DROP FUNCTION IF EXISTS easydb_count__delete_trigger() CASCADE;
3DROP FUNCTION IF EXISTS easydb_count__truncate_trigger() CASCADE;
4DROP FUNCTION IF EXISTS easydb_get_count(namxe);
5DROP FUNCTION IF EXISTS easydb_create_count_trigger(name);
6DROP FUNCTION IF EXISTS easydb_remove_count_trigger(name);
7
8DROP TABLE IF EXISTS eadb_table_stats CASCADE;
9
10
11CREATE TABLE eadb_table_stats(
12    id bigserial PRIMARY KEY,
13    table_name name NOT NULL,
14--    timestamp timestamp NOT NULL DEFAULT NOW(),
15    difference bigint NOT NULL
16);
17
18CREATE INDEX eadb_table_stats__table_name
19    ON eadb_table_stats (table_name);
20
21
22CREATE FUNCTION easydb_count__insert_trigger() RETURNS trigger LANGUAGE plpgsql AS $body$
23BEGIN
24--    RETURN NULL;
25    INSERT INTO eadb_table_stats(table_name, difference)
26    VALUES (TG_TABLE_NAME, +1);
27    RETURN NULL;
28END;
29$body$;
30
31CREATE FUNCTION easydb_count__delete_trigger() RETURNS trigger LANGUAGE plpgsql AS $body$
32BEGIN
33--    RETURN NULL;
34    INSERT INTO eadb_table_stats(table_name, difference)
35    VALUES (TG_TABLE_NAME, -1);
36    RETURN NULL;
37END;
38$body$;
39
40CREATE FUNCTION easydb_count__truncate_trigger() RETURNS trigger LANGUAGE plpgsql AS $body$
41BEGIN
42    DELETE FROM eadb_table_stats WHERE table_name = TG_TABLE_NAME;
43    INSERT INTO eadb_table_stats(table_name, difference)
44        VALUES (TG_TABLE_NAME, 0);
45    RETURN NULL;
46END;
47$body$;
48
49CREATE OR REPLACE FUNCTION easydb_get_count(v_table_name name) RETURNS bigint LANGUAGE plpgsql AS $body$
50DECLARE
51    v_table_n name := quote_ident(v_table_name);
52    v_sum bigint;
53    v_nr bigint;
54BEGIN
55    SELECT count(*), SUM(difference)
56    FROM eadb_table_stats
57    WHERE table_name = v_table_name INTO v_nr, v_sum;
58
59    IF v_sum IS NULL THEN
60        RAISE EXCEPTION 'table_count: count on uncounted table';
61    END IF;
62
63    /*
64     * We only sum up if we encounter a big enough amount of rows so summing
65     * is a real benefit.
66     */
67    IF v_nr > 100 THEN
68        DECLARE
69            v_cur_id bigint;
70            v_cur_difference bigint;
71            v_new_sum bigint := 0;
72            v_delete_ids bigint[];
73        BEGIN
74            RAISE NOTICE 'table_count: summing counter';
75
76            FOR v_cur_id, v_cur_difference IN
77               SELECT id, difference
78               FROM eadb_table_stats
79               WHERE table_name = v_table_name
80               ORDER BY id
81               FOR UPDATE NOWAIT LOOP
82                --collecting ids instead of doing every single delete is more efficient
83                v_delete_ids := v_delete_ids || v_cur_id;
84                v_new_sum := v_new_sum + v_cur_difference;
85                IF array_length(v_delete_ids, 1) > 100 THEN
86                    DELETE FROM eadb_table_stats WHERE id = ANY(v_delete_ids);
87                    v_delete_ids = '{}';
88                END IF;
89                --DELETE FROM eadb_table_stats WHERE id = v_cur_id;
90            END LOOP;
91            DELETE FROM eadb_table_stats WHERE id = ANY(v_delete_ids);
92            INSERT INTO eadb_table_stats(table_name, difference)
93                VALUES(v_table_name, v_new_sum);
94        EXCEPTION
95            --if somebody else summed up in a transaction which was open at the
96            --same time we ran the above statement
97            WHEN lock_not_available THEN
98                RAISE NOTICE 'table_count: locking failed';
99            --if somebody else summed up in a transaction which has committed
100            --successfully
101            WHEN serialization_failure THEN
102                RAISE NOTICE 'table_count: serialization failed';
103            --summing up won't work in a readonly transaction. One could check
104            --that explicitly
105            WHEN read_only_sql_transaction THEN
106                RAISE NOTICE 'table_count: not summing because in read only txn';
107        END;
108    END IF;
109    RETURN v_sum;
110END;
111$body$;
112
113CREATE FUNCTION easydb_create_count_trigger(v_table_name name) RETURNS void LANGUAGE plpgsql VOLATILE AS $body$
114DECLARE
115    v_table_n name := quote_ident(v_table_name);
116BEGIN
117    EXECUTE
118        'CREATE TRIGGER '||v_table_n||'__count_insert
119            AFTER INSERT
120            ON '||v_table_n||'
121            FOR EACH ROW
122            EXECUTE PROCEDURE easydb_count__insert_trigger()';
123
124    EXECUTE
125        'CREATE TRIGGER '||v_table_n||'__count_delete
126            AFTER DELETE
127            ON '||v_table_n||'
128            FOR EACH ROW
129            EXECUTE PROCEDURE easydb_count__delete_trigger()';
130
131    EXECUTE
132        'CREATE TRIGGER '||v_table_n||'__count_truncate
133            AFTER TRUNCATE
134            ON '||v_table_n||'
135            FOR EACH STATEMENT
136            EXECUTE PROCEDURE easydb_count__truncate_trigger()';
137
138    /*
139     * If the function was dropped without cleaning the content for that table
140     *  we would end up with old content + a new count
141     */
142    DELETE FROM eadb_table_stats WHERE table_name = v_table_name;
143    EXECUTE
144        $$INSERT INTO eadb_table_stats(table_name, difference)
145            SELECT $1, count(*) FROM $$||v_table_n USING(v_table_name);
146END
147$body$;
148
149
150CREATE FUNCTION easydb_remove_count_trigger(v_table_name name) RETURNS void LANGUAGE plpgsql VOLATILE AS $body$
151DECLARE
152    v_table_n name := quote_ident(v_table_name);
153BEGIN
154    EXECUTE 'DROP TRIGGER IF EXISTS '||v_table_n||'__count_insert ON '||v_table_n;
155    EXECUTE 'DROP TRIGGER IF EXISTS '||v_table_n||'__count_delete ON '||v_table_n;
156    EXECUTE 'DROP TRIGGER IF EXISTS '||v_table_n||'__count_truncate ON '||v_table_n;
157
158    DELETE FROM eadb_table_stats WHERE table_name = v_table_name;
159END
160$body$;
161
162--Beispiel:
163
164SELECT easydb_create_count_trigger('t');
165INSERT INTO t(data) VALUES(1);
166INSERT INTO t(data) VALUES(1);
167INSERT INTO t(data) VALUES(1);
168DELETE FROM t WHERE id = (SELECT min(id) FROM t);
169SELECT easydb_get_count('t');
170
171TRUNCATE t;
172
173SELECT easydb_get_count('t');
174
175SELECT easydb_remove_count_trigger('t');
176
177SELECT easydb_get_count('t');