plpgsql function to insert or update problem - Mailing list 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