Search Postgresql Archives

plpgsql function to insert or update problem

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]



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


[Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Programming PHP]     [Kernel Newbies]     [PHP Classes]     [Find Someone Nice]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

Add to Google Powered by Linux