Re: Database consistency after a power shortage

Samuel Gendler wrote:
> 
> 
> On Wed, Dec 15, 2010 at 11:38 PM, Scott Marlowe <scott.marlowe@gmail.com
> <mailto:scott.marlowe@gmail.com>> wrote:
> 
>     On Wed, Dec 15, 2010 at 8:12 AM, Alberto <blob2020@gmail.com
>     <mailto:blob2020@gmail.com>> wrote:
> 
>     >
>     > Is there any way to make the 3 operations be one transaction for the
>     > database, so that it keeps them all consistent in case a power
>     shortage
>     > occurs in the middle?
> 
>     Yes, put them in a transaction.
> 
>     begin;
>     insert into head_invoice ...
>     insert into detail_invocie ...
>     insert into payments_x_header_invoice ...
>     commit;
> 
>     Then they either all go or none go.
> 
> 
> But if the database transaction concept is new to you, I highly
> recommend you do a little reading about database transactions in general
> and postgres' implementation specifics as well.  It can be very easy for
> you to make mistakes that can cause the database to get slow or use up a
> lot of disk if you use transactions without understanding at least a
> little of what is happening in the database while the transaction is
> open but uncommitted.
> 
> Incidentally, any error on a query within the transaction will cause the
> transaction to automatically 'rollback' when the transaction completes,
> undoing all of the changes, or you can manually cancel a transaction by
> issuing a 'rollback;' statement instead of 'commit;' at the end.
> 
> 
You can also (or more appropriately, in addition) equip your system with
an uninterruptable power supply with enough capacity to coast over the
power shortage interval, or to perform a controlled shutdown. I do not
know how long it takes to do such a shutdown with postgreSQL, but it
could involve stopping all new transactions from entering the system,
and allowing those in process to complete. A UPS to allow 10 minutes of
run-time is not normally considered too expensive. Mine will run for
about an hour with new batteries, but after a few years it dwindles to
about 1/2 hour. Then I get new ones.

--  .~.  Jean-David Beyer          Registered Linux User 85642. /V\  PGP-Key: 9A2FC99A         Registered Machine
241939./()\ Shrewsbury, New Jersey    http://counter.li.org^^-^^ 09:30:01 up 14 days, 23:16, 4 users, load average:
5.61,4.98, 4.89