plpgsql function to insert or update problem

[Andy Colson <andy@xxxxxxxxxxxxxxx>]

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

--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general