pg_query transaction: auto rollback? begin or start?? commit or end???

...resending, email didn't go through.

On Tue, Jul 22, 2008 at 1:02 PM, Bill Wordsworth
<bill.wordsworth@gmail.com> wrote:

Bill Wordsworth wrote:

...resending, email didn't go through.

On Tue, Jul 22, 2008 at 1:02 PM, Bill Wordsworth
<bill.wordsworth@gmail.com> wrote:

Is this recommended?

pg_query("begin transaction read write;", $connection);
if(pg_transaction_status($connection) == 2) {
pg_query("insert...;", $connection);
pg_query("insert...;", $connection);
pg_query("insert...;", $connection);
}
pg_query("commit transaction;", $connection);
pg_close($connection);

Now *any* error inside transaction will trigger auto rollback for
*all* inserts so I don't need to explicitly issue conditional
rollback? Also is "begin/commit transaction" == "start/end
transaction"??

What if something gets an invalid state (eg you expect a record to have
'active = 156' but it's something else).

So in some cases yes you'll need to do a rollback. On the other hand, if
you don't explicitly do a commit, everything is rolled back.

Yes "begin" == "start transaction" and "commit" == "end transaction".

--
Postgresql & php tutorials
http://www.designmagick.com/

On Wed, Jul 23, 2008 at 01:15:30PM +1000, Chris wrote:

Now *any* error inside transaction will trigger auto rollback for
*all* inserts so I don't need to explicitly issue conditional
rollback? Also is "begin/commit transaction" == "start/end
transaction"??

What if something gets an invalid state (eg you expect a record to have
'active = 156' but it's something else).

So in some cases yes you'll need to do a rollback. On the other hand, if
you don't explicitly do a commit, everything is rolled back.

Yes "begin" == "start transaction" and "commit" == "end transaction".

"commit" really is not a well-chosen name for what it is. It
is often clearer to think in terms of the triple

begin

rollback
end

where begin/end are the standard begin/end transaction
commands while rollback is only ever needed when you detect
a condition someplace logically *outside* the transaction
itself and based on that want to undo the transaction that
is in progress.

Because no matter whether you issue commit or rollback - if
there was an error *inside* the transaction it'll rollback
in any case (unless the error was handled somehow).

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

Thanks Chris and Karsten. I still don't quite understand why invalid
state/record-mismatch would also not trigger auto rollback. How can I
even include something *outside* a transaction *inside* it- shouldn't
everything between "begin" and "end" be subject to auto rollback no
matter what?

Also what is the best way to check if transaction is 'read write'
after doing 'pg_query("begin transaction read write;", $connection);'.
pg_transaction_status() doesn't quite do it (read write=?=2).

http://us2.php.net/function.pg_transaction_status
"The status can be PGSQL_TRANSACTION_IDLE (currently idle),
PGSQL_TRANSACTION_ACTIVE (a command is in progress),
PGSQL_TRANSACTION_INTRANS (idle, in a valid transaction block), or
PGSQL_TRANSACTION_INERROR (idle, in a failed transaction block).
PGSQL_TRANSACTION_UNKNOWN is reported if the connection is bad.
PGSQL_TRANSACTION_ACTIVE is reported only when a query has been sent
to the server and not yet completed."
Cheers, Bill

On Wed, Jul 23, 2008 at 3:02 AM, Karsten Hilbert
<Karsten.Hilbert@gmx.net> wrote:

Bill Wordsworth wrote:

Thanks Chris and Karsten. I still don't quite understand why invalid
state/record-mismatch would also not trigger auto rollback.

If you should have put id 5 instead of id 2 as a foreign key, how is the
database going to know the difference? Both are valid id's and valid
data for an "int" type field.

How can I

even include something *outside* a transaction *inside* it- shouldn't
everything between "begin" and "end" be subject to auto rollback no
matter what?

Most things in postgres are transaction safe, some aren't like "cluster"
or "vacuum full". I couldn't find a list of things that won't work in a
transaction but it's a pretty short list. Even table changes (alter
table, create index etc) are transaction safe.

Maybe I misunderstand the question.

Also what is the best way to check if transaction is 'read write'
after doing 'pg_query("begin transaction read write;", $connection);'.
pg_transaction_status() doesn't quite do it (read write=?=2).

That's going to report if you're inside a transaction or not, it's not
going to report what transaction level you are in. I don't think there's
a way to show that.

--
Postgresql & php tutorials
http://www.designmagick.com/

How can I

even include something *outside* a transaction *inside* it

I was referring to conditions outside the database which you
detect while the transaction is in progress and which
invalidate the semantic integrity of the transaction as a
whole. Under such circumstances you would want to issue a
rollback even though technically the transaction went
through an *could* be committed. That's about the only case
where it makes sense to have a keyword separate from "end",
namely "rollback". Because you have the choice: either "end"
the transaction or "rollback". All other cases just need
"end". They will rollback or commit depending on whether
there were any unhandled errors.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346