Обсуждение: after postgres upgrade - ERROR: current transaction is aborted

Hi,

I have had a site working for the last 2 years and have had no problems
until at the weekend I replace my database server with a newer one. The
database migration went like a dream and I had the whole db changed over
in 1 hour.

Since the upgrade I have been getting the following error message
sporadically.

ERROR:  current transaction is aborted, queries ignored until end of
transaction block


I have the following setup

Webserver:
Apache 1.3.27 (Redhat 7.2)
PHP 4.1.2

Database server (old): Postgres 7.1.3
Database server (new): Postgres 7.3.4

The PHP scripts have not changed. I am using PEAR::DB for database
access. I think it is the PEAR::DB that is actually making the
transactions because some times the error message some times gets
generated when I don't even have a transaction. Not too sure about that
though.

The funny thing is, when I first start up the web-server I don't get any
error messages and the site carries on working fine just as it did when
using the old database server.

However, after about an hour. I suddenly get 10 errors a minute from my
site (average of course) with the majority of pages still accessing
fine. After a bit more time, this doubles to 20 errors a minute. If I
carry on leaving the site, I eventually get error messages every second.
The error message seem to be generated from completely unrelated bits of
my code.

When I restart Apache the error messages go away and the site functions
as normal.

My theory is, since I am using persistent connections in PHP, a
connection is some how becoming...unstable...for the want of a better
word. That connection then returns an error on every single request.

To put my theory to test, I have turned off the persistent connections
on my site and everything appears to be working fine now. I now need to
work out where to go from here as my server load is high and I would
prefer to use persistent connections.

Has anyone experienced any similar problems with changing Postgres
versions? Do I need to upgrade my PHP on the web-server as the RedHat
postgres the PHP was built against was Postgres 7.1.3? Anyone have any
suggestions on how I can fix this?

Regards,

--
Abdul-Wahid Paterson

Lintrix Networking & Comms. ltd.     Web: http://www.lintrix.net/
Tel: +44 (0) 870 285 4703            Mobile: +44 (0)7971 506177
Fax: 0870 133 0433                   Email/Jabber: aw@lintrix.net
--------------------------------------------------------------------
Web-Hosting  |  Development  |  Security  |  Consultancy  |  Domains
--------------------------------------------------------------------

While I would recommend you upgrade your php to be built against 7.3,
before that you can use one of the new 7.3 error messages to find out
where things are going awry. in your postgresql.conf set
log_min_error_statement = error. this will cause the offending sql
statement to be written to your logs, with this you can narrow down
where in your web code your problems are being created, and better
attack the problem at that point.

Robert Treat

On Mon, 2003-09-15 at 14:18, Abdul-Wahid Paterson wrote:
> Hi,
>
> I have had a site working for the last 2 years and have had no problems
> until at the weekend I replace my database server with a newer one. The
> database migration went like a dream and I had the whole db changed over
> in 1 hour.
>
> Since the upgrade I have been getting the following error message
> sporadically.
>
> ERROR:  current transaction is aborted, queries ignored until end of
> transaction block
>
>
> I have the following setup
>
> Webserver:
> Apache 1.3.27 (Redhat 7.2)
> PHP 4.1.2
>
> Database server (old): Postgres 7.1.3
> Database server (new): Postgres 7.3.4
>
> The PHP scripts have not changed. I am using PEAR::DB for database
> access. I think it is the PEAR::DB that is actually making the
> transactions because some times the error message some times gets
> generated when I don't even have a transaction. Not too sure about that
> though.
>
> The funny thing is, when I first start up the web-server I don't get any
> error messages and the site carries on working fine just as it did when
> using the old database server.
>
> However, after about an hour. I suddenly get 10 errors a minute from my
> site (average of course) with the majority of pages still accessing
> fine. After a bit more time, this doubles to 20 errors a minute. If I
> carry on leaving the site, I eventually get error messages every second.
> The error message seem to be generated from completely unrelated bits of
> my code.
>
> When I restart Apache the error messages go away and the site functions
> as normal.
>
> My theory is, since I am using persistent connections in PHP, a
> connection is some how becoming...unstable...for the want of a better
> word. That connection then returns an error on every single request.
>
> To put my theory to test, I have turned off the persistent connections
> on my site and everything appears to be working fine now. I now need to
> work out where to go from here as my server load is high and I would
> prefer to use persistent connections.
>
> Has anyone experienced any similar problems with changing Postgres
> versions? Do I need to upgrade my PHP on the web-server as the RedHat
> postgres the PHP was built against was Postgres 7.1.3? Anyone have any
> suggestions on how I can fix this?
>
> Regards,
>
> --
> Abdul-Wahid Paterson
>
> Lintrix Networking & Comms. ltd.     Web: http://www.lintrix.net/
> Tel: +44 (0) 870 285 4703            Mobile: +44 (0)7971 506177
> Fax: 0870 133 0433                   Email/Jabber: aw@lintrix.net
> --------------------------------------------------------------------
> Web-Hosting  |  Development  |  Security  |  Consultancy  |  Domains
> --------------------------------------------------------------------

--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

On Mon, 15 Sep 2003, Abdul-Wahid Paterson wrote:

> I have had a site working for the last 2 years and have had no problems
> until at the weekend I replace my database server with a newer one. The
> database migration went like a dream and I had the whole db changed over
> in 1 hour.
>
> Since the upgrade I have been getting the following error message
> sporadically.
>
> ERROR:  current transaction is aborted, queries ignored until end of
> transaction block

[...]

> To put my theory to test, I have turned off the persistent connections
> on my site and everything appears to be working fine now. I now need to
> work out where to go from here as my server load is high and I would
> prefer to use persistent connections.

<crystal ball>
You made some error when importing the new database and now you have
some sequences with their counter not greater than the last sequence
number. So you get some insert errors within transactions, and then you
cannot issue any inserts or updates in that transaction until you do a
rollback.
</crystal ball>


--
PGP/GPG Key-ID:
http://blackhole.pca.dfn.de:11371/pks/lookup?op=get&search=0xB5A1AFE1

I have kinda put my finger on the problem. It seems that a transaction
was crashing out somewhere in my code and a rollback was not being done
(I have not found the line of code yet) and the next PHP script to take
on the connection was therefore getting a message saying that the
current transaction was aborted.

I changed my PHP connection setting to not use persistent connections
and the problem was fixed. Although, that was not a good solution for me
as I have high sever load.

I then wrote a php shutdown function to always do a rollback at the end
of the script. I switched on the persistent connections again, and
everything was fine. Thus concluding that the problem was indeed a
failed transaction that had not been rollback. (At least this will be
good enough until I can sift through the thousands of lines of code
looking for transaction code that has a missing rollback).

The thing that still puzzles me is this: If you remember from my earlier
post, the only thing I have changed here is the database server (from
postgres 7.1.3 to 7.3.4). What I don't understand is why did 7.1.3 never
cause a problem with the failed transactions not being rolledback?
Postgres surely doesn't know that it is a new PHP script using the
connection as all Postgres knows about is the Apache HTTP process
holding the connection open. Can anyone shed some light on the
difference between version? Or perhaps there is something else in
Postgres configuration that would change the above behaviour.

Best regards,

Abdul-Wahid

On Mon, 2003-09-15 at 19:48, Robert Treat wrote:
> While I would recommend you upgrade your php to be built against 7.3,
> before that you can use one of the new 7.3 error messages to find out
> where things are going awry. in your postgresql.conf set
> log_min_error_statement = error. this will cause the offending sql
> statement to be written to your logs, with this you can narrow down
> where in your web code your problems are being created, and better
> attack the problem at that point.
>
> Robert Treat
>
> On Mon, 2003-09-15 at 14:18, Abdul-Wahid Paterson wrote:
> > Hi,
> >
> > I have had a site working for the last 2 years and have had no problems
> > until at the weekend I replace my database server with a newer one. The
> > database migration went like a dream and I had the whole db changed over
> > in 1 hour.
> >
> > Since the upgrade I have been getting the following error message
> > sporadically.
> >
> > ERROR:  current transaction is aborted, queries ignored until end of
> > transaction block
> >
> >
> > I have the following setup
> >
> > Webserver:
> > Apache 1.3.27 (Redhat 7.2)
> > PHP 4.1.2
> >
> > Database server (old): Postgres 7.1.3
> > Database server (new): Postgres 7.3.4
> >
> > The PHP scripts have not changed. I am using PEAR::DB for database
> > access. I think it is the PEAR::DB that is actually making the
> > transactions because some times the error message some times gets
> > generated when I don't even have a transaction. Not too sure about that
> > though.
> >
> > The funny thing is, when I first start up the web-server I don't get any
> > error messages and the site carries on working fine just as it did when
> > using the old database server.
> >
> > However, after about an hour. I suddenly get 10 errors a minute from my
> > site (average of course) with the majority of pages still accessing
> > fine. After a bit more time, this doubles to 20 errors a minute. If I
> > carry on leaving the site, I eventually get error messages every second.
> > The error message seem to be generated from completely unrelated bits of
> > my code.
> >
> > When I restart Apache the error messages go away and the site functions
> > as normal.
> >
> > My theory is, since I am using persistent connections in PHP, a
> > connection is some how becoming...unstable...for the want of a better
> > word. That connection then returns an error on every single request.
> >
> > To put my theory to test, I have turned off the persistent connections
> > on my site and everything appears to be working fine now. I now need to
> > work out where to go from here as my server load is high and I would
> > prefer to use persistent connections.
> >
> > Has anyone experienced any similar problems with changing Postgres
> > versions? Do I need to upgrade my PHP on the web-server as the RedHat
> > postgres the PHP was built against was Postgres 7.1.3? Anyone have any
> > suggestions on how I can fix this?
> >
> > Regards,
> >
> > --
> > Abdul-Wahid Paterson
> >
> > Lintrix Networking & Comms. ltd.     Web: http://www.lintrix.net/
> > Tel: +44 (0) 870 285 4703            Mobile: +44 (0)7971 506177
> > Fax: 0870 133 0433                   Email/Jabber: aw@lintrix.net
> > --------------------------------------------------------------------
> > Web-Hosting  |  Development  |  Security  |  Consultancy  |  Domains
> > --------------------------------------------------------------------
--
Abdul-Wahid Paterson

Lintrix Networking & Comms. ltd.     Web: http://www.lintrix.net/
Tel: +44 (0) 870 285 4703            Mobile: +44 (0)7971 506177
Fax: 0870 133 0433                   Email/Jabber: aw@lintrix.net
--------------------------------------------------------------------
Web-Hosting  |  Development  |  Security  |  Consultancy  |  Domains
--------------------------------------------------------------------

my guess is that the query causing the error in 7.3 actually was valid
syntax in 7.1, so the query never errored out and the transaction never
had to be rolled back. quickest way to find out which query is to make
the mods to postgresql.conf i mentioned below and get it from your
server logs.

Robert Treat

On Tue, 2003-09-16 at 12:23, Abdul-Wahid Paterson wrote:
> I have kinda put my finger on the problem. It seems that a transaction
> was crashing out somewhere in my code and a rollback was not being done
> (I have not found the line of code yet) and the next PHP script to take
> on the connection was therefore getting a message saying that the
> current transaction was aborted.
>
> I changed my PHP connection setting to not use persistent connections
> and the problem was fixed. Although, that was not a good solution for me
> as I have high sever load.
>
> I then wrote a php shutdown function to always do a rollback at the end
> of the script. I switched on the persistent connections again, and
> everything was fine. Thus concluding that the problem was indeed a
> failed transaction that had not been rollback. (At least this will be
> good enough until I can sift through the thousands of lines of code
> looking for transaction code that has a missing rollback).
>
> The thing that still puzzles me is this: If you remember from my earlier
> post, the only thing I have changed here is the database server (from
> postgres 7.1.3 to 7.3.4). What I don't understand is why did 7.1.3 never
> cause a problem with the failed transactions not being rolledback?
> Postgres surely doesn't know that it is a new PHP script using the
> connection as all Postgres knows about is the Apache HTTP process
> holding the connection open. Can anyone shed some light on the
> difference between version? Or perhaps there is something else in
> Postgres configuration that would change the above behaviour.
>
> Best regards,
>
> Abdul-Wahid
>
> On Mon, 2003-09-15 at 19:48, Robert Treat wrote:
> > While I would recommend you upgrade your php to be built against 7.3,
> > before that you can use one of the new 7.3 error messages to find out
> > where things are going awry. in your postgresql.conf set
> > log_min_error_statement = error. this will cause the offending sql
> > statement to be written to your logs, with this you can narrow down
> > where in your web code your problems are being created, and better
> > attack the problem at that point.
> >
> > Robert Treat
> >
> > On Mon, 2003-09-15 at 14:18, Abdul-Wahid Paterson wrote:
> > > Hi,
> > >
> > > I have had a site working for the last 2 years and have had no
> problems
> > > until at the weekend I replace my database server with a newer one.
> The
> > > database migration went like a dream and I had the whole db changed
> over
> > > in 1 hour.
> > >
> > > Since the upgrade I have been getting the following error message
> > > sporadically.
> > >
> > > ERROR:  current transaction is aborted, queries ignored until end of
> > > transaction block
> > >
> > >
> > > I have the following setup
> > >
> > > Webserver:
> > > Apache 1.3.27 (Redhat 7.2)
> > > PHP 4.1.2
> > >
> > > Database server (old): Postgres 7.1.3
> > > Database server (new): Postgres 7.3.4
> > >
> > > The PHP scripts have not changed. I am using PEAR::DB for database
> > > access. I think it is the PEAR::DB that is actually making the
> > > transactions because some times the error message some times gets
> > > generated when I don't even have a transaction. Not too sure about
> that
> > > though.
> > >
> > > The funny thing is, when I first start up the web-server I don't get
> any
> > > error messages and the site carries on working fine just as it did
> when
> > > using the old database server.
> > >
> > > However, after about an hour. I suddenly get 10 errors a minute from
> my
> > > site (average of course) with the majority of pages still accessing
> > > fine. After a bit more time, this doubles to 20 errors a minute. If
> I
> > > carry on leaving the site, I eventually get error messages every
> second.
> > > The error message seem to be generated from completely unrelated
> bits of
> > > my code.
> > >
> > > When I restart Apache the error messages go away and the site
> functions
> > > as normal.
> > >
> > > My theory is, since I am using persistent connections in PHP, a
> > > connection is some how becoming...unstable...for the want of a
> better
> > > word. That connection then returns an error on every single request.
> > >
> > > To put my theory to test, I have turned off the persistent
> connections
> > > on my site and everything appears to be working fine now. I now need
> to
> > > work out where to go from here as my server load is high and I would
> > > prefer to use persistent connections.
> > >
> > > Has anyone experienced any similar problems with changing Postgres
> > > versions? Do I need to upgrade my PHP on the web-server as the
> RedHat
> > > postgres the PHP was built against was Postgres 7.1.3? Anyone have
> any
> > > suggestions on how I can fix this?
> > >
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

Abdul-Wahid Paterson <aw@lintrix.net> writes:
> I have kinda put my finger on the problem. It seems that a transaction
> was crashing out somewhere in my code and a rollback was not being done
> (I have not found the line of code yet) and the next PHP script to take
> on the connection was therefore getting a message saying that the
> current transaction was aborted.
> [snip]
> The thing that still puzzles me is this: If you remember from my earlier
> post, the only thing I have changed here is the database server (from
> postgres 7.1.3 to 7.3.4). What I don't understand is why did 7.1.3 never
> cause a problem with the failed transactions not being rolledback?

Most likely, the transaction didn't fail in the first place with 7.1 ---
ie, the failure is due to some version-to-version incompatibility
between 7.1 and 7.3.  Check the 7.2 and 7.3 release notes for some
ideas what to look for.

            regards, tom lane

On 16 Sep 2003, Abdul-Wahid Paterson wrote:

> I have kinda put my finger on the problem. It seems that a transaction
> was crashing out somewhere in my code and a rollback was not being done
> (I have not found the line of code yet) and the next PHP script to take
> on the connection was therefore getting a message saying that the
> current transaction was aborted.
>
> ...
>
> The thing that still puzzles me is this: If you remember from my earlier
> post, the only thing I have changed here is the database server (from
> postgres 7.1.3 to 7.3.4). What I don't understand is why did 7.1.3 never
> cause a problem with the failed transactions not being rolledback?

Were they failing before? I should imagine there's sufficient differences in
between 7.1.x and 7.3.x to start causing you problems if you've just blindly
assumed  all queries will work exactly the same.

> Postgres surely doesn't know that it is a new PHP script using the
> connection as all Postgres knows about is the Apache HTTP process
> holding the connection open. Can anyone shed some light on the
> difference between version? Or perhaps there is something else in
> Postgres configuration that would change the above behaviour.

Use the server logs to determine what query is failing. Simple thing is it to
start at the start of the log and the first error report you find should be
the, or at least a, problem. If not then obviously you'll have to scan the
errors until you find a query you don't expect to get an error.

--
Nigel Andrews

Abdul-Wahid Paterson wrote:
-- Start of PGP signed section.
> I have kinda put my finger on the problem. It seems that a transaction
> was crashing out somewhere in my code and a rollback was not being done
> (I have not found the line of code yet) and the next PHP script to take
> on the connection was therefore getting a message saying that the
> current transaction was aborted.
>
> I changed my PHP connection setting to not use persistent connections
> and the problem was fixed. Although, that was not a good solution for me
> as I have high sever load.

This problem of transactions staying around for another connection was
fixed in PHP, I thought.  PHP5 also has a fix to RESET all GUC variables
for persistent connection, but I thought the transaction-reset code was
already in current PHP versions.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073