#!/usr/bin/perl
# demonstration for the postgres trigger performance problem
# christian mock <cm@coretec.at> 2002-08-12
use strict;
use DBI;
use Time::HiRes qw(gettimeofday tv_interval);

my ($dh,$du,$dp,$db) = qw(db.host.name db_user db_pass db_name);
my ($nrows,$step) = (5000,5000);
my ($use_trigger,$fake_trigger) = (1,0);
my $cycles = 10;

my $dbh = DBI->connect("DBI:Pg:dbname=$db;host=$dh", $du, $dp) ||
  die $DBI::errstr;

# create tables:
$dbh->do("drop table input");
$dbh->do(qq{CREATE TABLE input (
  id integer,
  c1 integer,
  c2 integer,
  c3 integer)});
$dbh->do("drop table output");
$dbh->do(qq{CREATE TABLE output (
  id integer,
  c1 integer,
  c2 integer,
  c3 integer)});
$dbh->do("drop table stats");
$dbh->do("drop sequence stats_stat_id_seq");
$dbh->do(qq{CREATE TABLE stats (
  stat_id serial,
  c1 integer,
  c2 integer,
  c3 integer,
  count integer,
  UNIQUE(c1,c2,c3))});

if($use_trigger) {
  if($fake_trigger) {
    $dbh->do(qq{CREATE OR REPLACE FUNCTION stats_trigger_f() RETURNS OPAQUE AS '
DECLARE
BEGIN
 RETURN NEW;
END;
' LANGUAGE 'plpgsql'});
  } else {
    $dbh->do(qq{CREATE OR REPLACE FUNCTION stats_trigger_f() RETURNS OPAQUE AS '
DECLARE
 result RECORD;
BEGIN
 SELECT INTO result stat_id FROM stats WHERE c1 = NEW.c1 AND c2 = NEW.c2
  AND c3 = NEW.c3;
 IF NOT FOUND THEN
  INSERT INTO stats (c1,c2,c3,count) VALUES(NEW.c1,NEW.c2,NEW.c3,1);
 ELSE
  UPDATE stats SET count = count+1 WHERE c1 = NEW.c1 AND c2 = NEW.c2
   AND c3 = NEW.c3;
 END IF;
 RETURN NEW;
END;
' LANGUAGE 'plpgsql'});
  }
  $dbh->do(qq{CREATE TRIGGER stats_trigger BEFORE INSERT ON output
FOR EACH ROW EXECUTE PROCEDURE stats_trigger_f()});
}

insert_testdata($nrows); # number of rows to randomly generate

my $tt = 0;
for (my $c = 0; $c < $cycles; $c++) {
  for (my $i = 0; $i < $nrows; $i+=$step) {
    my $last = ($i+$step-1 > $nrows) ? $nrows : $i+$step-1;
    my $t0 = [gettimeofday];
    my $n = $dbh->do(qq{INSERT INTO output SELECT * FROM input
WHERE id >= ? AND id <= ?}, {}, $i, $last);
    my $dt = tv_interval($t0);
    $tt+=$dt;
    printf("%5d %7.3f sec %4d rps %4d stats\n", $n, $dt, $n/$dt,
	   ($dbh->selectall_arrayref("SELECT count(*) FROM stats"))->[0]->[0]);
  };
}
printf("total %5d %7.3f sec %4d rps\n", $nrows*$cycles, $tt, $nrows/$tt);


sub insert_testdata {
  my($count) = @_;
  my $sth = $dbh->prepare("INSERT INTO input VALUES(?,?,?,?)");
  for(my $i = 0; $i < $count; $i++) {
    $sth->execute($i, int(rand(10)), int(rand(10)),
		  int(rand(10)));
  }
}