Re: Database consistency after a power shortage

On Wed, Dec 15, 2010 at 11:38 PM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>wrote:

> On Wed, Dec 15, 2010 at 8:12 AM, Alberto <blob2020(at)gmail(dot)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.