Обсуждение: Database consistency after a power shortage

My question is regarding a potential situation:<br /><br />I have a program that inserts values on 3 tables linked to
eachother. My program is used in a POS. In this specific case, the program has to update the tables "header_invoice",
"detail_invoice"and "payments_x_header_invoice". <br /><br />In a normal operation, the program should insert first a
registryon "header_invoice", then insert N registries on "detail_invoice" referencing the header_invoice number. After
thatit should insert N registries regarding the payments related to the header_invoice, referencing again the invoice.
<br/><br />So the order goes like this:<br />1) Insert 1 new registry on "header_invoice"<br />2) Insert N registries
on"detail_invoice" referencing header_invoice<br />3) Insert N registries on "payments_x_header_invoice" referencing
theheader_invoice<br /><br />If lets say the header_invoice registry was inserted, operation was committed and then a
powershortage occurs and the system shuts down. In that case the database will never know that more registries had to
beinserted, because that happened on the application level. <br /><br />Is there any way to make the 3 operations be
onetransaction for the database, so that it keeps them all consistent in case a power shortage occurs in the middle?<br
/>

On Wed, Dec 15, 2010 at 8:12 AM, Alberto <blob2020@gmail.com> wrote:
> My question is regarding a potential situation:
>
> I have a program that inserts values on 3 tables linked to each other. My
> program is used in a POS. In this specific case, the program has to update
> the tables "header_invoice", "detail_invoice" and
> "payments_x_header_invoice".
>
> In a normal operation, the program should insert first a registry on
> "header_invoice", then insert N registries on "detail_invoice" referencing
> the header_invoice number. After that it should insert N registries
> regarding the payments related to the header_invoice, referencing again the
> invoice.
>
> So the order goes like this:
> 1) Insert 1 new registry on "header_invoice"
> 2) Insert N registries on "detail_invoice" referencing header_invoice
> 3) Insert N registries on "payments_x_header_invoice" referencing the
> header_invoice
>
> If lets say the header_invoice registry was inserted, operation was
> committed and then a power shortage occurs and the system shuts down. In
> that case the database will never know that more registries had to be
> inserted, because that happened on the application level.
>
> 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.

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