Pl/perlu, DBD::Oracle and Sys:SigAction problem - crash of backend process - Mailing list pgsql-hackers

Greetings!

I've encounter repeatable postgres crash.

I developed set of pl/perlu functions to import data from oracle to postgres.

there is one function that initializes session variables ($_SHARED) .

Everything was very effective and fast but... when Oracle host disconnected plperlu function that executed some statement on oracla hangs. I found solution of making connection timeout when using DBD::Oracle like:

if(!defined $_SHARED{IMPORT_GET_ORA_DB})
{

$ENV{NLS_LANG}="POLISH_POLAND.AL32UTF8";
$ENV{NLS_NCHAR}="AL32UTF8";
$_SHARED{IMPORT_GET_ORA_DB}= sub
{
use DBI;
my $dbh = $_SHARED{IMPORT_ORA_DB};
use Sys::SigAction qw( set_sig_handler );

if ( (!$dbh) )
{
my $tnsAlias = $_SHARED{IMPORT_ORA_TNS_ALIAS};
my $login = $_SHARED{IMPORT_ORA_LOGIN};
my $pass = $_SHARED{IMPORT_ORA_PASSWORD};

my $wasTimeout=0;
eval {

Sys::SigAction::set_sig_handler( 'ALRM' ,sub{$wasTimeout=1;die;} ,{mask=>[qw( ALRM ) ]  } );
alarm(5);
$dbh =DBI->connect("dbi:Oracle:".$tnsAlias,$login,$pass,{ RaiseError => 1, AutoCommit =>0, ora_envhp=> 0,InactiveDestroy => 0 });
$dbh->do("alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'");
$dbh->do("alter session set NLS_NUMERIC_CHARACTERS='. '");       
alarm(0);
};
alarm(0);
elog DEBUG,"was timeout:$wasTimeout";
if ( $wasTimeout )
{
elog ERROR,"timeout when connecting to oracle";
}
if ($@)
{
elog WARNING, "some error:$@";
}
if (not $dbh)
{
elog ERROR, "Can't connect to oracle:".DBI::errstr;
}

$_SHARED{IMPORT_ORA_DB}=$dbh;
}     
return $dbh;
};
}
$_SHARED{IMPORT_GET_ORA_DB}();

So in other plperlu functions i get the database handler like:

my $dbh = $_SHARED{IMPORT_GET_ORA_DB}();

It worked good on tests so I made something like (in function that initializes shared(session) variables)

if (!exists($_SHARED{EXECUTE}))
{
$_SHARED{EXECUTE}=sub
{
use DBI;
my($sth,$time)=@_;

my $wasTimeout=0;
my $ok;
eval {

Sys::SigAction::set_sig_handler( 'ALRM' ,sub{$wasTimeout =1;$sth->cancel();$sth->finish();die;} ,{mask=>[qw( ALRM ) ] } );
alarm($time);
$ok = $sth->execute();
alarm(0);
}; #signal handler is reset when $h goes out of scope
alarm(0);
if ($wasTimeout)
{
elog ERROR,"timeout when executing statement on oracle";
}
if ($@)
{
elog ERROR,"some error:$@";
}
if (not $ok)
{
elog ERROR, "can't execute statement:".$DBI::errstr;
}
}
}


So i can use:

my $dbh = $_SHARED{IMPORT_GET_ORA_DB}();
my $query="delete from table";
my $sth = $_SHARED{PREPARE_CACHED}($dbh,$query,5);
$_SHARED{EXECUTE}($sth,5);

When there is no connection to oracle, $_SHARED{IMPORT_GET_ORA_DB}(); will create this connection and it will be in $_SHARED{IMPORT_ORA_DB} variable so there will be only one connection to oracle.
$_SHARED{PREPARE_CACHED} is similar to $_SHARED{EXECUTE}, there is implemented timeout to
$dbh->prepare_cached($query) .

Suppose that i have table A on oracle and postgres. There is some modified and new rows on oracle. I want to import them to postgres so I type in pgadmin:

select * from function_that_imports_table_a();

and have all rows fat updated,inserted (function function_that_imports_table_a() is making select on table B that has info bout changed and new rows of table A, getting them from oracle and making inserts).

Everything works fine but when I make 2 sessions in pgadmin and type in each:

select * from import_tow_from_ora();

I get  :,,Program exited with code 0377'' from using gdb on first process

and ,,Program received signal SIGQUIT, Quit'' from second process.

The backtrace of second process is:
#0  0xb7f97410 in ?? ()                    
#1  0xbfc118c8 in ?? ()                    
#2  0x00002000 in ?? ()                    
#3  0xb6059fa0 in ?? ()                    
#4  0xb7e8f883 in __read_nocancel () from /lib/libc.so.6
#5  0x081bd1f2 in FileRead ()                          
#6  0x081ca59f in mdread ()                            
#7  0x081caaba in smgrread ()                          
#8  0x081ba4d8 in ReadBuffer ()                        
#9  0x08099440 in _bt_getbuf ()                        
#10 0x0809a903 in _bt_getroot ()                       
#11 0x0809ccf5 in _bt_search ()                        
#12 0x08098952 in _bt_doinsert ()                      
#13 0x0809bd98 in btinsert ()                          
#14 0x0824e0c0 in FunctionCall6 ()                     
#15 0x08096435 in index_insert ()                      
#16 0x081513de in ExecInsertIndexTuples ()             
#17 0x081492d0 in ExecutorRun ()                       
#18 0x0815ff6e in _SPI_execute_plan ()                 
#19 0x08160298 in SPI_execute_plan ()                  
#20 0xb35f60b8 in exec_stmt_execsql () from /opt/pg-8.2.9/lib/postgresql/plpgsql.so
#21 0xb35f7c73 in exec_stmts () from /opt/pg-8.2.9/lib/postgresql/plpgsql.so      
#22 0xb35f975b in exec_stmt_if () from /opt/pg-8.2.9/lib/postgresql/plpgsql.so
#23 0xb35f7986 in exec_stmts () from /opt/pg-8.2.9/lib/postgresql/plpgsql.so
#24 0xb35f975b in exec_stmt_if () from /opt/pg-8.2.9/lib/postgresql/plpgsql.so
#25 0xb35f7986 in exec_stmts () from /opt/pg-8.2.9/lib/postgresql/plpgsql.so
#26 0xb35f7753 in exec_stmt_block () from /opt/pg-8.2.9/lib/postgresql/plpgsql.so
#27 0xb35fa133 in plpgsql_exec_function () from /opt/pg-8.2.9/lib/postgresql/plpgsql.so
#28 0xb35f0218 in plpgsql_call_handler () from /opt/pg-8.2.9/lib/postgresql/plpgsql.so
#29 0x0814bb1c in ExecMakeFunctionResult ()
#30 0x0814a256 in ExecProject ()
#31 0x0815a45d in ExecResult ()
#32 0x081498c5 in ExecProcNode ()
#33 0x08148c7b in ExecutorRun ()
#34 0x0815ff6e in _SPI_execute_plan ()
#35 0x08160298 in SPI_execute_plan ()
#36 0xb35f522b in exec_run_select () from /opt/pg-8.2.9/lib/postgresql/plpgsql.so
#37 0xb35f8281 in exec_stmts () from /opt/pg-8.2.9/lib/postgresql/plpgsql.so
#38 0xb35f7352 in exec_stmt_block () from /opt/pg-8.2.9/lib/postgresql/plpgsql.so
#39 0xb35f7942 in exec_stmts () from /opt/pg-8.2.9/lib/postgresql/plpgsql.so
#40 0xb35f901b in exec_stmt_fors () from /opt/pg-8.2.9/lib/postgresql/plpgsql.so
#41 0xb35f79e6 in exec_stmts () from /opt/pg-8.2.9/lib/postgresql/plpgsql.so
#42 0xb35f7352 in exec_stmt_block () from /opt/pg-8.2.9/lib/postgresql/plpgsql.so
#43 0xb35fa133 in plpgsql_exec_function () from /opt/pg-8.2.9/lib/postgresql/plpgsql.so
#44 0xb35f0218 in plpgsql_call_handler () from /opt/pg-8.2.9/lib/postgresql/plpgsql.so
#45 0x0814eb89 in ExecMakeTableFunctionResult ()
#46 0x08159f80 in FunctionNext ()
#47 0x0814fac4 in ExecScan ()
#48 0x08159f09 in ExecFunctionScan ()
#49 0x08149920 in ExecProcNode ()
#50 0x08148c7b in ExecutorRun ()
#51 0x081d1ba0 in PortalRunSelect ()
#52 0x081d2ad9 in PortalRun ()
#53 0x081ce967 in exec_simple_query ()
#54 0x081cfe88 in PostgresMain ()
#55 0x081a9816 in BackendRun ()
#56 0x081a8fef in BackendStartup ()
#57 0x081a706a in ServerLoop ()
#58 0x081a650a in PostmasterMain ()
#59 0x081680a0 in main ()


When i make this test without ,,timeout'' implementation (without using system signals) everything works fine.  I made some traces in functions and process crashes when is trying update or insert row that has already been updated or inserted by other process (when there is no ,,timeout'' code one process is waiting to end of the other - so is working correctly). So I suppose it can has something to do with locks mechanism.

Maybe some of you encounter such a problem.

I'll be very gratefull of any of your help.

Sorry for my childish english.

Regards

Tomasz Olszak