Failing code looks like:
PERFORM bytes FROM usage_snapshot WHERE year = syear AND month = smonth AND day = sday AND hour = shour AND username = usaname AND class = NEW.class FOR UPDATE;
IF NOT FOUND THEN
-- FAIL!!
INSERT INTO usage_snapshot(username, year, month, day, hour, bytes, class) values(usaname, syear, smonth, sday, shour, tally, NEW.class);
ELSE
UPDATE usage_snapshot SET bytes = (bytes+tally) WHERE year = syear AND month = smonth AND day = sday AND hour = shour AND username = usaname AND class = NEW.class;
END IF;
Sample barf:
Aug 22 16:01:52 radiuz postgres[12071]: [8-1] ERROR: duplicate key value violates unique constraint "there_can_be_only_one"
Aug 22 16:01:52 radiuz postgres[12071]: [8-2] DETAIL: Key (username, year, month, day, hour, class)=(user@xxx.zz, 2011, 8, 22, 16, 0x4e5331) already exists.
Aug 22 16:01:52 radiuz postgres[12071]: [8-3] CONTEXT: SQL statement "INSERT INTO usage_snapshot(username, year, month, day, hour, bytes, class) values(usaname, syear, smonth, sday, shour, tally, NEW.class)"
Aug 22 16:01:52 radiuz postgres[12071]: [8-4] #011PL/pgSQL function "consumeunits" line 49 at SQL statement
Aug 22 16:01:52 radiuz postgres[12071]: [8-5] STATEMENT: UPDATE radacct SET AcctStopTime = ('2011-08-22 16:01:52'::timestamp - '0'::interval), AcctUpdateTime = now(), AcctSessionTime = CASE WHEN '616' = '' THEN (EXTRACT(EPOCH FROM ('2011-08-22 16:01:52'::TIMESTAMP WITH TIME ZONE - AcctStartTime::TIMESTAMP WITH TIME ZONE - '0'::INTERVAL)))::BIGINT ELSE NULLIF('616','')::BIGINT END, AcctInputOctets = (('0'::bigint << 32) + '1482588'::bigint), AcctOutputOctets = (('0'::bigint << 32) + '3238223'::bigint), AcctTerminateCause = 'Port-Error', AcctStopDelay = 0, FramedIPAddress = NULLIF('1.1.1.1', '')::inet, ConnectInfo_stop = '' WHERE AcctSessionId = '1/0/0/197.109_06BDF097' AND UserName = 'user@xxx.zz' AND NASIPAddress = '2.2.2.2' AND AcctStopTime IS NULL