Skip to content

Instantly share code, notes, and snippets.

@anarazel
Created July 22, 2012 15:14
Show Gist options
  • Save anarazel/3159959 to your computer and use it in GitHub Desktop.
Save anarazel/3159959 to your computer and use it in GitHub Desktop.
DROP FUNCTION IF EXISTS easydb_count__insert_trigger() CASCADE;
DROP FUNCTION IF EXISTS easydb_count__delete_trigger() CASCADE;
DROP FUNCTION IF EXISTS easydb_count__truncate_trigger() CASCADE;
DROP FUNCTION IF EXISTS easydb_get_count(namxe);
DROP FUNCTION IF EXISTS easydb_create_count_trigger(name);
DROP FUNCTION IF EXISTS easydb_remove_count_trigger(name);
DROP TABLE IF EXISTS eadb_table_stats CASCADE;
CREATE TABLE eadb_table_stats(
id bigserial PRIMARY KEY,
table_name name NOT NULL,
-- timestamp timestamp NOT NULL DEFAULT NOW(),
difference bigint NOT NULL
);
CREATE INDEX eadb_table_stats__table_name
ON eadb_table_stats (table_name);
CREATE FUNCTION easydb_count__insert_trigger() RETURNS trigger LANGUAGE plpgsql AS $body$
BEGIN
-- RETURN NULL;
INSERT INTO eadb_table_stats(table_name, difference)
VALUES (TG_TABLE_NAME, +1);
RETURN NULL;
END;
$body$;
CREATE FUNCTION easydb_count__delete_trigger() RETURNS trigger LANGUAGE plpgsql AS $body$
BEGIN
-- RETURN NULL;
INSERT INTO eadb_table_stats(table_name, difference)
VALUES (TG_TABLE_NAME, -1);
RETURN NULL;
END;
$body$;
CREATE FUNCTION easydb_count__truncate_trigger() RETURNS trigger LANGUAGE plpgsql AS $body$
BEGIN
DELETE FROM eadb_table_stats WHERE table_name = TG_TABLE_NAME;
INSERT INTO eadb_table_stats(table_name, difference)
VALUES (TG_TABLE_NAME, 0);
RETURN NULL;
END;
$body$;
CREATE OR REPLACE FUNCTION easydb_get_count(v_table_name name) RETURNS bigint LANGUAGE plpgsql AS $body$
DECLARE
v_table_n name := quote_ident(v_table_name);
v_sum bigint;
v_nr bigint;
BEGIN
SELECT count(*), SUM(difference)
FROM eadb_table_stats
WHERE table_name = v_table_name INTO v_nr, v_sum;
IF v_sum IS NULL THEN
RAISE EXCEPTION 'table_count: count on uncounted table';
END IF;
/*
* We only sum up if we encounter a big enough amount of rows so summing
* is a real benefit.
*/
IF v_nr > 100 THEN
DECLARE
v_cur_id bigint;
v_cur_difference bigint;
v_new_sum bigint := 0;
v_delete_ids bigint[];
BEGIN
RAISE NOTICE 'table_count: summing counter';
FOR v_cur_id, v_cur_difference IN
SELECT id, difference
FROM eadb_table_stats
WHERE table_name = v_table_name
ORDER BY id
FOR UPDATE NOWAIT LOOP
--collecting ids instead of doing every single delete is more efficient
v_delete_ids := v_delete_ids || v_cur_id;
v_new_sum := v_new_sum + v_cur_difference;
IF array_length(v_delete_ids, 1) > 100 THEN
DELETE FROM eadb_table_stats WHERE id = ANY(v_delete_ids);
v_delete_ids = '{}';
END IF;
--DELETE FROM eadb_table_stats WHERE id = v_cur_id;
END LOOP;
DELETE FROM eadb_table_stats WHERE id = ANY(v_delete_ids);
INSERT INTO eadb_table_stats(table_name, difference)
VALUES(v_table_name, v_new_sum);
EXCEPTION
--if somebody else summed up in a transaction which was open at the
--same time we ran the above statement
WHEN lock_not_available THEN
RAISE NOTICE 'table_count: locking failed';
--if somebody else summed up in a transaction which has committed
--successfully
WHEN serialization_failure THEN
RAISE NOTICE 'table_count: serialization failed';
--summing up won't work in a readonly transaction. One could check
--that explicitly
WHEN read_only_sql_transaction THEN
RAISE NOTICE 'table_count: not summing because in read only txn';
END;
END IF;
RETURN v_sum;
END;
$body$;
CREATE FUNCTION easydb_create_count_trigger(v_table_name name) RETURNS void LANGUAGE plpgsql VOLATILE AS $body$
DECLARE
v_table_n name := quote_ident(v_table_name);
BEGIN
EXECUTE
'CREATE TRIGGER '||v_table_n||'__count_insert
AFTER INSERT
ON '||v_table_n||'
FOR EACH ROW
EXECUTE PROCEDURE easydb_count__insert_trigger()';
EXECUTE
'CREATE TRIGGER '||v_table_n||'__count_delete
AFTER DELETE
ON '||v_table_n||'
FOR EACH ROW
EXECUTE PROCEDURE easydb_count__delete_trigger()';
EXECUTE
'CREATE TRIGGER '||v_table_n||'__count_truncate
AFTER TRUNCATE
ON '||v_table_n||'
FOR EACH STATEMENT
EXECUTE PROCEDURE easydb_count__truncate_trigger()';
/*
* If the function was dropped without cleaning the content for that table
* we would end up with old content + a new count
*/
DELETE FROM eadb_table_stats WHERE table_name = v_table_name;
EXECUTE
$$INSERT INTO eadb_table_stats(table_name, difference)
SELECT $1, count(*) FROM $$||v_table_n USING(v_table_name);
END
$body$;
CREATE FUNCTION easydb_remove_count_trigger(v_table_name name) RETURNS void LANGUAGE plpgsql VOLATILE AS $body$
DECLARE
v_table_n name := quote_ident(v_table_name);
BEGIN
EXECUTE 'DROP TRIGGER IF EXISTS '||v_table_n||'__count_insert ON '||v_table_n;
EXECUTE 'DROP TRIGGER IF EXISTS '||v_table_n||'__count_delete ON '||v_table_n;
EXECUTE 'DROP TRIGGER IF EXISTS '||v_table_n||'__count_truncate ON '||v_table_n;
DELETE FROM eadb_table_stats WHERE table_name = v_table_name;
END
$body$;
--Beispiel:
SELECT easydb_create_count_trigger('t');
INSERT INTO t(data) VALUES(1);
INSERT INTO t(data) VALUES(1);
INSERT INTO t(data) VALUES(1);
DELETE FROM t WHERE id = (SELECT min(id) FROM t);
SELECT easydb_get_count('t');
TRUNCATE t;
SELECT easydb_get_count('t');
SELECT easydb_remove_count_trigger('t');
SELECT easydb_get_count('t');
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment