incremental_summary.sql

incremental_summary.sql

\set ON_ERROR_STOP ON
BEGIN;
DROP SCHEMA IF EXISTS anthony_test CASCADE;
CREATE SCHEMA anthony_test;
SET SEARCH_PATH to anthony_test, public;

CREATE TABLE downloads(
    id bigint GENERATED ALWAYS AS IDENTITY primary key,
    created_at timestamptz NOT NULL DEFAULT now(),
    username text NOT NULL,
    provider text NOT NULL,
    area bigint NOT NULL,
    filesize bigint NOT NULL
);



CREATE TABLE download_summary(
    id bigint GENERATED ALWAYS AS IDENTITY primary key,
    username text NOT NULL,
    provider text NOT NULL,
    area bigint NOT NULL DEFAULT 0,
    filesize bigint NOT NULL DEFAULT 0,
    UNIQUE(username, provider)
);

-- Bootstrap download_summary with any existing data or reset the table
-- to make sure it is absolutely in sync
TRUNCATE download_summary;
INSERT INTO download_summary (username, provider, area, filesize)
SELECT username, provider, sum(area), sum(filesize) FROM downloads GROUP BY 1,2;

CREATE OR REPLACE FUNCTION downloads_trigger_func() RETURNS TRIGGER AS $$
DECLARE
BEGIN
    -- If it's a statment level insert trigger, do the insert in bulk
    IF TG_LEVEL = 'STATEMENT' and TG_OP = 'INSERT' THEN
        INSERT INTO download_summary AS d
            (username, provider, area, filesize)
            SELECT username, provider, sum(area), sum(filesize)
            FROM new_table
            GROUP BY 1, 2
        ON CONFLICT (username, provider)
        DO UPDATE SET
            area = d.area + EXCLUDED.area,
            filesize = d.filesize + EXCLUDED.filesize
        ;
        RETURN NULL;
    ELSIF TG_LEVEL = 'STATEMENT' AND TG_OP = 'TRUNCATE' THEN
        TRUNCATE download_summary;
    ELSIF TG_OP = 'INSERT' THEN
        IF NEW.area > 0 OR NEW.filesize > 0 THEN
            INSERT INTO download_summary AS d
                (username, provider, area, filesize)
                VALUES
                (NEW.username, NEW.provider, NEW.area, NEW.filesize)
            ON CONFLICT (username, provider)
            DO UPDATE SET
                area = d.area + EXCLUDED.area,
                filesize = d.filesize + EXCLUDED.filesize
            ;
        END IF;
        RETURN NEW;
    ELSIF TG_OP = 'UPDATE' THEN
        IF NEW.area != OLD.area OR NEW.filesize != OLD.filesize THEN
            UPDATE download_summary AS d SET
                area = d.area + NEW.area - OLD.area,
                filesize = d.filesize + NEW.filesize - OLD.filesize
            WHERE d.username = NEW.username AND d.provider = NEW.provider
            ;
        END IF;
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE download_summary AS d SET
            area = d.area - OLD.area,
            filesize = d.filesize - OLD.filesize
        WHERE d.username = OLD.username AND d.provider = OLD.provider
        ;
        RETURN OLD;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER downloads_row_trigger AFTER UPDATE OR DELETE
ON downloads
FOR EACH ROW EXECUTE PROCEDURE downloads_trigger_func();

CREATE TRIGGER downloads_stmt__insert_trigger AFTER INSERT
ON downloads REFERENCING NEW TABLE AS new_table
FOR EACH STATEMENT EXECUTE PROCEDURE downloads_trigger_func();

CREATE TRIGGER downloads_stmt__truncate_trigger AFTER TRUNCATE
ON downloads
FOR EACH STATEMENT EXECUTE PROCEDURE downloads_trigger_func();

INSERT INTO downloads (username, provider, area, filesize) VALUES
('a', 'a', 10, 10),
('a', 'a', 10, 10),
('a', 'a', 10, 10),
('a', 'a', 10, 10),
('b', 'a', 10, 10),
('b', 'a', 10, 10),
('b', 'a', 10, 10),
('b', 'a', 10, 10)
;

SELECT * FROM download_summary;

DELETE FROM downloads WHERE id=1;

SELECT * FROM download_summary;

UPDATE downloads SET area=30 WHERE id=2;
UPDATE downloads SET area=0 WHERE id=5;

SELECT * FROM download_summary;

INSERT INTO downloads (username, provider, area, filesize)
SELECT 'c', 'c', 10, 10 FROM generate_series(0,100000);

SELECT * FROM download_summary;


CREATE TABLE quota (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    username text,
    provider text,
    area bigint,
    filesize bigint,
    UNIQUE(username, provider)
);

INSERT INTO quota (username, provider, area, filesize)
VALUES
('a','a',1000,1000),
('b','b',1000,1000),
('c','c',10000,10000)
;

CREATE OR REPLACE FUNCTION download_summary_trigger_func() RETURNS TRIGGER AS $$
DECLARE
q quota%ROWTYPE;
BEGIN
IF EXISTS (SELECT 1 FROM quota WHERE
        username=NEW.username AND provider=NEW.provider AND
        area >= NEW.area AND
        filesize >= NEW.filesize
    ) THEN
    RETURN NEW;
ELSE
    SELECT * INTO q FROM quota WHERE
        username=NEW.username AND provider=NEW.provider;
    IF NOT FOUND THEN
        RAISE 'No quota exists for user %, provider %.', NEW.username, NEW.provider USING errcode='23001';
    ELSE
        RAISE 'Quota exceeded for User %, Profile % for area % filesize %.  User has used % / % area and % / % filesize.', NEW.username, NEW.provider, NEW.area, NEW.filesize, OLD.area, q.area, OLD.filesize, q.filesize USING errcode='23001';
    END IF;
    RETURN NULL;
END IF;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER download_summary_trigger BEFORE INSERT OR UPDATE ON download_summary
FOR EACH ROW EXECUTE PROCEDURE download_summary_trigger_func();

TRUNCATE downloads;

-- should all work
INSERT INTO downloads (username, provider, area, filesize) VALUES
('a', 'a', 10, 10),
('a', 'a', 10, 10),
('a', 'a', 10, 10),
('a', 'a', 10, 10),
('b', 'b', 10, 10),
('b', 'b', 10, 10),
('b', 'b', 10, 10),
('b', 'b', 10, 10)
;


COMMIT;
 -- these should all give an error
INSERT INTO downloads (username, provider, area, filesize)
SELECT 'c', 'c', 10, 10 FROM generate_series(0,100000);

INSERT INTO downloads (username, provider, area, filesize) VALUES
('a', 'a', 1001, 10)
;

INSERT INTO downloads (username, provider, area, filesize) VALUES
('d', 'd', 1001, 10)
;

UPDATE downloads SET filesize=1001 WHERE username='b' AND provider='b';

SELECT * FROM download_summary;