pl/perl autonomous transactions question - Mailing list pgsql-general

I would like to use autonomous transactions for a large batch process and I want this all encapsulated within stored procedures. I want to commit after say every 15,000 records. The only way I have found to do this is to use the perl DBI in my stored procedure to establish a new connection to the database.

1. Is there any way to tell the DBI connection to use the current credtials just with a new connection?  
2. Is there any way to get the spi call to create a new connection instead of using the connection it is called with?

One issue I see with my current DBI  solution is I need to hard code or pass in as variables the connection information.  I would prefer not to have the password lying around in plain site.  Keep in mind this is a batch process not a something I that is called manually where a user is
going to be entering their username and password in.


Any help or ideas would be great.

Below is a simple example to demonstrate.

CREATE TABLE test_values ( c1 SERIAL, c2 VARCHAR (200));

CREATE OR REPLACE FUNCTION proc_perl_test_insert() RETURNS VOID AS $$
use DBI;

my $db_host = 'localhost';
my $db_user = 'postgres';
my $db_pass = 'somepassword';
my $db_name = 'dev';

elog(NOTICE,"Executeing proc_perl_test_insert");

#Creates a new connection so that an autonomous transactions can take place independent of main transaction.
#INSERT INTO test_values (c2) VALUES ('Autonomous Transaction') will commit regardless if the calling transaction fails or is rolled back.
my $db = "DBI:PgPP:dbname=${db_name};host=${db_host}";
my $dbh=DBI->connect("DBI:PgPP:dbname=dev;host=localhost","postgres", "c21993b");
if ($dbh)
{
     my $sth = $dbh->prepare("INSERT INTO test_values (c2) VALUES ('Autonomous Transaction')");
     $sth->execute();
}

#This inserts using spi_exec_query and will only commit if the calling transaction commits.
for ($count=1; $count<2; $count++)
{
    my $query = qq{

    INSERT INTO test_values ( c2 )
    VALUES ( 'Non Autonomous Transaction' )

};
my $rv = spi_exec_query($query);
}
$$ LANGUAGE plperlu;


--Now Test the pl/perl function from psql and use a outer transaction
START TRANSACTION;
SELECT proc_perl_test_insert();
ROLLBACK TRANSACTION;
SELECT * FROM test_values;



--HERE IS MY psql commands being run as you can see it does what I want in that it commits my one statement but not the other
dev=# START TRANSACTION;
START TRANSACTION
Time: 0.000 ms
dev=# SElECT * FROM proc_perl_test_insert();
NOTICE: Running proc_perl_test_insert
proc_perl_test_insert
-----------------------

(1 row)

Time: 70.000 ms
dev=# ROLLBACK TRANSACTION;
ROLLBACK
Time: 0.000 ms
dev=# select * from test_values;
c1 | c2
---------+------------------------
2898364 | Autonomous Transaction
(1 row)

Time: 0.000 ms
dev=#

Regards,
Bob Henkel