Thread: Database Integrity

Database Integrity

From
JORGE MALDONADO
Date:
I need to delete several records from a table so my query is composed of a DELETE command with a condition, something like the following:

DELETE FROM tblTable WHERE field1 = something

Do I need to use a transaction in an operation like this to be sure the database remains integral in case of an error?

Respectfully,
Jorge Maldonado

Re: Database Integrity

From
Ramy Abdel-Azim
Date:
The purpose of using a transaction is so that if you run your delete
statement and realize that you've deleted the wrong rows, you can roll
the transaction back and get those rows back. You can then modify your
query and try again and when you finally get it right, issue a commit
command.

Hope that answers your question.

On 11/18/11 2:32 PM, JORGE MALDONADO wrote:
> I need to delete several records from a table so my query is composed
> of a DELETE command with a condition, something like the following:
>
> DELETE FROM tblTable WHERE field1 = something
>
> Do I need to use a transaction in an operation like this to be sure
> the database remains integral in case of an error?
>
> Respectfully,
> Jorge Maldonado


Re: Database Integrity

From
"Jean-Yves F. Barbier"
Date:
On Fri, 18 Nov 2011 13:32:03 -0600
JORGE MALDONADO <jorgemal1960@gmail.com> wrote:

> I need to delete several records from a table so my query is composed of a
> DELETE command with a condition, something like the following:
>
> DELETE FROM tblTable WHERE field1 = something
>
> Do I need to use a transaction in an operation like this to be sure the
> database remains integral in case of an error?

AFAIK no because even if you're not in your transaction, Pg opens its own (hence
rollback if something bad happens).

--
Living on Earth may be expensive, but it includes an annual free trip
around the Sun.

Re: Database Integrity

From
"Daniel Staal"
Date:
On Fri, November 18, 2011 2:34 pm, Ramy Abdel-Azim wrote:
> The purpose of using a transaction is so that if you run your delete
> statement and realize that you've deleted the wrong rows, you can roll
> the transaction back and get those rows back. You can then modify your
> query and try again and when you finally get it right, issue a commit
> command.

In other words: What do you mean by 'database integrity'?  If all you are
asking is if the database will still return queries, you don't need the
transaction.  If you need those queries to return some consistent logical
state that the delete would have affected, you probably do need the
transaction.

If your application can run correctly with 'half' of the delete run, you
don't need to be sure the whole delete ran.  If it can't, here's a tool to
make sure either the whole delete ran or none of it did.

The choice for which you need is up to you, and depends on what you are
doing.  ;)

Daniel T. Staal

---------------------------------------------------------------
This email copyright the author.  Unless otherwise noted, you
are expressly allowed to retransmit, quote, or otherwise use
the contents for non-commercial purposes.  This copyright will
expire 5 years after the author's death, or in 30 years,
whichever is longer, unless such a period is in excess of
local copyright law.
---------------------------------------------------------------


Re: Database Integrity

From
"Jean-Yves F. Barbier"
Date:
On Fri, 18 Nov 2011 20:44:33 +0100
"Jean-Yves F. Barbier" <12ukwn@gmail.com> wrote:

>
> AFAIK no because even if you're not in your transaction, Pg opens its own (hence
> rollback if something bad happens).

Oops: forgot to say that in this case, you'll loose the concurrency you
could have with SET TRANSACTION SERIALIZABLE, which is bad if there are
many concurrent DELETEs & users making them.

--
Evening hours "all clear" for romance!
(Tell mate you have to work late.)