pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDatabase consistency after a power shortage
4+ messages / 4 participants
[nested] [flat]
* Database consistency after a power shortage
@ 2010-12-15 15:12 Alberto <blob2020@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Alberto @ 2010-12-15 15:12 UTC (permalink / raw)
To: pgsql-sql
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?
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Database consistency after a power shortage
@ 2010-12-16 07:38 Scott Marlowe <scott.marlowe@gmail.com>
parent: Alberto <blob2020@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Scott Marlowe @ 2010-12-16 07:38 UTC (permalink / raw)
To: Alberto <blob2020@gmail.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Database consistency after a power shortage
@ 2010-12-16 07:58 Samuel Gendler <sgendler@ideasculptor.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Samuel Gendler @ 2010-12-16 07:58 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Alberto <blob2020@gmail.com>; pgsql-sql
On Wed, Dec 15, 2010 at 11:38 PM, Scott Marlowe <scott.marlowe@gmail.com>wrote:
> On Wed, Dec 15, 2010 at 8:12 AM, Alberto <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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Database consistency after a power shortage
@ 2010-12-16 14:38 Jean-David Beyer <jeandavid8@verizon.net>
parent: Samuel Gendler <sgendler@ideasculptor.com>
0 siblings, 0 replies; 4+ messages in thread
From: Jean-David Beyer @ 2010-12-16 14:38 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2010-12-16 14:38 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-12-15 15:12 Database consistency after a power shortage Alberto <blob2020@gmail.com>
2010-12-16 07:38 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-12-16 07:58 ` Samuel Gendler <sgendler@ideasculptor.com>
2010-12-16 14:38 ` Jean-David Beyer <jeandavid8@verizon.net>
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