after insert trigger hangs - Mailing list pgsql-general

hello,

i am using linux 8.0 along with postgres sql 8.0.
here is the schema of the db.

create table user_list (
        id      serial primary key,
        name    character varying (100) not null,
        login_name      character varying (100) not
null,
        password        character varying (100) not
null,
        cryptpassword character varying (100) not
null,
        bitset  numeric(20,0) not null
);

drop table role_list cascade;
create table role_list (
        id      serial primary key,
        name    character varying (100) not null,
        bitset  numeric(20,0) not null
);

drop table roles_users;
create table roles_users (
        role_id integer references role_list(id),
        user_id integer references user_list(id)
);




CREATE OR REPLACE FUNCTION recalc_add_bitset()
RETURNS trigger AS $$

    my $user_id=$_TD->{'new'}{'user_id'};
    my $role_id=$_TD->{'new'}{'role_id'};

    use DBI;
    my $dbname = 'database_name';
    my $dbh = DBI->connect("dbi:Pg:dbname='$dbname'",
"postgres_user_id", "password", {AutoCommit => 0,
RaiseError=>1});
    my $sth = $dbh->prepare("select bitset from
role_list where id = ? ");
    $sth->execute($role_id);
    my ($bitset) = $sth->fetchrow_array;
    $sth->finish;


    my $command = "update user_list set
bitset=bitset+$bitset where user_list.id=$user_id";
    $sth = $dbh->prepare($command);
    $sth->execute;
    $sth->finish;
    $dbh->disconnect;


   return;
$$ LANGUAGE plperlu;


CREATE TRIGGER trecalc_add_bitset
    AFTER INSERT  ON public.roles_users
    FOR EACH ROW EXECUTE PROCEDURE
recalc_add_bitset();

when i try to insert any record in **roles_users**
table it should add the bitset of the that role from
role_list table into user_list table.

but this trigger hangs at the update command.

anybody have any suggestion ?

pradeep singh

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com