Hi

I have

set autocommit on;
set nojournaling;

create table table-name as select
with nojournaling;

I am using Ingres 2.6
and need to avoid journaling but the table create still appears to be
using (and exceeding) the transaction logs.

Regards

Hi,
Transaction logs are used to record what you write to tables before the
table information is updated on disk or in cache.
This is used, whether the table is journalled or not.

There is an exception to this, which is 'bulk copying'.
Refer to the SQL guide under the 'copy' section in chapter 8.

Cheers,
Michael

-----Original Message-----
From: info-ingres-bounces (AT) kettleriver...ting (DOT) com
[mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of
eunever32 (AT) yahoo (DOT) co.uk
Sent: 22 January 2009 09:03
To: info-ingres (AT) kettleriverconsulting (DOT) com
Subject: [Info-Ingres] create table as select and transaction logging

Hi

I have

set autocommit on;
set nojournaling;

create table table-name as select
with nojournaling;

I am using Ingres 2.6
and need to avoid journaling but the table create still appears to be
using (and exceeding) the transaction logs.

Regards
_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com
http://www.kettleriverconsulting.com...fo/info-ingres

Hi Michael

So are you saying that a "create table" cannot run without
"transaction logging"?

Regards


On Jan 22, 9:18*am, "Michael Dyer" <Michael.D... (AT) ingres (DOT) com> wrote:

Hi,

Transaction logging can be turned off, but its very dangerous. One single error and your up for a database recovery. Which is not fun.

How big is your transaction logfile anyway?

Try this...
declare global temporary table session.stuff as select ...
on commit preserve rows with norecovery;

copy table session.stuff () into 'stuff.dat';

create table my_stuff as select ... where 1 = 0 with nojournaling;

copy table my_stuff () from 'stuff.dat';

Martin Bowes

-----Original Message-----
From: info-ingres-bounces (AT) kettleriver...ting (DOT) com [mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of eunever32 (AT) yahoo (DOT) co.uk
Sent: 22 January 2009 09:41
To: info-ingres (AT) kettleriverconsulting (DOT) com
Subject: Re: [Info-Ingres] create table as select and transaction logging

Hi Michael

So are you saying that a "create table" cannot run without
"transaction logging"?

Regards


On Jan 22, 9:18*am, "Michael Dyer" <Michael.D... (AT) ingres (DOT) com> wrote:
_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com
http://www.kettleriverconsulting.com...fo/info-ingres

A "create table as select ... With nojournaling" should not write anything to the transaction log file except for the create table information, as if a transaction abort is required, all that needs to be done is effectively to drop the table. All that should be written is a LOAD.

If you are seeing transaction log file full situations it may be that your load is very large, and being done in conjunction with many other transactions. These other transactions may eventually wrap around the transaction log file, and run into the "create ... as select" transaction, which would then result in a log full.

How big is your transaction log file? How many rows are you trying to load? You can monitor logging with a "set log_trace" and see what's being written to the transaction log for this statement.

If you are actually seeing each row being written to the log file with the statements you have included then you are running into a bug. You should contact technical support.

John




-----Original Message-----
From: info-ingres-bounces (AT) kettleriver...ting (DOT) com [mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of eunever32 (AT) yahoo (DOT) co.uk
Sent: Thursday, 22 January 2009 8:41 PM
To: info-ingres (AT) kettleriverconsulting (DOT) com
Subject: Re: [Info-Ingres] create table as select and transaction logging

Hi Michael

So are you saying that a "create table" cannot run without "transaction logging"?

Regards


On Jan 22, 9:18*am, "Michael Dyer" <Michael.D... (AT) ingres (DOT) com> wrote:

Hi John,

I'm glad to hear you say the logging should be minimal. Problem is that it doesn't appear to be so.

If you do the set log_trace and a simple create table x as select * from iitables with nojournaling will show there is a lot of logging going on.

Marty


-----Original Message-----
From: info-ingres-bounces (AT) kettleriver...ting (DOT) com [mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of John Dennis
Sent: 22 January 2009 10:34
To: Ingres and related product discussion forum
Subject: Re: [Info-Ingres] create table as select and transaction logging

A "create table as select ... With nojournaling" should not write anything to the transaction log file except for the create table information, as if a transaction abort is required, all that needs to be done is effectively to drop the table. All that should be written is a LOAD.

If you are seeing transaction log file full situations it may be that your load is very large, and being done in conjunction with many other transactions. These other transactions may eventually wrap around the transaction log file, and run into the "create ... as select" transaction, which would then result in a log full.

How big is your transaction log file? How many rows are you trying to load? You can monitor logging with a "set log_trace" and see what's being written to the transaction log for this statement.

If you are actually seeing each row being written to the log file with the statements you have included then you are running into a bug. You should contact technical support.

John




-----Original Message-----
From: info-ingres-bounces (AT) kettleriver...ting (DOT) com [mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of eunever32 (AT) yahoo (DOT) co.uk
Sent: Thursday, 22 January 2009 8:41 PM
To: info-ingres (AT) kettleriverconsulting (DOT) com
Subject: Re: [Info-Ingres] create table as select and transaction logging

Hi Michael

So are you saying that a "create table" cannot run without "transaction logging"?

Regards


On Jan 22, 9:18*am, "Michael Dyer" <Michael.D... (AT) ingres (DOT) com> wrote:
_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com
http://www.kettleriverconsulting.com...fo/info-ingres

On Jan 22, 2009, at 4:03 AM, eunever32 (AT) yahoo (DOT) co.uk wrote:

That's not right. You should be seeing some minimal amount of logging,
independent of the number of rows loaded.

Try adding "structure=heap" to the create table WITH-clause and see if
that reduces the logging to a minimal level. If this works, I'd say
it's
a bug quite frankly. Actually, any non-journaled create-table-as-select
should run using minimal logging, since the undo is to simply drop the
table. (If the new table is journaled, then logging of each inserted
row is obviously needed.)

Karl

Does it not treat "create table as select from" as DDL rather than DML
and do it minimal logging ?
(Forgive the Orakal references)

Geoff

-----Original Message-----
From: info-ingres-bounces (AT) kettleriver...ting (DOT) com
[mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of Karl
& Betty Schendel
Sent: 22 January 2009 11:50
To: Ingres and related product discussion forum
Subject: Re: [Info-Ingres] create table as select and transaction
logging


On Jan 22, 2009, at 4:03 AM, eunever32 (AT) yahoo (DOT) co.uk wrote:

That's not right. You should be seeing some minimal amount of logging,
independent of the number of rows loaded.

Try adding "structure=heap" to the create table WITH-clause and see if
that reduces the logging to a minimal level. If this works, I'd say
it's a bug quite frankly. Actually, any non-journaled
create-table-as-select should run using minimal logging, since the undo
is to simply drop the table. (If the new table is journaled, then
logging of each inserted row is obviously needed.)

Karl

_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com
http://www.kettleriverconsulting.com...fo/info-ingres

************************************************** ********************
This e-mail including any attachments is provided for general information purposes only and does not constitute advice except to the addressee if expressly stated in this email. For the avoidance of doubt the contents of this email and any attachments are subject to contract and are not intended to and will not constitute a legally binding contract or any part of a legally binding contract.

The information contained in this email and any attachments is confidential. It is intended only for the use of the intended recipient at the email address to which it has been addressed. If the reader of this message is not an intended recipient, you are hereby notified that you have received this document in error and that any review, dissemination, distribution or copying of the message or associated attachments is strictly prohibited.

If you have received this e-mail in error, please notify postmaster (AT) RWEsystemsUK (DOT) com and permanently delete the email and any attachments immediately from your system. You should not retain, copy or use this email or any attachment for any purpose, nor disclose all or any part of the contents to any other person. Thank you for your co-operation.

Neither RWE IT UK Ltd nor any of the other companies in the RWE Group from whom this e-mail originates accept any responsibility for losses or damage as a result of any viruses and it is your responsibility to check attachments (if any) for viruses.

RWE IT UK Ltd Registered office: Mistral, Westlea Campus, Chelmsford Road, Swindon, Wiltshire, SN5 7EZ. Registered in England and Wales: company number 6052966.

*******************************************

On Jan 22, 2009, at 7:02 AM, <Geoffrey.Wooton (AT) rwesystemsuk (DOT) com> wrote:

Not as much as it should, it seems.

create table as select is compiled into a create, optionally a modify,
an insert/select, and optionally a modify. (Just one modify, but it
might
come before or after, depending on circumstances.)

The create logs all the create junk which is dependent on the number
of columns (and partitions, in Ingres 2006), but otherwise is a fixed
number of log records.

The modify is also a small fixed number of log records.

The insert/select is compiled as either a LOAD or a series of PUTs.
Ingres 2.6 (the version in use by the OP) only compiles a LOAD if
an explicit WITH NOJOURNALING clause is added to the statement.
I was under the impression that the inspection of journaling vs
nojournaling was the only thing that controlled LOAD vs PUT.
LOAD is highly desirable, because it's one log record for the
entire operation, as opposed to one PUT per row.

Either there's more to the LOAD/PUT decision than I remember,
or there's a query compiler bug that is suppressing LOAD
improperly. I don't believe that there is any way for runtime
to revert a LOAD to PUTs. (With COPY there is, but not for
compiled LOAD.)

Karl