Message-id: <4D0A2472.7030304@verizon.net>
Date: Thu, 16 Dec 2010 09:38:42 -0500
From: Jean-David Beyer <jeandavid8@verizon.net>
Organization: Institute for Regimented Whimsey
User-Agent: Thunderbird 2.0.0.24 (X11/20100318)
MIME-version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: Database consistency after a power shortage
References: <AANLkTikURg8vNPTcM6PTABws18E-xZttR3=E+6UMnH=9@mail.gmail.com>
	<AANLkTi=EqMwK6g5b+wZGuqdw+4SvoY1HtF50bO0BjFS1@mail.gmail.com>
	<AANLkTinBxkuUJcLye7Gq76erttNuRy6CquSdydfakUTj@mail.gmail.com>
In-reply-to: <AANLkTinBxkuUJcLye7Gq76erttNuRy6CquSdydfakUTj@mail.gmail.com>
Content-type: text/plain; charset=ISO-8859-1
Content-transfer-encoding: 7bit

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