transaction logging on update

Hi Paul,

Presumably you want to do this because the update logging is blowing
your log file apart.

You have a few options:
1. Set nojournaling on tablename; update tablename...; set journaling on
tablename.
This should improve the logging situation, but at the cost of
removing journaling from the table until the next checkpoint runs. You
should follow the update with a checkpoint.

2. set session with on_logfull=commit;
Everything gets logged, but when the transaction log file fills
up it autocommits what it has and then continues with the rest of the
update statement.

I find this to be the best option.

3. set nologging.
This is extremely dangerous. One error of any type and you are
up for a database recovery. If you absolutely must use it, take a backup
of the database first and then run the update using an exclusive
database lock. Once completed, take another backup of the database.

Martin Bowes

-----Original Message-----
From: info-ingres-bounces DeleteThis @kettleriverconsulting.com
[mailto:info-ingres-bounces@kettleriverconsulting.com] On Behalf Of

Sent: 22 December 2008 11:08
To: info-ingres DeleteThis @kettleriverconsulting.com
Subject: [Info-Ingres] transaction logging on update

Hi

I know I can do a "copy.in" to a structured table which has no
secondary indexes and it is not logged.

I tried the same approach for an update but cannot seem to get it to
work.

I have
set autocommit on;
set nojournaling;
update ...

but even without secondary indexes it is being logged.

Is it possible to do updates without logging?

Regards
_______________________________________________
Info-Ingres mailing list
Info-Ingres DeleteThis @kettleriverconsulting.com
http://www.kettleriverconsulting.com/mailman/listinfo/info-ingres
 >> Stay informed about: transaction logging on update 

Quite correct. The difference between journaling and logging always
trips me up.

Marty

-----Original Message-----
From: info-ingres-bounces.RemoveThis@kettleriverconsulting.com
[mailto:info-ingres-bounces@kettleriverconsulting.com] On Behalf Of John
Dennis
Sent: 22 December 2008 11:32
To: Ingres and related product discussion forum
Subject: Re: [Info-Ingres] transaction logging on update

Paul, Marty,

The journaling state will make no difference to an update statement, as
all the updated rows need to be logged so they can be rolled back if the
transaction is aborted.

John

-----Original Message-----
From: info-ingres-bounces.RemoveThis@kettleriverconsulting.com
[mailto:info-ingres-bounces@kettleriverconsulting.com] On Behalf Of
Martin Bowes
Sent: Monday, 22 December 2008 10:24 PM
To: Ingres and related product discussion forum
Cc:
Subject: Re: [Info-Ingres] transaction logging on update

Hi Paul,

Presumably you want to do this because the update logging is blowing
your log file apart.

You have a few options:
1. Set nojournaling on tablename; update tablename...; set journaling on
tablename.
This should improve the logging situation, but at the cost of
removing journaling from the table until the next checkpoint runs. You
should follow the update with a checkpoint.

2. set session with on_logfull=commit;
Everything gets logged, but when the transaction log file fills
up it autocommits what it has and then continues with the rest of the
update statement.

I find this to be the best option.

3. set nologging.
This is extremely dangerous. One error of any type and you are
up for a database recovery. If you absolutely must use it, take a backup
of the database first and then run the update using an exclusive
database lock. Once completed, take another backup of the database.

Martin Bowes

-----Original Message-----
From: info-ingres-bounces.RemoveThis@kettleriverconsulting.com
[mailto:info-ingres-bounces@kettleriverconsulting.com] On Behalf Of

Sent: 22 December 2008 11:08
To: info-ingres.RemoveThis@kettleriverconsulting.com
Subject: [Info-Ingres] transaction logging on update

Hi

I know I can do a "copy.in" to a structured table which has no secondary
indexes and it is not logged.

I tried the same approach for an update but cannot seem to get it to
work.

I have
set autocommit on;
set nojournaling;
update ...

but even without secondary indexes it is being logged.

Is it possible to do updates without logging?

Regards
_______________________________________________
Info-Ingres mailing list
Info-Ingres.RemoveThis@kettleriverconsulting.com
http://www.kettleriverconsulting.com/mailman/listinfo/info-ingres

_______________________________________________
Info-Ingres mailing list
Info-Ingres.RemoveThis@kettleriverconsulting.com
http://www.kettleriverconsulting.com/mailman/listinfo/info-ingres

_______________________________________________
Info-Ingres mailing list
Info-Ingres.RemoveThis@kettleriverconsulting.com
http://www.kettleriverconsulting.com/mailman/listinfo/info-ingres
 >> Stay informed about: transaction logging on update