trigger didn't fired by using copy from command

Question

I have populate a table using the copy from command which in turn will create record in summary table. While after the copy command successfully run, I can not see any record in the summary table. Anyone can shed some light on me? Pls find the table as well as the store procedure below:-

CREATE TABLE apache_log (
        log_name character varying(255),
        line integer,
        client_address character varying(255),
        rfc1413 character varying(32),
        user_name character varying(32),
        local_time timestamp with time zone,
        log_date date,
        log_hour smallint,
        tenminute_bucket smallint,
        fiveminute_bucket smallint,
        method character varying(10),
        url character varying(8192),
        protocol character varying(10),
        status_code smallint,
        bytes_sent integer,
        referer character varying(8192),
        agent character varying(8192),
        canon_name character varying(512)
);

CREATE INDEX apache_log_local_time ON apache_log USING btree (local_time);
CREATE INDEX apache_log_client_address ON apache_log USING btree (client_address);
CREATE INDEX apache_log_user_name ON apache_log USING btree (user_name);
CREATE INDEX apache_log_canon_name ON apache_log USING btree (canon_name);
CREATE INDEX apache_log_url ON apache_log USING btree (url);
CREATE INDEX apache_log_method ON apache_log USING btree (method);
CREATE INDEX apache_log_status_code ON apache_log USING btree (status_code);
CREATE UNIQUE INDEX apache_log_name_line ON apache_log (log_name, line);

CREATE TABLE tenminute_summary (
        log_date date,
        log_hour smallint,
        bucket smallint,
        hit integer,
        bytes_sent bigint,
        status_code smallint
);

CREATE INDEX tenminute_summary_log_date_log_hour_bucket ON tenminute_summary (log_date, log_hour, bucket);
CREATE UNIQUE INDEX tenminute_summary_log_date_log_hour_bucket_status_code ON tenminute_summary (log_date, log_hour, bucket, status_code);

CREATE TABLE fiveminute_summary (
        log_date date,
        log_hour smallint,
        bucket smallint,
        hit integer,
        bytes_sent bigint,
        status_code smallint
);

CREATE INDEX fiveminute_summary_log_date_log_hour_bucket ON fiveminute_summary (log_date, log_hour, bucket);
CREATE UNIQUE INDEX fiveminute_summary_log_date_log_hour_bucket_status_code ON fiveminute_summary (log_date, log_hour, bucket, status_code);

CREATE OR REPLACE FUNCTION update_history(history_log_date date, history_log_hour smallint, history_status_code smallint, history_fiveminute_bucket smallint, history_tenminute_bucket smallint, history_fiveminute_bytes_sent bigint, history_fiveminute_hit integer, history_fiveminute_bytes_sent bigint, history_fiveminute_hit integer) RETURNS INTEGER AS
$update_history$
        BEGIN
                IF ( history_fiveminute_bucket IS NOT NULL) THEN
                        <<fiveminute_update>>
                        LOOP
                                UPDATE fiveminute_summary
                                   SET  bytes_sent = bytes_sent + history_fiveminute_bytes_sent,
                                                hit = hit + history_fiveminute_hit
                                        WHERE log_date = history_log_date AND
                                                        log_hour = history_log_hour AND
                                                        bucket = history_fiveminute_bucket AND
                                                        status_code = history_status_code;
                                EXIT fiveminute_update WHEN found;
                                BEGIN
                                        INSERT INTO fiveminute_summary (
                                                log_date,
                                                log_hour,
                                                bucket,
                                                status_code,
                                                bytes_sent,
                                                hit)
                                        VALUES (
                                                history_log_date,
                                                history_log_hour,
                                                history_fiveminute_bucket,
                                                history_status_code,
                                                history_fiveminute_bytes_sent,
                                                history_fiveminute_hit);
                                        EXIT fiveminute_update;
                                EXCEPTION
                                        WHEN UNIQUE_VIOLATION THEN
                                                -- do nothing
                                END;
                        END LOOP fiveminute_update;
                END IF;
                IF ( history_tenminute_bucket IS NOT NULL) THEN
                        <<tenminute_update>>
                        LOOP
                                UPDATE tenminute_summary
                                   SET  bytes_sent = bytes_sent + history_tenminute_bytes_sent,
                                                hit = hit + history_tenminute_hit
                                        WHERE log_date = history_log_date AND
                                                        log_hour = history_log_hour AND
                                                        bucket = history_tenminute_bucket AND
                                                        status_code = history_status_code;
                                EXIT tenminute_update WHEN found;
                                BEGIN
                                        INSERT INTO tenminute_summary (
                                                log_date,
                                                log_hour,
                                                bucket,
                                                status_code,
                                                bytes_sent,
                                                hit)
                                        VALUES (
                                                history_log_date,
                                                history_log_hour,
                                                history_tenminute_bucket,
                                                history_status_code,
                                                history_tenminute_bytes_sent,
                                                history_tenminute_hit);
                                        EXIT tenminute_update;
                                EXCEPTION
                                        WHEN UNIQUE_VIOLATION THEN
                                                -- do nothing
                                END;
                        END LOOP tenminute_update;
                END IF;
                RETURN 0;
        END;
$update_history$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION update_apache_log() RETURNS TRIGGER AS $update_apache_log$
        DECLARE
                history_log_date date := null;
                history_log_hour smallint := null;
                history_status_code smallint := null;
                history_fiveminute_bucket smallint := null;
                history_tenminute_bucket smallint := null;
                history_fiveminute_bytes_sent bigint := null;
                history_fiveminute_hit integer := null;
                history_tenminute_bytes_sent bigint := null;
                history_tenminute_hit integer := null;
                future_log_date date := null;
                future_log_hour smallint := null;
                future_status_code smallint := null;
                future_fiveminute_bucket smallint := null;
                future_tenminute_bucket smallint := null;
                future_fiveminute_bytes_sent bigint := null;
                future_fiveminute_hit integer := null;
                future_tenminute_bytes_sent bigint := null;
                future_tenminute_hit integer := null;
                dummy integer := 0;
        BEGIN
                IF (TG_OP = 'DELETE') THEN
                        history_log_date                                := OLD.log_date;
                        history_log_hour                                := OLD.log_hour;
                        history_fiveminute_bucket               := OLD.fiveminute_bucket;
                        history_tenminute_bucket                := OLD.tenminute_bucket;
                        history_status_code                             := OLD.status_code;
                        history_fiveminute_bytes_sent   := 0 - OLD.bytes_sent;
                        history_fiveminute_hit                  := -1;
                        history_tenminute_bytes_sent    := 0 - OLD.bytes_sent;
                        history_tenminute_hit                   := -1;
                        dummy:=update_history(history_log_date, history_log_hour, history_status_code, history_fiveminute_bucket, history_tenminute_bucket, history_fiveminute_bytes_sent, history_fiveminute_hit, history_fiveminute_bytes_sent, history_fiveminute_hit);
                        RETURN OLD;
                ELSIF (TG_OP = 'INSERT') THEN
                        NEW.log_date                                    := extract(date from NEW.log_date AT TIME ZONE 'GMT+8');
                        NEW.log_hour                                    := extract(hour from NEW.log_date AT TIME ZONE 'GMT+8');
                        NEW.fiveminute_bucket                   := floor(extract(minute from NEW.log_date AT TIME ZONE 'GMT+8') / 5);
                        NEW.tenminute_bucket                    := floor(extract(minute from NEW.log_date AT TIME ZONE 'GMT+8') / 10);
                        future_log_date                                 := NEW.log_date;
                        future_log_hour                                 := NEW.log_hour;
                        future_status_code                              := NEW.status_code;
                        future_fiveminute_bucket                := NEW.fiveminute_bucket;
                        future_tenminute_bucket                 := NEW.tenminute_bucket;
                        future_fiveminute_bytes_sent    := NEW.bytes_sent;
                        future_fiveminute_hit                   := 1;
                        future_tenminute_bytes_sent             := NEW.bytes_sent;
                        future_tenminute_hit                    := 1;
                        dummy:=update_history(future_log_date, future_log_hour, future_status_code, future_fiveminute_bucket, future_tenminute_bucket, future_fiveminute_bytes_sent, future_fiveminute_hit, future_fiveminute_bytes_sent, future_fiveminute_hit);
                        RETURN NEW;
                ELSIF (TG_OP = 'UPDATE') THEN
                        IF (NEW.log_date <> OLD.log_date) THEN
                                NEW.date                                        := extract(date from NEW.log_date AT TIME ZONE 'GMT+8');
                                NEW.hour                                        := extract(hour from NEW.log_date AT TIME ZONE 'GMT+8');
                                NEW.fiveminute_bucket           := floor(extract(minute from NEW.log_date AT TIME ZONE 'GMT+8') / 5);
                                NEW.tenminute_bucket            := floor(extract(minute from NEW.log_date AT TIME ZONE 'GMT+8') / 10);
                                history_log_date                        := OLD.log_date;
                                history_log_hour                        := OLD.log_hour;
                                history_fiveminute_bucket       := OLD.fiveminute_bucket;
                                history_tenminute_bucket        := OLD.tenminute_bucket;
                                history_status_code                     := OLD.status_code;
                                IF (OLD.status_code = NEW.status_code) THEN
                                        history_fiveminute_bytes_sent   := 0 - OLD.bytes_sent;
                                        history_fiveminute_hit                  := -1;
                                        history_tenminute_bytes_sent    := 0 - OLD.bytes_sent;
                                        history_tenminute_hit                   := -1;
                                        future_log_date                              := NEW.log_date;
                                        future_log_hour                              := NEW.log_hour;
                                        future_status_code                           := NEW.status_code;
                                        future_fiveminute_bucket                := NEW.fiveminute_bucket;
                                        future_tenminute_bucket                 := NEW.tenminute_bucket;
                                        future_fiveminute_bytes_sent    := NEW.bytes_sent;
                                        future_fiveminute_hit                   := 1;
                                        future_tenminute_bytes_sent             := NEW.bytes_sent;
                                        future_tenminute_hit                    := 1;
                                        dummy:=update_history(future_log_date, future_log_hour, future_status_code, future_fiveminute_bucket, future_tenminute_bucket, future_fiveminute_bytes_sent, future_fiveminute_hit, future_fiveminute_bytes_sent, future_fiveminute_hit);
                                ELSE
                                        IF (OLD.fiveminute_bucket = NEW.fiveminute_bucket AND OLD.log_date = NEW.log_date AND OLD.log_hour = OLD.log_hour) THEN
                                                history_fiveminute_bytes_sent   := NEW.bytes_sent - OLD.bytes_sent;
                                                history_tenminute_bytes_sent    := NEW.bytes_sent - OLD.bytes_sent;
                                                history_tenminute_hit                := 0;
                                        ELSE
                                                history_fiveminute_bytes_sent   := 0 - OLD.bytes_sent;
                                                history_fiveminute_hit               := -1;
                                                future_log_date                              := NEW.log_date;
                                                future_log_hour                              := NEW.log_hour;
                                                future_status_code                           := NEW.status_code;
                                                future_fiveminute_bucket             := NEW.fiveminute_bucket;
                                                future_fiveminute_bytes_sent    := NEW.bytes_sent;
                                                future_fiveminute_hit                := 1;
                                                IF (OLD.tenminute_bucket = NEW.tenminute_bucket) THEN
                                                        history_tenminute_bytes_sent := NEW.bytes_sent - OLD.bytes_sent;
                                                        history_tenminute_hit                := 0;
                                                ELSE
                                                        history_tenminute_bytes_sent := 0 - OLD.bytes_sent;
                                                        history_tenminute_hit                := -1;
                                                        future_tenminute_bucket              := NEW.tenminute_bucket;
                                                        future_tenminute_bytes_sent          := NEW.bytes_sent;
                                                        future_tenminute_hit                 := 1;
                                                END IF;
                                                dummy:=update_history(future_log_date, future_log_hour, future_status_code, future_fiveminute_bucket, future_tenminute_bucket, future_fiveminute_bytes_sent, future_fiveminute_hit, future_fiveminute_bytes_sent, future_fiveminute_hit);
                                        END IF;
                                END IF;
                        ELSE
                                history_log_date                                := OLD.log_date;
                                history_log_hour                                := OLD.log_hour;
                                history_status_code                             := OLD.status_code;
                                history_fiveminute_bucket               := OLD.fiveminute_bucket;
                                history_tenminute_bucket                := OLD.tenminute_bucket;
                                IF (OLD.status_code <> NEW.status_code) THEN
                                        history_fiveminute_bytes_sent   := 0 - OLD.bytes_sent;
                                        history_fiveminute_hit                  := -1;
                                        history_tenminute_bytes_sent    := 0 - OLD.bytes_sent;
                                        history_tenminute_hit                   := -1;
                                        future_log_date                              := NEW.log_date;
                                        future_log_hour                              := NEW.log_hour;
                                        future_status_code                           := NEW.status_code;
                                        future_fiveminute_bucket                := NEW.fiveminute_bucket;
                                        future_tenminute_bucket                 := NEW.tenminute_bucket;
                                        future_fiveminute_bytes_sent            := NEW.bytes_sent;
                                        future_fiveminute_hit                   := 1;
                                        future_tenminute_bytes_sent             := NEW.bytes_sent;
                                        future_tenminute_hit                    := 1;
                                        dummy:=update_history(future_log_date, future_log_hour, future_status_code, future_fiveminute_bucket, future_tenminute_bucket, future_fiveminute_bytes_sent, future_fiveminute_hit, future_fiveminute_bytes_sent, future_fiveminute_hit);
                                ELSIF (OLD.bytes_sent <> NEW.bytes_sent) THEN
                                        history_fiveminute_bytes_sent   := NEW.bytes_sent - OLD.bytes_sent;
                                        history_tenminute_bytes_sent    := NEW.bytes_sent - OLD.bytes_sent;
                                END IF;
                        END IF;
                        dummy:=update_history(history_log_date, history_log_hour, history_status_code, history_fiveminute_bucket, history_tenminute_bucket, history_fiveminute_bytes_sent, history_fiveminute_hit, history_fiveminute_bytes_sent, history_fiveminute_hit);
                        RETURN NEW;
                END IF;
                RETURN NULL;
        END;
$update_apache_log$ LANGUAGE plpgsql;

CREATE TRIGGER update_apache_log
        BEFORE INSERT OR UPDATE OR DELETE ON apache_log
        FOR EACH ROW EXECUTE PROCEDURE update_apache_log();

Answer 1

The function update_history uses two parameters with the same name twice:

ERROR: parameter name "history_fiveminute_bytes_sent" used more than once
SQL status:42P13

See:

CREATE OR REPLACE FUNCTION update_history(
    history_log_date date, 
    history_log_hour smallint, 
    history_status_code smallint, 
    history_fiveminute_bucket smallint, 
    history_tenminute_bucket smallint, 
    history_fiveminute_bytes_sent bigint, <=== See errormessage
    history_fiveminute_hit integer,            <=== And this one as well
    history_fiveminute_bytes_sent bigint, <===
    history_fiveminute_hit integer             <===
) RETURNS INTEGER AS

PostgreSQL 9.0 beta doesn't like this and it doesn't make sense. Older versions might not complain but might have the same problems with execution. Did you check the errorlogs?

And raise in both functions a notice, just to see if the trigger is activated.

RAISE NOTICE 'function X is doing something';

Answer 2

Thx, in fact I found a lot of error while Postgresql 8.4 didn't complaint at all. Anyway, I have give up the store procedure approach and to populate the table using sql directly since I am doing batch update onto the table in a very adhoc manner and poppulating the table using sql is much more effectient in terms of time spent on the whole process. Anyway, thx very much for your help.