Re: Opinions on how to Integrate Transactions

On Sun, 2009-06-14 at 11:14 -0400, APseudoUtopia wrote:

> associated forum posts. From what I've read on transactions, they
> would be the solution to my problems.

Yes, but proper use of transactions - and a proper understanding of
transactions - will help you a lot more than that.

I very, very strongly recommend that you read the PostgreSQL manual. It
will not only teach you about PostgreSQL, but should teach you a lot
about how to use a relational database effectively.

It's really important to understand how transactions operate in a
concurrent environment, how locking works (and where it doesn't apply),
etc. Understanding MVCC is also rather helpful. The PostgreSQL manual
will teach you all these things and a LOT more.

> 1. Is there a way to list active transactions on all databases?

SELECT * FROM pg_stat_activity;

Filter the results as desired.

>  I'd
> like a way to see if there are any "idle" transactions that are just
> hanging there. Is there a way to "kill" them from the console?

pg_cancel_backend(...)

However, you should not have idle transactions in the first place. An
idle transaction indicates an application bug unless your application is
designed to hold transactions open while waiting for user input. If it
is meant to, then killing those idle transactions would be wrong, since
the app expects them to be there later.

Holding transactions open for long periods, like waiting for user input,
causes all sorts of issues, though, and is best avoided.

> 2. I have a PHP class which communicates with the DB, which is
> included into every other page on the site. Should I just add BEGIN
> and COMMIT at the beginning and end of the class? This way, I wont
> need to edit every single page on my site to include BEGIN and COMMIT?
> Or will that type of "blind" transactions cause problems?

It's a much better idea to look at how you're actually communicating
with the database, examine each query or set of queries, and see what
needs to be wrapped in a transaction.

In lots of cases it will be fine to just wrap the lot in begin/commit.
In some cases, though, you might have one operation that you want to
succeed even if later ones fail. Additionally, if you have single
independent statements, there's no need to wrap them in BEGIN/COMMIT,
since Pg creates an implicit transaction for each statement if one isn't
already running.

> 3. What happens when there IS an error? Do I need to run ROLLBACK
> every time? Or will it automatically reset when the connection is
> closed when the PHP script dies?

If the connection is closed while a transaction is open, an implicit
ROLLBACK is issued by the backend.

However, you should always close your transactions properly, both to
ensure that data that should get committed does get committed, and so
that if you start using connection pooling (which you probably will)
you're not driven insane by issues with transactions left open.

--
Craig Ringer