Gurjeet Singh <singh(dot)gurjeet(at)gmail(dot)com> wrote:
> Will Leinweber <will(at)heroku(dot)com> wrote:
>
>> I ruined a 5 hour UPDATE by typoing a table name on a SELECT to
>> verify the update worked.
Ouch! I normally use tab-completion or copy/paste to save myself
from myself in such situations.
>> I only later found out about SAVEPOINT, which I immediately ran
>> the next time I attempted the huge update.
That could work, too. Of course you have to remember too execute
the SAVEPOINT statement *after* the big UPDATE, and you could have a
typo in entering the SAVEPOINT command.
>> psql console, while in a transaction, and while in interactive
>> mode, should savepoint for me.
I could potentially see a psql backslash command for that. One
concern I would have about it is that sometimes people paste a
series of commands into an interactive psql session as one big
paste. What happens then?
> I guess it would be a neat feature to have this in Postgres rather
> than in psql. That is, if running in an explicit transaction (one
> started with BEGIN), issue a savepoint after/before every command
> and emit the savepoint name in a NOTICE.
You certainly wouldn't want to do that all the time, and I'm very
skeptical about the idea of putting it in the backend code. For one
thing, I don't think it makes sense to do this except in an
interactive session. (At least, I can't think of a use-case without
a human on the other end of the connection.)
> If there's no perceivable performance difference in using
> savepoints even under large transactions, then we might want to
> make it all automatic and transparent. So Postgres issues a
> savepoint before every command, and if the command fails, rollback
> to that savepoint, else release that savepoint.
No. While I haven't seen the "failed transaction" concept in other
databases, I'm inclined to think it's a good idea. If you have a
series of statements to run, and one of them fails, continuing to
execute later statements could cause data loss. (Picture CREATE
TABLE AS SELECT followed by DROP TABLE on the original.)
-Kevin