Thread: after insert trigger hangs

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

pradeep singh <lets_begin_with_me@yahoo.com> writes:
> CREATE OR REPLACE FUNCTION recalc_add_bitset()
> RETURNS trigger AS $$

>     use DBI;
>     my $dbname = 'database_name';
>     my $dbh = DBI->connect("dbi:Pg:dbname='$dbname'",
> "postgres_user_id", "password", {AutoCommit => 0,
> RaiseError=>1});

That (using DBI in a trigger) is a really bad idea in most cases.
The DBI connection is a separate transaction that (a) can't see
the uncommitted results of your transaction and (b) can have lock
conflicts against your transaction.  I suspect (b) explains the
hang, though the part of the problem you've shown us doesn't make
it clear exactly why there's a conflict.

plperl has a database access protocol, learn to use it.

            regards, tom lane