Cleaning up aborted transactions

I've been working my way through learning PostgreSQL (7.3.2) and PHP=20
(4.3.0) and have been really pleased how things have been coming=20
together. However, I have a question about handling transactions with=20
PostgreSQL and PHP.

Before I figured out how to successfully implement a transaction=20
through PHP, I bungled it quite a few times, in that the transaction=20
got aborted. Unfortunately, due to my inexperience, the COMMIT=20
statement didn't get sent after it was aborted=97the PHP script just died.

After I finally figured out how to do transactions without crashing and=20
burning (or at least sending a COMMIT if it did abort), I would still=20
get SQL errors saying that the current SQL couldn't be processed=20
because a transaction had been aborted. Now, I know for a fact that the=20
scripts I was running then weren't issuing transactions. And it was=20
very sporadic. Sometimes I'd just be running the same script that had=20
just run successfully=97no changes, nothing occurring in the interim=97and=
=20
I'd get the "transaction aborted" SQL error.

I'm wondering if PHP is cycling through a number of different=20
connections to use to get to the database. A few of them had been=20
aborted and not committed, so when PHP selected that connection,=20
Postgres would send the transaction aborted error.

How do I clean up these aborted transactions? Of course, prevention is=20
better than cure, but we all get colds sometimes. :)

I've since restarted postmaster and the problem seems like it went=20
away. Scripts running fine. But I'd like to know how to fix this in the=20
future (without restrarting postmaster) when it happens again.

Thanks for any advice!=20

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

http://archives.postgresql.org

Re: Cleaning up aborted transactions

The correct way of working with transactions is to use:

BEGIN
....sql commands
COMMIT or ROLLBACK

So I would use this for example:

pg_connect($connection);
pg_query($connection,"BEGIN;");
$insert="INSERT INTO table VALUES (2,7,5);
$result=pg_query($connection,$insert);
if(!$result){
pg_query($connection,"ROLLBACK");
//Something went wrong with the insert so we rollback and nothing
changes in the db
}else{
pg_query($connection,"COMMIT");
// If everything went all right, then we commit the changes
}
pg_close($connection);

Of course, the interesting thing comes when you have several operations
(inserts, deletes or updates) between begin and commit, this way either you
make all the changes or make none, that's the cool thing about transactions.
In each operation just check whether the result was valid or not. If ANY of
them was invalid, rollback and none of the changes will take effect.

I don't know if this answer your question...

Adrian Tineo



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

http://archives.postgresql.org

Re: Cleaning up aborted transactions

On Tuesday, Jun 10, 2003, at 17:05 Asia/Tokyo, Adrian Tineo wrote:
> pg_connect($connection);
> pg_query($connection,"BEGIN;");
> $insert="INSERT INTO table VALUES (2,7,5);
> $result=pg_query($connection,$insert);
> if(!$result){
> pg_query($connection,"ROLLBACK");
> //Something went wrong with the insert so we rollback and nothing
> changes in the db
> }else{
> pg_query($connection,"COMMIT");
> // If everything went all right, then we commit the changes
> }
> pg_close($connection);


Thanks, Adrian, for the illustration, especially the instance with
ROLLBACK. I've figured out this sequence now.

What I'm wondering about is what happens when this sequence isn't
properly followed and the script quits before issuing a ROLLBACK or a
COMMIT. Obviously it's not a good situation and one to be avoided.

But, if it does happen, what are the alternatives? How do I finish off
that transaction that hasn't been properly finished? I'm assuming
restarting postmaster will do it, but I'm wondering if there's any
other way.

I haven't been able to find anything about this after googling.
Obviously the vast majority of people have their ducks in a row. :)

Thanks again, Adrian. I appreciate the response.

Carefully closing my transactions,

Michael


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Re: Cleaning up aborted transactions

Hi all,

Just so I have this straight, because I've been wondering the same,
if someone on my web site does a SELECT and then clicks on the browser
stop or gets click happy hitting one or many other links on the same
web page then the 1st SELECT and all other interrupted SELECT's should
get rolled back if each query starts with a BEGIN; because there is a
disconnect. Correct?

What about with persistant connections?

Greg


Rod Taylor wrote:
> On Tue, 2003-06-10 at 05:21, Michael Glaesemann wrote:
>
>>On Tuesday, Jun 10, 2003, at 17:05 Asia/Tokyo, Adrian Tineo wrote:
>>
>>>pg_connect($connection);
>>>pg_query($connection,"BEGIN;");
>>>$insert="INSERT INTO table VALUES (2,7,5);
>>>$result=pg_query($connection,$insert);
>>>if(!$result){
>>> pg_query($connection,"ROLLBACK");
>>> //Something went wrong with the insert so we rollback and
>>
> nothing
>
>>>changes in the db
>>>}else{
>>> pg_query($connection,"COMMIT");
>>> // If everything went all right, then we commit the changes
>>>}
>>>pg_close($connection);
>>
>>
>>Thanks, Adrian, for the illustration, especially the instance with
>>ROLLBACK. I've figured out this sequence now.
>>
>>What I'm wondering about is what happens when this sequence isn't
>>properly followed and the script quits before issuing a ROLLBACK or a
>>COMMIT. Obviously it's not a good situation and one to be avoided.
>
>
> The database, unless explicitly told to commit and the commit is
> completely successful will always rollback.
>
> So a disconnect results in a rollback.
>


--
Greg Spiegelberg
Sr. Product Development Engineer
Cranel, Incorporated.
Phone: 614.318.4314
Fax: 614.431.8388
Email: gspiegelberg@Cranel.com
Cranel. Technology. Integrity. Focus.



---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Re: Cleaning up aborted transactions

--=-9QgrjoDhMXyCpPOowDhI
Content-Type: text/plain
Content-Transfer-Encoding: quoted-printable

On Tue, 2003-06-10 at 05:21, Michael Glaesemann wrote:
> On Tuesday, Jun 10, 2003, at 17:05 Asia/Tokyo, Adrian Tineo wrote:
> > pg_connect($connection);
> > pg_query($connection,"BEGIN;");
> > $insert=3D"INSERT INTO table VALUES (2,7,5);
> > $result=3Dpg_query($connection,$insert);
> > if(!$result){
> > pg_query($connection,"ROLLBACK");
> > //Something went wrong with the insert so we rollback and nothing
> > changes in the db
> > }else{
> > pg_query($connection,"COMMIT");
> > // If everything went all right, then we commit the changes
> > }
> > pg_close($connection);
>=20
>=20
> Thanks, Adrian, for the illustration, especially the instance with=20
> ROLLBACK. I've figured out this sequence now.
>=20
> What I'm wondering about is what happens when this sequence isn't=20
> properly followed and the script quits before issuing a ROLLBACK or a=20
> COMMIT. Obviously it's not a good situation and one to be avoided.

The database, unless explicitly told to commit and the commit is
completely successful will always rollback.

So a disconnect results in a rollback.

--=20
Rod Taylor

PGP Key: http://www.rbt.ca/rbtpub.asc

--=-9QgrjoDhMXyCpPOowDhI
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: This is a digitally signed message part

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (FreeBSD)

iD8DBQA+5cWu6DETLow6vwwRAmbgAJ9TIED2yqMD67mlE8sN02A27DQgjwCf feZa
AZY1rbKCr7+GWM/dQ6gsDu4=
=QxO9
-----END PGP SIGNATURE-----

--=-9QgrjoDhMXyCpPOowDhI--

Re: Cleaning up aborted transactions

--=-aIhhuNeeOTFi9tDwF9Th
Content-Type: text/plain
Content-Transfer-Encoding: quoted-printable

On Tue, 2003-06-10 at 07:39, Greg Spiegelberg wrote:
> Hi all,
>=20
> Just so I have this straight, because I've been wondering the same,
> if someone on my web site does a SELECT and then clicks on the browser
> stop or gets click happy hitting one or many other links on the same
> web page then the 1st SELECT and all other interrupted SELECT's should
> get rolled back if each query starts with a BEGIN; because there is a
> disconnect. Correct?

What happens is up to PHP to decide in this case. If the browser
disconnects from the webserver, thus PHP is interrupted, then yes, that
sounds like what should happen.

> What about with persistant connections?

Persistent & pooled connections are managed by the client. It is up to
PHP to determine what needs to happen. PHP should be resetting (rolling
back) the connection when it re-enters into the pool, but the database
has no knowledge that sharing is taking place.

I'm not entirely sure. Haven't tried persistent connections since the
early 3.x days.

> Rod Taylor wrote:
> > On Tue, 2003-06-10 at 05:21, Michael Glaesemann wrote:
> >=20
> >>On Tuesday, Jun 10, 2003, at 17:05 Asia/Tokyo, Adrian Tineo wrote:
> >>
> >>>pg_connect($connection);
> >>>pg_query($connection,"BEGIN;");
> >>>$insert=3D"INSERT INTO table VALUES (2,7,5);
> >>>$result=3Dpg_query($connection,$insert);
> >>>if(!$result){
> >>> pg_query($connection,"ROLLBACK");
> >>> //Something went wrong with the insert so we rollback and
> >>
> > nothing
> >=20
> >>>changes in the db
> >>>}else{
> >>> pg_query($connection,"COMMIT");
> >>> // If everything went all right, then we commit the changes
> >>>}
> >>>pg_close($connection);
> >>
> >>
> >>Thanks, Adrian, for the illustration, especially the instance with=20
> >>ROLLBACK. I've figured out this sequence now.
> >>
> >>What I'm wondering about is what happens when this sequence isn't=20
> >>properly followed and the script quits before issuing a ROLLBACK or a=
=20
> >>COMMIT. Obviously it's not a good situation and one to be avoided.
> >=20
> >=20
> > The database, unless explicitly told to commit and the commit is
> > completely successful will always rollback.
> >=20
> > So a disconnect results in a rollback.
> >=20
--=20
Rod Taylor

PGP Key: http://www.rbt.ca/rbtpub.asc

--=-aIhhuNeeOTFi9tDwF9Th
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: This is a digitally signed message part

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (FreeBSD)

iD8DBQA+5drZ6DETLow6vwwRAtGSAJ4k4E0pZOISLI1A1d87rOLNHkvzfQCZ AWNu
i7iDTtlBrC+M+NrzQtXQdn8=
=cfQs
-----END PGP SIGNATURE-----

--=-aIhhuNeeOTFi9tDwF9Th--

Re: Cleaning up aborted transactions

Hi Greg

> Just so I have this straight, because I've been wondering the same,
> if someone on my web site does a SELECT and then clicks on the browser
> stop or gets click happy hitting one or many other links on the same
> web page then the 1st SELECT and all other interrupted SELECT's should
> get rolled back if each query starts with a BEGIN; because there is a
> disconnect. Correct?

I don't let that happen. First, I gather all the required information, then
when the user clicks on submit or whatever he's doing, I do all in one
transaction. If it ends right the user is taken to a screen where he knows
that everything went fine (commit was successfull). If it fails at some
point I send the user to an error page, which can be more or less
descriptive depending on the needs of the interface.

> What about with persistant connections?

I haven't used them as most server out there will have them disabled.
Besides I don't think you need them and is probably not very efficient, also
it could limit the number of users for your app. And it can lead to
unpredictable behaviour on complex websites. If you don't need them, avoid
them.

Adrian Tineo



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

http://archives.postgresql.org

Re: Cleaning up aborted transactions

PHP issues a ROLLBACK into every persistent connection before passing it to
the next process.

Chris

----- Original Message -----
From: "Greg Spiegelberg"
To:
Sent: Tuesday, June 10, 2003 7:39 PM
Subject: Re: [PHP] Cleaning up aborted transactions


> Hi all,
>
> Just so I have this straight, because I've been wondering the same,
> if someone on my web site does a SELECT and then clicks on the browser
> stop or gets click happy hitting one or many other links on the same
> web page then the 1st SELECT and all other interrupted SELECT's should
> get rolled back if each query starts with a BEGIN; because there is a
> disconnect. Correct?
>
> What about with persistant connections?
>
> Greg
>
>
> Rod Taylor wrote:
> > On Tue, 2003-06-10 at 05:21, Michael Glaesemann wrote:
> >
> >>On Tuesday, Jun 10, 2003, at 17:05 Asia/Tokyo, Adrian Tineo wrote:
> >>
> >>>pg_connect($connection);
> >>>pg_query($connection,"BEGIN;");
> >>>$insert="INSERT INTO table VALUES (2,7,5);
> >>>$result=pg_query($connection,$insert);
> >>>if(!$result){
> >>> pg_query($connection,"ROLLBACK");
> >>> //Something went wrong with the insert so we rollback and
> >>
> > nothing
> >
> >>>changes in the db
> >>>}else{
> >>> pg_query($connection,"COMMIT");
> >>> // If everything went all right, then we commit the changes
> >>>}
> >>>pg_close($connection);
> >>
> >>
> >>Thanks, Adrian, for the illustration, especially the instance with
> >>ROLLBACK. I've figured out this sequence now.
> >>
> >>What I'm wondering about is what happens when this sequence isn't
> >>properly followed and the script quits before issuing a ROLLBACK or a
> >>COMMIT. Obviously it's not a good situation and one to be avoided.
> >
> >
> > The database, unless explicitly told to commit and the commit is
> > completely successful will always rollback.
> >
> > So a disconnect results in a rollback.
> >
>
>
> --
> Greg Spiegelberg
> Sr. Product Development Engineer
> Cranel, Incorporated.
> Phone: 614.318.4314
> Fax: 614.431.8388
> Email: gspiegelberg@Cranel.com
> Cranel. Technology. Integrity. Focus.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend