Re: Strange INSERT phenomenon with key constraints

On Wed, 2 May 2001, Joel Burton wrote:

> On Wed, 2 May 2001, Lukas Ertl wrote:
>
> > Is this a bug, or am I just blind stupid?
>
> I don't know.
>
> Why don't you post your script?

Ok, here it is:

#!/usr/bin/perl -w

use DBI;
use strict;

$|++;

print "Script started: ", scalar localtime, "\n";

my %config = (
max_foo => 1000,
max_bar => 20000,
max_baz => 500,
);

my $dbh = DBI->connect(
"dbi:Pg:dbname=foobarbaz",
"XXX",
"XXX",
{ RaiseError => 1 }
);

&populate_foo();
&populate_baz();
&populate_bar();

$dbh->disconnect;

print "Script finished: ", scalar localtime, "\n";

sub populate_foo {
my $sth = $dbh->prepare("INSERT INTO foo (foo) VALUES(?)");

for (1 .. $config{max_foo}) {
my $foo = '';
for (1 .. 5) {
$foo = $foo . join '', (' ', 0..9, 'A'..'Z', 'a'..'z')
[rand 63, rand 63, rand 63, rand 63, rand 63];
}
$foo .= $foo;
$sth->execute($foo);
}
}

sub populate_baz {
my $sth = $dbh->prepare("INSERT INTO baz (baz) VALUES(?)");

for (1 .. $config{max_baz}) {
my $baz = '';
for (1 .. 5) {
$baz = $baz . join '', (' ', 0..9, 'A'..'Z', 'a'..'z')
[rand 63, rand 63, rand 63, rand 63, rand 63];
}
$baz .= $baz;
$sth->execute($baz);
}
}

sub populate_bar {

for (1 .. $config{max_bar}) {

my $foo_id = int(rand $config{max_foo});
my $baz_id = int(rand $config{max_baz});
my $parent = int(rand $_);

$foo_id = ($foo_id == 0) ? 1 : $foo_id;
$baz_id = ($baz_id == 0) ? 1 : $baz_id;
$parent = ($parent == 0) ? 1 : $parent;

my $bar = '';
for (1 .. 5) {
$bar = $bar . join '', (' ', 0..9, 'A'..'Z', 'a'..'z')
[rand 63, rand 63, rand 63, rand 63, rand 63];
}
$bar .= $bar;

if (rand > 0.3) {

print qq(
foo_id: $foo_id
baz_id: $baz_id
parent: $parent
bar: $bar
Current: $_

);
$dbh->do(
"INSERT INTO bar (date, foo_id, baz_id, bar, parent)
VALUES(NOW(),?,?,?,?)", undef,
$foo_id, $baz_id, $bar, $parent
);

} else {

print qq(
foo_id: $foo_id
baz_id: $baz_id
bar: $bar
Current: $_

);
$dbh->do(
"INSERT INTO bar (date, foo_id, baz_id, bar)
VALUES (NOW(),?,?,?)", undef,
$foo_id, $baz_id, $bar
);

}
}
}

regards,
le

--
Lukas Ertl eMail: l(dot)ertl(at)univie(dot)ac(dot)at
WWW-Redaktion Tel.: (+43 1) 4277-14073
Zentraler Informatikdienst (ZID) Fax.: (+43 1) 4277-9140
der Universität Wien