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.ertl@univie.ac.at
WWW-Redaktion                       Tel.:  (+43 1) 4277-14073
Zentraler Informatikdienst (ZID)    Fax.:  (+43 1) 4277-9140
der Universität Wien