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

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(at)nasby(dot)net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)