473,847 Members | 2,426 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Pg + perl + apache

I'm having some odd issues, but I'm not sure exactly the cause of them,
but postgres is the component in the system that is throwing the errors,
so I thought I'd start here.

I have built a web application that uses postgres (7.3.2) as its
backend, with the front being an Apache2/mod_perl solution. I've
printed what I think the relevant versions are below.

Anway, DBD::Pg throws random errors into my logs, along the lines of
what is below (the lines beginning with SQL are debug statements from
the script, and are the lines references by the DBD error.

SQL: SELECT session_key FROM session WHERE user_id = 101 at
/home/dragon/public_html/iactweb/lib/perl/Apache/IACT/AuthUtil.pm line 38.
DBD::Pg::db selectrow_array failed: ERROR: current transaction is
aborted, queries ignored until end of transaction block at
/home/dragon/public_html/iactweb/lib/perl/Apache/IACT/AuthUtil.pm line 40.
[Thu Feb 12 18:48:13 2004] [error] [client 127.0.0.1] DBD::Pg::db
selectrow_array failed: ERROR: current transaction is aborted, queries
ignored until end of transaction block at
/home/dragon/public_html/iactweb/lib/perl/Apache/IACT/AuthUtil.pm line 40.

with the corresponding postgresql.log entry being

LOG: statement: SELECT session_key FROM session WHERE user_id = 101
ERROR: current transaction is aborted, queries ignored until end of
transaction block

This seems to randomly happen in my application, resulting in an error
page being returned to the user. However, if the user refreshes their
browser (sometimes more than once), it seems to come back fine. I have
seen it happen with DBD::Pg::db st, selectrow_array , selectrow_array ref,
and selectrow_hashr ef.

The components of the system currently are (upgraded this morning to the
latest):

DBI 1.40
DBD::Pg 1.31
Apache::DBI 0.93
Apache 2.0.48 and 2.0.47
mod_perl 1.99.11 and 1.99.12
PostgreSQL 7.4.1 and 7.3.2

I'm leaning towards blaming the interaction between DBI and Apache::DBI
(persists DB connections within apache), but I'd like to rule out
postgres as the problem, if anyone can do that conclusively. I never
disconnect from my DB handles, since Apache::DBI caches them, so I was
wondering about the whole 'ping' method. I wrote a script to exercise
one of my scripts from the command line, and ran it 10000 times without
error, so I am pretty sure that there is some interaction error going on
in the system.

If anyone has seen anything like this before (i.e. random errors in a
similar apache/mod_perl) situation, or might have any pointers as to
where to look next, let me know. What would cause postgres to return an
error for a selectrow_array , or one of those others mentioned?

Thanks
-Mike


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 22 '05 #1
6 3965
On Sat, Feb 14, 2004 at 04:35:58PM -0500, Michael L. Artz wrote:
I'm having some odd issues, but I'm not sure exactly the cause of them,
but postgres is the component in the system that is throwing the errors,
so I thought I'd start here.
<snip> with the corresponding postgresql.log entry being

LOG: statement: SELECT session_key FROM session WHERE user_id = 101
ERROR: current transaction is aborted, queries ignored until end of
transaction block
This happens when, within a transaction gets an error, you don't notice and
keep blindly sending queries.

Scroll up to find the actual error.

If it's the first transaction in a session, that means someone forgot to
clear their transaction. I guess you could fix it then by sending "abort"
as the first query.

Hope this helps,

--
Martijn van Oosterhout <kl*****@svana. org> http://svana.org/kleptog/ If the Catholic church can survive the printing press, science fiction
will certainly weather the advent of bookwarez.
http://craphound.com/ebooksneitherenorbooks.txt - Cory Doctorow


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFAL9/rY5Twig3Ge+YRAs luAJ4lj6faVhNBY 5r0p3QgfA8NWroM jgCfaPoz
2FhSCEhViJL6Z3d sRWRB0bQ=
=hemf
-----END PGP SIGNATURE-----

Nov 22 '05 #2
"Michael L. Artz" <dr****@october 29.net> writes:
Anway, DBD::Pg throws random errors into my logs, along the lines of
what is below (the lines beginning with SQL are debug statements from
the script, and are the lines references by the DBD error. LOG: statement: SELECT session_key FROM session WHERE user_id = 101
ERROR: current transaction is aborted, queries ignored until end of
transaction block


This error cannot occur unless some other error occurred previously in
the same transaction.

My bet is that you are using some sort of connection-pooling mechanism,
and are being sloppy about handing off database connections from one
Apache thread to another. Specifically, some thread is getting an error
and then letting go of the connection without rolling back the failed
transaction on the database side. The next guy who gets that connection
from the pool fails as above.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 22 '05 #3
On Saturday 14 February 2004 22:35, Michael L. Artz wrote:
(...)
If anyone has seen anything like this before (i.e. random errors in a
similar apache/mod_perl) situation, or might have any pointers as to
where to look next, let me know. What would cause postgres to return an
error for a selectrow_array , or one of those others mentioned?


Just checking, but do all your scripts have :

use strict;
use warnings;

at the top?
Ian Barwick
ba*****@gmx.net
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org

Nov 22 '05 #4
Thanks, that did help to debug the application. I found that my errors
weren't so random after all ... if I went to a page with a bad query,
then I would start getting the error.

So, basically, if I grab a DB connection and issue a bad query, then
every other query issued via that DB connection will not work because
they are in the same transaction? So the following perl snippet:

----------------------------
my $dbh = DBI->connect(@datab ase);

# invalid SQL
my $sql = "SELECT crap;";
my $sth = $dbh->prepare($sql );
$sth->execute();

$sql = "SELECT 1;";
my ($should_be_one ) = $dbh->selectrow_arra y($sql);

$dbh->disconnect() ;
-----------------------------

should throw an error for *both* queries? So I guess I need to issue a
commit after I do my queries, or else turn autocommit on.

And yes, always using strict and warnings in my scripts, and using
Apache::DBI's connection pooling to cache my DB connections.

Thanks
-Mike

Martijn van Oosterhout wrote:
On Sat, Feb 14, 2004 at 04:35:58PM -0500, Michael L. Artz wrote:

I'm having some odd issues, but I'm not sure exactly the cause of them,
but postgres is the component in the system that is throwing the errors,
so I thought I'd start here.


<snip>

with the corresponding postgresql.log entry being

LOG: statement: SELECT session_key FROM session WHERE user_id = 101
ERROR: current transaction is aborted, queries ignored until end of
transaction block


This happens when, within a transaction gets an error, you don't notice and
keep blindly sending queries.

Scroll up to find the actual error.

If it's the first transaction in a session, that means someone forgot to
clear their transaction. I guess you could fix it then by sending "abort"
as the first query.

Hope this helps,

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 22 '05 #5
You could try something like this:

Assuming you are using perl+DBI (as per your examples).

If you are using modperl:
[modperl can override the "connect to db" for connection reuse/pooling]

begin cgi web app:
connect to db
rollback
do stuff
do more stuff
commit if ok
by default rollback
disconnect.

If you are using fastcgi:
[fastcgi - explicit DB connection reuse]
connect to db
while (fastcgi connection) {
rollback
do stuff
do more stuff
commit if ok
by default rollback
}
rollback
disconnect

You probably want to do a rollback before you do stuff so that the
transaction times are correct. AFAIK Perl DBI automatically does a BEGIN
after a rollback or commit, so if the previous transaction was rolled back
or committed 1 hour ago, you'd be reusing a transaction that begun 1 hour
ago for something that's happening now.

This would cause select 'now'::timestam p or SELECT CURRENT_TIMESTA MP return
times that are 1 hour ago, which is usually not what you want.

Hope that helps,
At 12:40 AM 2/16/2004 -0500, Michael L. Artz wrote:
Thanks, that did help to debug the application. I found that my errors
weren't so random after all ... if I went to a page with a bad query, then
I would start getting the error.

should throw an error for *both* queries? So I guess I need to issue a
commit after I do my queries, or else turn autocommit on.


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

Nov 22 '05 #6

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You probably want to do a rollback before you do stuff so that the
transaction times are correct. AFAIK Perl DBI automatically does a
BEGIN after a rollback or commit


This was true until very recently, but the DBD::Pg code has been
rewritten so that we no longer hang around in a transaction, but
only issue a BEGIN when we need to, e.g. right before we execute
something. This will be in version 1.32, which should be released
to CPAN in about a week.

- --
Greg Sabino Mullane gr**@turnstep.c om
PGP Key: 0x14964AC8 200402180653

-----BEGIN PGP SIGNATURE-----

iD8DBQFAM1I+vJu QZxSWSsgRAucWAK DXkRiHX6dJRf+y3 GwPgZSZ7CvB2ACf Ygnt
QLhSuv8tvIrzs6m lcLEHxOU=
=oz4E
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 22 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
2418
by: Keith | last post by:
All: What is the difference between Perl (CGI) and PHP (Apache module)? I thought both used servers to direct the user to the appropiate Perl or PHP program in order to execute the program to interpret the user's Perl or PHP script respectively. Keith
1
1997
by: Stephan Diehl | last post by:
Is there something similar to the PERL Apache::Admin::Config module out there that is written in Python? Stephan
3
1764
by: glytchbinary | last post by:
i'm not sure of the interpreter's architecture, but i think it starts a new interpreter for every perl script someone on the system launches yes? why nto make a perl server that prioritized jobs evenly and interpreted for everyone. seems like it'd be faster.
3
2557
by: Mark Simon | last post by:
I am having a very hard time trying to write a cgi script in perl. Whenever I change the code, and call it from my form, I usually find results from an older version of the scrip, though it happens inconistently. Obviously something is being cached somewhere. An example of this weirdness is when I write a line, and the script stops working. Then I coment out the line, and the script works again. OK, so I delete the line, but the script...
3
2800
by: Tommo | last post by:
Hello All, I am a still learning so be easy on me. I am trying to get some code to work that is using JS and Perl/CGI, I am using AS Perl and an Apache Server on XP as the webserver. Can anyone take a look at the code and tell me why I am getting Apache Server errors in the code below (Part A), when I modify the code as shown in part B the errors go but the JS does not run ?? Part A.
29
3200
by: Mainlander | last post by:
An ISP I belong to uses Majordomo for their mailing list system. I'd like to encourage them to move to a system that uses a database, preferably psql which they already run on their server. Anything out there in Php?
12
2372
by: rurpy | last post by:
Is there an effcient way (more so than cgi) of using Python with Microsoft IIS? Something equivalent to Perl-ISAPI?
6
7988
by: MaiyaHolliday | last post by:
Hello, I've recently installed apache on a new computer, and cannot figure out why my site will not process any includes. (it was working on my old one) There are no errors on the page such as "include not found", rather much of the page is blank where the included menus, etc would be, and the CSS is not attached. thanks so much!!! My httpd.conf looks like this:
10
6988
by: happyse27 | last post by:
Hi All, I got this apache errors(see section A1 and A2 below) when I used a html(see section b below) to activate acctman.pl(see section c below). Section D below is part of the configuration of section c. Not sure where went wrong as the web page displayed internal server error. Also, what is the error 543? and error 2114. Where to find the list of errors in websites as it is not the standard apache error. I could not find...
0
9879
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10981
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10643
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10706
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10330
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7879
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7050
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5716
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
3
3158
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.