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
----------------------------------------------------------------
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:
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:
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.
Show quoted text
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:
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:
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 = falseAnyways, 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