Re: [Info-Ingres] Journal size/frequency

[Database Administrator]

2008/11/13 A.C.P.Crawshaw

> Karl & Betty Schendel wrote:
> >
> > It would appear that you're getting CP's from somewhere. I would suspect
> > a cp_timer setting (for the recovery server). As far as I know, dm1305
> > isn't
> > sticky in any sense ... it just signals CPNEEDED to the logging system.
> >
> > The archiver runs when a CP takes place, assuming that archiver_interval
> > is 1.
> > I think there used to be a bug of some kind that failed to run the
> archiver
> > cycle when a CP was forced with DM1305, but I'm sure that was quite a
> > while ago.
> >
> > You can force an archiver cycle with DM1314.
>
> Hmmm... cp_timer is set to zero. I'd set archiver_interval on the
> production db but
> forgot to change it on the test - it was 7, I've now changed it to 1.
>

Were you the sole user of the test database? If so, and if you exited your
session after each update then that would trigger the database being closed,
which would trigger an archiver cycle.

--
Paul Mason

[Database Administrator]

Paul Mason wrote:
>
>
> 2008/11/13 A.C.P.Crawshaw >
>
> Karl & Betty Schendel wrote:
> >
> > It would appear that you're getting CP's from somewhere. I would
> suspect
> > a cp_timer setting (for the recovery server). As far as I know,
> dm1305
> > isn't
> > sticky in any sense ... it just signals CPNEEDED to the logging
> system.
> >
> > The archiver runs when a CP takes place, assuming that
> archiver_interval
> > is 1.
> > I think there used to be a bug of some kind that failed to run
> the archiver
> > cycle when a CP was forced with DM1305, but I'm sure that was quite a
> > while ago.
> >
> > You can force an archiver cycle with DM1314.
>
> Hmmm... cp_timer is set to zero. I'd set archiver_interval on the
> production db but
> forgot to change it on the test - it was 7, I've now changed it to 1.
>
>
> Were you the sole user of the test database? If so, and if you exited
> your session after each update then that would trigger the database
> being closed, which would trigger an archiver cycle.

Aha... yes, I was. Thanks Paul and Karl for the illuminating replies.

Alan

[Database Administrator]

Hi,

Me again. I think I find the bug 120346 as described in doc 416727 at Ingres Knowledge Base.

The workaround is:

Disable the usage oh hash joins.(either by setting opf_hash_join to OFF or for a particular query by executing "set nohash" before running that query).

Cheers.

Leandro Fava
Setor de Informática - UNISC
+55 51 3717 7636


-----Original Message-----
From: info-ingres-bounces@kettleriverconsulting.com [mailto:info-ingres-bounces@kettleriverconsulting.com] On Behalf Of Leandro Pinto Fava
Sent: quinta-feira, 13 de novembro de 2008 10:58
To: Ingres and related product discussion forum
Subject: [Info-Ingres] Strange behaviour with date and timezone

Hi.

I'm facing a strange problem here after I migrated form Ingres II 2.6 to Ingres 2006r2 since last week. I share it to you know.
It seems Ingres is considering timezone twice in some queries. I find this problem when using date columns in NOT EXISTS or OUTER JOIN clauses and II_TIMEZONE_NAME=GMT-2.
I did a testcase to send to Ingres service desk and I put it below for who wants to do a test.

Note: I opened an issue with Ingres Corp and a bug was confirmed but it is not solved yet. I have a workaround, but only use when it is identified on our system.
This bug can be having a high impact on our business, maily when considering dates to calculate duties/taxes over late obligations of our students.

Environment:
Sun Fire x4100 2 AMD Dual CPU
Red Hat ES 5
Ingres II 9.1.1 (a64.lnx/103)NPTL + p12941 + p13083
II_TIMEZONE_NAME=GMT-2
II_DATE_FORMAT=MULTINATIONAL4

The testcase - SQL script:

/* Create two tables with a date column in each */
create table td1 (td1_date date);
create table td2 (td2_date date);

/* Insert data into tables */
insert into td1 values ('06/11/2008');
insert into td2 values ('01/11/2008');

/* Select data with a simple select - the row returned is ok */
select td1_date from td1;

/* Select data with not exists clause - the row is NOT ok 2 hours minus */
/* When considering the day, it is the day before */
select td1_date from td1
where not exists (
select 1 from td2
where td1.td1_date = td2.td2_date);

/* The same wrong result in a query with outer join */
select *
from td1 left join td2
on td1.td1_date = td2.td2_date;

/* As an workaround, doing a cast in the where clause the problem does not happen */
select td1_date from td1
where not exists (
select 1 from td2
where date(td1.td1_date) = td2.td2_date);

Regards,

Leandro Fava
Setor de Informática - UNISC
+55 51 3717 7636

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

[Database Administrator]

On Nov 13, 2008, at 12:06 PM, Leandro Pinto Fava wrote:

> Hi,
>
> Me again. I think I find the bug 120346 as described in doc 416727
> at Ingres Knowledge Base.
>
> The workaround is:
>
> Disable the usage oh hash joins.(either by setting opf_hash_join
> to OFF or for a particular query by executing "set nohash" before
> running that query).


The other thing that you can try, if the application permits, is to
use ANSIDATE
instead of the traditional Ingres DATE. ANSIDATE by definition has
no time part
to screw up the works.

Also, just as a point of interest, this really is a date handling
bug, not a hash
join bug per se. I gather that time parts are being inappropriately
introduced
into what should be date-only ingresdate values. The date normalization
done for hash join doesn't normalize 13-nov-2008 00:00:00 (local
midnight)
into 13-nov-2008 (no time), even though the two compare equal.
Hash join depends on identical bit patterns. The old style FSM join
does an actual date comparison, which does do all the nonsense needed
for the two to compare equal.

Karl


>
> -----Original Message-----
> From: info-ingres-bounces@kettleriverconsulting.com [mailto:info-
> ingres-bounces@kettleriverconsulting.com] On Behalf Of Leandro
> Pinto Fava
> Sent: quinta-feira, 13 de novembro de 2008 10:58
> To: Ingres and related product discussion forum
> Subject: [Info-Ingres] Strange behaviour with date and timezone
>
> Hi.
>
> I'm facing a strange problem here after I migrated form Ingres II
> 2.6 to Ingres 2006r2 since last week. I share it to you know.
> It seems Ingres is considering timezone twice in some queries. I
> find this problem when using date columns in NOT EXISTS or OUTER
> JOIN clauses and II_TIMEZONE_NAME=GMT-2.
> I did a testcase to send to Ingres service desk and I put it below
> for who wants to do a test.
>
> Note: I opened an issue with Ingres Corp and a bug was confirmed
> but it is not solved yet. I have a workaround, but only use when it
> is identified on our system.
> This bug can be having a high impact on our business, maily when
> considering dates to calculate duties/taxes over late obligations
> of our students.
>
> Environment:
> Sun Fire x4100 2 AMD Dual CPU
> Red Hat ES 5
> Ingres II 9.1.1 (a64.lnx/103)NPTL + p12941 + p13083
> II_TIMEZONE_NAME=GMT-2
> II_DATE_FORMAT=MULTINATIONAL4
>
> The testcase - SQL script:
>
> /* Create two tables with a date column in each */
> create table td1 (td1_date date);
> create table td2 (td2_date date);
>
> /* Insert data into tables */
> insert into td1 values ('06/11/2008');
> insert into td2 values ('01/11/2008');
>
> /* Select data with a simple select - the row returned is ok */
> select td1_date from td1;
>
> /* Select data with not exists clause - the row is NOT ok 2 hours
> minus */
> /* When considering the day, it is the day before */
> select td1_date from td1
> where not exists (
> select 1 from td2
> where td1.td1_date = td2.td2_date);
>
> /* The same wrong result in a query with outer join */
> select *
> from td1 left join td2
> on td1.td1_date = td2.td2_date;
>
> /* As an workaround, doing a cast in the where clause the problem
> does not happen */
> select td1_date from td1
> where not exists (
> select 1 from td2
> where date(td1.td1_date) = td2.td2_date);
>
> Regards,

[Database Administrator]

Hi Karl.

> > Me again. I think I find the bug 120346 as described in doc 416727
> > at Ingres Knowledge Base.
> >
> > The workaround is:
> >
> > Disable the usage oh hash joins.(either by setting opf_hash_join
> > to OFF or for a particular query by executing "set nohash" before
> > running that query).
>
>
> The other thing that you can try, if the application permits, is to
> use ANSIDATE
> instead of the traditional Ingres DATE. ANSIDATE by definition has
> no time part
> to screw up the works.

Ok.

> Also, just as a point of interest, this really is a date handling
> bug, not a hash
> join bug per se. I gather that time parts are being inappropriately
> introduced
> into what should be date-only ingresdate values. The date
normalization
> done for hash join doesn't normalize 13-nov-2008 00:00:00 (local
> midnight)
> into 13-nov-2008 (no time), even though the two compare equal.
> Hash join depends on identical bit patterns. The old style FSM join
> does an actual date comparison, which does do all the nonsense needed
> for the two to compare equal.

It makes sense.

Leandro