Thread: pl/perl autonomous transactions question

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

On Sep 25, 2006, at 3:05 PM, Bob wrote:
> 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.

You might be able to use ident authentication. There's also
the .pgpass file.

Unfortunately, there's no way right now to handle the authentication
automatically, though that would be nice to have.

BTW, you should take a look at contrib/dblink. It'd probably be more
performant than a perl procedure would be, among other benefits.
--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

On Monday 25 September 2006 15:05, Bob wrote:
> 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?
>

I guess the depends on where you draw the line between credentials and
connection information.

> 2. Is there any way to get the spi call to create a new connection instead
> of using the connection it is called with?
>

no... that is after all the whole point of spi.

> 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.
>

One option is to use dbi-link, which will store the connection information in
it's own table, so you only pass in a reference to the particular connection
inside your function.  You can then control permissions on this table more
granularly.  Taking that a step farther would be creating specific functions
to handle the posting of the autonomous transactions and then calling those
inside your functions.

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL