Обсуждение: Re: pg_query transaction: auto rollback? begin or start?? commit or end???
Re: pg_query transaction: auto rollback? begin or start?? commit or end???
От
"Bill Wordsworth"
Дата:
...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"?? > Cheers, Bill
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
Re: pg_query transaction: auto rollback? begin or start?? commit or end???
От
"Bill Wordsworth"
Дата:
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: > 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 > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
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