Skip Menu |
 

This queue is for tickets about the DBD-Pg CPAN distribution.

Report information
The Basics
Id: 79164
Status: rejected
Priority: 0/
Queue: DBD-Pg

People
Owner: Nobody in particular
Requestors: gallantm [...] indiana.edu
Cc:
AdminCc:

Bug Information
Severity: Important
Broken in: 2.15.1
Fixed in: (no value)



Subject: error: create database
Download (untitled) / with headers
text/plain 2.6k
Using RHEL 6 (2.6.32-220) connecting to a pgsql 8.3 database. Using perl.5.10.1 dbd.2.15.1 (from rpm) and perl-dbi.1.609 (from rpm). Trying to move admin functions in to a perl script. Errors are generated when attempting to issue a "create database" sql command. The error indicates that the sql can not run inside a transaction. I have autocommit set to 0 so it should not be a transaction block. Code: sub create_db { my ($user) = @_; # create user $user with password '$pass' connection limit 10 # create database $user with owner $user # grant connect on database $user to $user my $dbname = "admin"; my $host = "db"; my $port = "5433"; my $results = ""; my $results_err = ""; my $debug = 1; print "create_db:1 start create db\n" if $debug; # dbh - database handle $dbh = DBI->connect("dbi:Pg:dbname=$dbname;host=$host;port=$port", 'gallantm', 'bigtree', {AutoCommit => 0}); if (!defined $dbh) { print "create_db:can not connect to $host\n"; $results_err = $results_err."create_db:can not connect to $host\n"; } # use DBD::Pg qw(:pg_types); # my $sql = "create database $user with owner $user"; print "create_db:2:$sql\n" if $debug; $dbh->do($sql); $results_err = $results_err."create_db:2.1:".$dbh->errstr.":".$dbh- Show quoted text
>err().":".$DBI::errstr."\n";
$sth->execute(); $rv = $sth->err; if ($rv) { print "create_db:4:".$sth->errstr.":".$DBI::errstr."\n"; $results_err = $results_err."create_db:4:".$sth->errstr.":".$DBI::errstr."\n"; } else { while ( @row = $sth->fetchrow()) { print "create_db:row:$#row\n" if $debug; $results = $row[0]; } } $rv = $dbh->disconnect; $rv = $sth->err; if ($rv) { print "create_db:5:".$sth->errstr."\n"; $results_err = $results_err."create_db:5:".$sth->errstr."\n"; } return $results_err; } Errors: create_db:1 start create db create_db:2:create database test with owner test DBD::Pg::db do failed: ERROR: CREATE DATABASE cannot run inside a transaction block at ./mkdbacct line 105. DBD::Pg::st execute failed: Cannot call execute on a disconnected database handle at ./mkdbacct line 114. create_db:4:Cannot call execute on a disconnected database handle:Cannot call execute on a disconnected database handle create_db:5:Cannot call execute on a disconnected database handle main:6:create_db:2.1:ERROR: CREATE DATABASE cannot run inside a transaction block:7:ERROR: CREATE DATABASE cannot run inside a transaction block create_db:4:Cannot call execute on a disconnected database handle:Cannot call execute on a disconnected database handle
Download (untitled) / with headers
text/plain 612b
On 2012-08-23 09:00:17, gallantm@indiana.edu wrote: Show quoted text
> Using RHEL 6 (2.6.32-220) connecting to a pgsql 8.3 database. Using > perl.5.10.1 dbd.2.15.1 > (from rpm) and perl-dbi.1.609 (from rpm). > > Trying to move admin functions in to a perl script. Errors are > generated when attempting to > issue a "create database" sql command. The error indicates that the > sql can not run inside a > transaction. I have autocommit set to 0 so it should not be a > transaction block.
Setting AutoCommit to 0 *is* starting a transaction. Don't do that. Set AutoCommit to 1 and each statement will be its own transaction.


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.