plpgsql function to insert or update problem
От | Andy Colson |
---|---|
Тема | plpgsql function to insert or update problem |
Дата | |
Msg-id | 4F6CC5CF.2020407@squeakycode.net обсуждение исходный текст |
Ответы |
Re: plpgsql function to insert or update problem
(Alban Hertroys <haramrae@gmail.com>)
|
Список | pgsql-general |
Hi all, I am inserting apache log into into a database. Seem to have a little problem with this function: create or replace function insert_webstat( ivhost text, iip inet, isessid text, ihittime timestamp, iurl text, istatus integer, isize integer, icompress integer, ielapsed integer, iuseragent text) returns void as $$ declare x text; vid integer; aid integer; r integer; begin if iurl = '*' then return; end if; ... skip hopefully uninteresting stuff ... ... and then .. insert into webstats(vhost, ip, sessid, hittime, url, status, size, compress, elapsed, useragent) values (vid, iip, isessid, ihittime, iurl, istatus, isize, icompress, ielapsed, aid); if iurl like '%.php' and current_timestamp - ihittime < '24 hours'::interval then update by_ip set hits = hits + 1 where vhost = vid and time = date_trunc('hour', ihittime) and ip = iip and sessid = isessid; if not found then insert into by_ip(vhost, time, ip, sessid, hits) values (vid, date_trunc('hour', ihittime), iip, isessid, 1); end if; end if; end $$ language 'plpgsql'; I have two or three web boxes that collect stats and forward them (via perl and ZeroMQ) to the stat server that is also running a perl/ZeroMQ program. The server perl script starts a transaction, and collects as many ZeroMQ messages as it can without blocking, and inserts them all into the db, then commits. So, only one connection to the database. In slight copy/paste/summarize perl: $q = $db->prepare('select insert_webstat($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)'); # this blocks and waits for a message my $msg = $sock->recv(); $db->begin_work; while (1) { my $data = $msg->data(); eval { $q->execute(@list) }; if ($@) { my $x = $db->errstr; $log->print("DB Error: $x\n[", join(",", @list), "]\n"); $q = undef; $db = undef; opendb(); last; } $msg = $sock->recv(ZMQ_NOBLOCK); if (! $msg) { $db->commit; last; } } This is not the exact code, the above is just example to try and describe without too much detail. Anyway, the problem. I get a lot of DB Error messages: DB Error: ERROR: duplicate key value violates unique constraint "by_ip_pk" DETAIL: Key (ip, sessid, "time")=(97.64.237.59, 2qggi9gcdkcaoecqg3arvo1gu7, 2012-03-23 13:00:00) already exists. CONTEXT: SQL statement "insert into by_ip(vhost, time, ip, sessid, hits) values (vid, date_trunc('hour', ihittime), iip, isessid, 1)" PL/pgSQL function "insert_webstat" line 38 at SQL statement [jasper.iowaassessors.com,97.64.237.59,2qggi9gcdkcaoecqg3arvo1gu7,23/Mar/2012:13:19:48 -0500,/getSketch.php,200,1837,,6387,Mozilla/5.0 (Windows NT 5.1) AppleWebKit/535.11 (KHTML, like Gecko) Chrome/17.0.963.83 Safari/535.11] Lets see. Server is PG 9.0.7, on Slackware 64 13.1. stats=# \d by_ip Table "public.by_ip" Column | Type | Modifiers --------+-----------------------------+----------- vhost | integer | not null time | timestamp without time zone | not null ip | inet | not null sessid | text | hits | integer | Indexes: "by_ip_pk" UNIQUE, btree (ip, sessid, "time") vhost is an integer in this table... the function turns jasper.iowaassessors.com into an integer based on a lookup table. Why is it hitting the insert statement? Why doesn't the "if not found" seem to work? Any hints would be appreciated. -Andy
В списке pgsql-general по дате отправления: