Thread: Newbie to Postgres - Urgent query
Hello all,
I am trying to use DBI in perl to connect to my Postgres database. I am able to connect to the database but when any further queries are made using the database handle, I see the following message in my error logs and no results are generated -
"NOTICE: current transaction is aborted, queries ignored until end of transaction block"
Please help on what could be going wrong.
Here's a part of my code :
$dbh = DBI->connect(DBI:Pg:dbname=dbname, $user_name, $password, { RaiseError => 0, PrintError => 0, AutoCommit => 0});
my $results = "select * from user_regs order by id desc";
my $ids = $dbh->prepare($results);
my $sth = $ids->execute;
$iderr = $ids->errstr;
if ($iderr) {
print STDERR "Couldn't get usercount from user_regs";
exit 0;
}
my @resultrows = $ids->fetchrow_array;
my $rowcount = scalar(@resultrows);
print STDERR "Rowcount: $rowcount\n";
exit 0;
Need urgent response.
Regards,
Ritu
I am trying to use DBI in perl to connect to my Postgres database. I am able to connect to the database but when any further queries are made using the database handle, I see the following message in my error logs and no results are generated -
"NOTICE: current transaction is aborted, queries ignored until end of transaction block"
Please help on what could be going wrong.
Here's a part of my code :
$dbh = DBI->connect(DBI:Pg:dbname=dbname, $user_name, $password, { RaiseError => 0, PrintError => 0, AutoCommit => 0});
my $results = "select * from user_regs order by id desc";
my $ids = $dbh->prepare($results);
my $sth = $ids->execute;
$iderr = $ids->errstr;
if ($iderr) {
print STDERR "Couldn't get usercount from user_regs";
exit 0;
}
my @resultrows = $ids->fetchrow_array;
my $rowcount = scalar(@resultrows);
print STDERR "Rowcount: $rowcount\n";
exit 0;
Need urgent response.
Regards,
Ritu
---------------------------------------------------------------- NETCORE SOLUTIONS *** Ph: +91 22 5662 8000 Fax: +91 22 5662 8134 MailServ and FlexiMail: Messaging Solutions: http://netcore.co.in Pragatee: Integrated Server-Software Suite: http://www.pragatee.com Emergic Freedom: Server-centric Computing: http://www.emergic.com BlogStreet: Blog Profiles and RSS Ecosystem: http://blogstreet.com Deeshaa: Rural Development: http://www.deeshaa.com Rajesh Jain's Weblog on Technology: http://www.emergic.org ----------------------------------------------------------------
On Friday 16 January 2004 09:18, Ritu Khetan wrote: > Hello all, > > I am trying to use DBI in perl to connect to my Postgres database. I > am able to connect to the database but when any further queries are made > using the database handle, I see the following message in my error logs > and no results are generated - > > "NOTICE: current transaction is aborted, queries ignored until end of > transaction block" 1. Check the return-values of your calls (e.g. connect, exectute...) 2. Turn on statement logging in PG to see what is happening. If the return values and the logs don't seem to help, post both back here and we'll see what we can do. -- Richard Huxton Archonet Ltd
Hello Richard,
How do I switch on statement logging...?
postmaster --help doesn't talk of any such switch.
Regards,
Ritu
On Fri, 2004-01-16 at 15:25, Richard Huxton wrote:
How do I switch on statement logging...?
postmaster --help doesn't talk of any such switch.
Regards,
Ritu
On Fri, 2004-01-16 at 15:25, Richard Huxton wrote:
On Friday 16 January 2004 09:18, Ritu Khetan wrote: > Hello all, > > I am trying to use DBI in perl to connect to my Postgres database. I > am able to connect to the database but when any further queries are made > using the database handle, I see the following message in my error logs > and no results are generated - > > "NOTICE: current transaction is aborted, queries ignored until end of > transaction block" 1. Check the return-values of your calls (e.g. connect, exectute...) 2. Turn on statement logging in PG to see what is happening. If the return values and the logs don't seem to help, post both back here and we'll see what we can do.
---------------------------------------------------------------- NETCORE SOLUTIONS *** Ph: +91 22 5662 8000 Fax: +91 22 5662 8134 MailServ and FlexiMail: Messaging Solutions: http://netcore.co.in Pragatee: Integrated Server-Software Suite: http://www.pragatee.com Emergic Freedom: Server-centric Computing: http://www.emergic.com BlogStreet: Blog Profiles and RSS Ecosystem: http://blogstreet.com Deeshaa: Rural Development: http://www.deeshaa.com Rajesh Jain's Weblog on Technology: http://www.emergic.org ----------------------------------------------------------------
On Friday 16 January 2004 16:03, Ritu Khetan wrote: > Hello Richard, > > How do I switch on statement logging...? > > postmaster --help doesn't talk of any such switch. Check options in postgresql.conf. Shridhar
Hi,
No such option found. There is the "S" switch for silent mode which is off by default anyways.
Regards,
Ritu
On Fri, 2004-01-16 at 16:34, Shridhar Daithankar wrote:
No such option found. There is the "S" switch for silent mode which is off by default anyways.
Regards,
Ritu
On Fri, 2004-01-16 at 16:34, Shridhar Daithankar wrote:
On Friday 16 January 2004 16:03, Ritu Khetan wrote: > Hello Richard, > > How do I switch on statement logging...? > > postmaster --help doesn't talk of any such switch. Check options in postgresql.conf. Shridhar ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
---------------------------------------------------------------- NETCORE SOLUTIONS *** Ph: +91 22 5662 8000 Fax: +91 22 5662 8134 MailServ and FlexiMail: Messaging Solutions: http://netcore.co.in Pragatee: Integrated Server-Software Suite: http://www.pragatee.com Emergic Freedom: Server-centric Computing: http://www.emergic.com BlogStreet: Blog Profiles and RSS Ecosystem: http://blogstreet.com Deeshaa: Rural Development: http://www.deeshaa.com Rajesh Jain's Weblog on Technology: http://www.emergic.org ----------------------------------------------------------------
Have you read the documentation about configuring the postgres server: http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=runtime.html Actually there is a very fine suite of documentation on that site. HTH, Csaba. On Fri, 2004-01-16 at 12:13, Ritu Khetan wrote: > Hi, > > No such option found. There is the "S" switch for silent mode which is > off by default anyways. > > Regards, > Ritu > On Fri, 2004-01-16 at 16:34, Shridhar Daithankar wrote: > > On Friday 16 January 2004 16:03, Ritu Khetan wrote: > > > Hello Richard, > > > > > > How do I switch on statement logging...? > > > > > > postmaster --help doesn't talk of any such switch. > > > > Check options in postgresql.conf. > > > > Shridhar > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > > ---------------------------------------------------------------- > > NETCORE SOLUTIONS *** Ph: +91 22 5662 8000 Fax: +91 22 5662 8134 > > MailServ and FlexiMail: Messaging Solutions: http://netcore.co.in > > Pragatee: Integrated Server-Software Suite: http://www.pragatee.com > > Emergic Freedom: Server-centric Computing: http://www.emergic.com > > BlogStreet: Blog Profiles and RSS Ecosystem: http://blogstreet.com > > Deeshaa: Rural Development: http://www.deeshaa.com > > Rajesh Jain's Weblog on Technology: http://www.emergic.org > ----------------------------------------------------------------
Hi all,
I discovered the problem, AutoCommit is off by default and I did not commit the transaction..it works fine now.
Thanks,
Ritu
On Fri, 2004-01-16 at 16:56, Csaba Nagy wrote:
I discovered the problem, AutoCommit is off by default and I did not commit the transaction..it works fine now.
Thanks,
Ritu
On Fri, 2004-01-16 at 16:56, Csaba Nagy wrote:
Have you read the documentation about configuring the postgres server: http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=runtime.html Actually there is a very fine suite of documentation on that site. HTH, Csaba. On Fri, 2004-01-16 at 12:13, Ritu Khetan wrote: > Hi, > > No such option found. There is the "S" switch for silent mode which is > off by default anyways. > > Regards, > Ritu > On Fri, 2004-01-16 at 16:34, Shridhar Daithankar wrote: > > On Friday 16 January 2004 16:03, Ritu Khetan wrote: > > > Hello Richard, > > > > > > How do I switch on statement logging...? > > > > > > postmaster --help doesn't talk of any such switch. > > > > Check options in postgresql.conf. > > > > Shridhar > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > > ---------------------------------------------------------------- > > NETCORE SOLUTIONS *** Ph: +91 22 5662 8000 Fax: +91 22 5662 8134 > > MailServ and FlexiMail: Messaging Solutions: http://netcore.co.in > > Pragatee: Integrated Server-Software Suite: http://www.pragatee.com > > Emergic Freedom: Server-centric Computing: http://www.emergic.com > > BlogStreet: Blog Profiles and RSS Ecosystem: http://blogstreet.com > > Deeshaa: Rural Development: http://www.deeshaa.com > > Rajesh Jain's Weblog on Technology: http://www.emergic.org > ----------------------------------------------------------------
---------------------------------------------------------------- NETCORE SOLUTIONS *** Ph: +91 22 5662 8000 Fax: +91 22 5662 8134 MailServ and FlexiMail: Messaging Solutions: http://netcore.co.in Pragatee: Integrated Server-Software Suite: http://www.pragatee.com Emergic Freedom: Server-centric Computing: http://www.emergic.com BlogStreet: Blog Profiles and RSS Ecosystem: http://blogstreet.com Deeshaa: Rural Development: http://www.deeshaa.com Rajesh Jain's Weblog on Technology: http://www.emergic.org ----------------------------------------------------------------
On Friday 16 January 2004 16:43, Ritu Khetan wrote: > Hi, > > No such option found. There is the "S" switch for silent mode which is > off by default anyways. In my local installation I have following relevant options. #log_connections = false #log_duration = false #log_pid = false #log_statement = false #log_timestamp = false #log_hostname = false #log_source_port = false Anyways, the problem is solved, as you pointed in other mail. Shridhar
Hi,
Oh ok..my conf didnt show the log_statement option. Thanks, should be helpful some other time.
Regards,
Ritu
On Fri, 2004-01-16 at 17:39, Shridhar Daithankar wrote:
Oh ok..my conf didnt show the log_statement option. Thanks, should be helpful some other time.
Regards,
Ritu
On Fri, 2004-01-16 at 17:39, Shridhar Daithankar wrote:
On Friday 16 January 2004 16:43, Ritu Khetan wrote: > Hi, > > No such option found. There is the "S" switch for silent mode which is > off by default anyways. In my local installation I have following relevant options. #log_connections = false #log_duration = false #log_pid = false #log_statement = false #log_timestamp = false #log_hostname = false #log_source_port = false Anyways, the problem is solved, as you pointed in other mail. Shridhar ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
---------------------------------------------------------------- NETCORE SOLUTIONS *** Ph: +91 22 5662 8000 Fax: +91 22 5662 8134 MailServ and FlexiMail: Messaging Solutions: http://netcore.co.in Pragatee: Integrated Server-Software Suite: http://www.pragatee.com Emergic Freedom: Server-centric Computing: http://www.emergic.com BlogStreet: Blog Profiles and RSS Ecosystem: http://blogstreet.com Deeshaa: Rural Development: http://www.deeshaa.com Rajesh Jain's Weblog on Technology: http://www.emergic.org ----------------------------------------------------------------
On Jan 16, 2004, at 1:18, Ritu Khetan wrote:
> $dbh = DBI->connect(DBI:Pg:dbname=dbname, $user_name, $password, {
> RaiseError => 0, PrintError => 0, AutoCommit => 0});
I've managed to avoid perl for a very long time now, but doesn't
RaiseError => 0 turn off the feature that causes DBI to actively tell
you when you've done something wrong rather than you having to ask
every time you do something if you did it correctly and it was
successful (which you're also not doing)?
Perhaps it's just style, but I can't think of any case where I'd want
normal execution to continue upon DB error without explicit code to
handle the situation. Likewise, I certainly wouldn't want to bother
writing extra code every time I require something to succeed.
--
Dustin Sallings
>>>>> "Dustin" == Dustin Sallings <dustin@spy.net> writes: Dustin> Perhaps it's just style, but I can't think of any case where Dustin> I'd want normal execution to continue upon DB error without explicit Dustin> code to handle the situation. Likewise, I certainly wouldn't want to Dustin> bother writing extra code every time I require something to succeed. My DBI standard method is to call RaiseError => 1, PrintError => 0. It just seems to make for easier to read code. -- Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095 <merlyn@stonehenge.com> <URL:http://www.stonehenge.com/merlyn/> Perl/Unix/security consulting, Technical writing, Comedy, etc. etc. See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!
Hello!
I want to execute some dynamic query from for loop in plpgsql
But i don't know how to add vaules like boolean or integer. With text type
works.
Here is my query. As you can see I tried with ::text but with no luck.
EXECUTE ''UPDATE directory_entry set
"caption" = '' || quote_literal(tmp.caption) || '',
"description" = '' || quote_literal(tmp.description) || '',
"keywords" = '' || quote_literal(tmp.keywords) || '',
"url" = '' || quote_literal(tmp.url) || '',
"is_company" = '' || tmp.is_company::text || '',
"is_novelty" = '' || tmp.is_novelty::text || '',
"expire" = '' || tmp.expire::text || '',
"modified" = ''''now'''' where id_entry='' || tmp.id_entry;
tia
Uros