From: Jean-David Beyer <jeandavid8@verizon.net>
To: pgsql-sql@postgresql.org
Subject: Re: Database consistency after a power shortage
Date: Thu, 16 Dec 2010 09:38:42 -0500
Message-ID: <4D0A2472.7030304@verizon.net> (raw)
In-Reply-To: <AANLkTinBxkuUJcLye7Gq76erttNuRy6CquSdydfakUTj@mail.gmail.com>
References: <AANLkTikURg8vNPTcM6PTABws18E-xZttR3=E+6UMnH=9@mail.gmail.com>
<AANLkTi=EqMwK6g5b+wZGuqdw+4SvoY1HtF50bO0BjFS1@mail.gmail.com>
<AANLkTinBxkuUJcLye7Gq76erttNuRy6CquSdydfakUTj@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jeandavid8@verizon.net
Subject: Re: Database consistency after a power shortage
In-Reply-To: <4D0A2472.7030304@verizon.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox