gistfile1.txt

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');