Re: Why adding BEFORE TRIGGER affect client CPU? - Mailing list pgsql-general
Adrian Klaver <adrian.klaver@aklaver.com> writes:
> What is the client server and what is it doing?
> What is the client server and what is it doing?
Our client creates a temp table
CREATE TEMP TABLE myraw(LIKE raw INCLUDING DEFAULTS)
We load data with a copy
COPY myraw (transferid, pollgrpid, date, data, rrdtypeid, error ) FROM STDIN WITH DELIMITER '|'
We do some checking on the data in the temp table
SELECT rawdate, raw_maint(rawdate) AS checked FROM (SELECT date_trunc('minute', date) AS rawdate FROM myraw GROUP BY rawdate) AS foo;
Then we save the data into the permanent location
SELECT transferid, pollgrpid, process_lastpoll(ROW(myraw.*)::raw) AS processed FROM myraw ORDER BY transferid
Our change was in the process_lastpoll() function. We went from
IF cntr THEN
oval := calc_last_cntr(ptype, pgid, ds, pdate, data);
ELSE
oval := data;
END IF;
-- Insert data into table
-- RAISE DEBUG 'Process: dsnum=%, polltype=%, cntr?=%, pdate=%, data=%, oval=%', dsnum, ptype, cntr, pdate, data, oval;
UPDATE lastpoll SET polltype=ptype, timeslot=ts, lasttime=pdate, lastval=data, outval=oval, error=err WHERE pollgrpid=pgid AND dsnum=ds;
IF found THEN
RETURN true;
END IF;
BEGIN
INSERT INTO lastpoll (timeslot, pollgrpid, dsnum, polltype, lasttime, lastval, outval, error) VALUES (ts, pgid, ds, ptype, pdate, data, oval, err);
RETURN true;
EXCEPTION WHEN unique_violation THEN
-- Don't do anything
END;
to this
IF NOT cntr THEN
oval := data;
END IF;
-- Insert data into table
-- RAISE DEBUG 'Process: dsnum=%, polltype=%, cntr?=%, pdate=%, data=%, oval=%', dsnum, ptype, cntr, pdate, data, oval;
UPDATE lastpoll SET polltype=ptype, timeslot=ts, lasttime=pdate, lastval=data, outval=oval, error=err WHERE pollgrpid=pgid AND dsnum=ds;
IF found THEN
RETURN true;
END IF;
BEGIN
INSERT INTO lastpoll (timeslot, pollgrpid, dsnum, polltype, lasttime, lastval, outval, error) VALUES (ts, pgid, ds, ptype, pdate, data, oval, err);
RETURN true;
EXCEPTION WHEN unique_violation THEN
-- Don't do anything
END;
The calc_last_cntr() was replaced with the trigger.
from:
CREATE OR REPLACE FUNCTION calc_last_cntr(ptype text, pgid int4, ds int4, pdate timestamp with time zone, data double precision) RETURNS double precision AS $$
DECLARE
mcurr RECORD;
res double precision;
dwindow int4;
BEGIN
SELECT lasttime, lastval INTO mcurr FROM lastpoll WHERE pollgrpid=pgid AND dsnum=ds;
IF mcurr IS NULL THEN
-- RAISE DEBUG 'No previous data found for pgid=%', pgid;
return NULL;
END IF;
-- Calculate the counter rate
-- Check for div by 0
dwindow := EXTRACT(EPOCH FROM (pdate - mcurr.lasttime));
IF dwindow = 0 THEN
RAISE WARNING '(%) Time difference is zero (% - %)', current_database(), pdate, mcurr.lasttime;
return NULL;
END IF;
res := (data - mcurr.lastval)/dwindow;
IF res < 0 THEN
-- RAISE DEBUG 'Counter loop for pgid=%', pgid;
return data/dwindow;
END IF;
return res;
END;
$$ LANGUAGE plpgsql;
to:
CREATE OR REPLACE FUNCTION cntr_lastpoll() RETURNS trigger AS $$
DECLARE
dwindow int4;
BEGIN
-- Log new value into history if has a date
IF NEW.outval IS NULL THEN
-- Calculate counter value for this DS.
dwindow := EXTRACT(EPOCH FROM (NEW.lasttime - OLD.lasttime));
IF dwindow = 0 THEN
RAISE WARNING '(%) Time difference is zero (% - %)', current_database(), NEW.lasttime, OLD.lasttime;
return NEW;
END IF;
NEW.outval := (NEW.lastval - OLD.lastval)/dwindow;
IF NEW.outval < 0 THEN
NEW.outval := NEW.lastval/dwindow;
END IF;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- Create Trigger to calculate counter values if needed.
CREATE TRIGGER lastpoll_counter BEFORE UPDATE ON lastpoll
FOR EACH ROW EXECUTE PROCEDURE cntr_lastpoll();
On Fri, Jan 18, 2019 at 2:01 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
what uses that value client-side, exactly?
The outval number is later read looking for values outside of a thresholds. I did not think about it affecting selects later in the code. All in all everything worked fine, but since the client box is an AWS EC2 instance, it started eating all of our CPU credits.
iGLASS Networks
www.iglass.net
www.iglass.net
Attachment
pgsql-general by date: