Hi Stefan:

The simple test works ok. Our scenario its a 15 million rows fact table with 800,000 records inserted monthly. For a specific year-month period (201102) we deleted some data with this sql:

delete from table where period = 201102



We inserted the data again for that period  and now we have the double of records for that period: 1,600,000 records



select period,count(*) from table
group by period

returns:

201012  --- 800,000
201101  --- 800,000
201102  --- 1,600,000


select count(*) from table shows 800,000 extra records too. It looks like MonetDB create some aggregated temporal tables.




Thank you guys for your help.

Osmin


2011/5/5 <Stefan.Manegold@cwi.nl>
Osmin,

you could / could you also try the latest Apr2011 release rather than the
previous Mar2011 release ?

Does also the simple test I used (see below) fail for you with either / all
of Oct2010, Mar2011, Apr2011? (Well, IMHO only the latest release matters.)

If that works, fine, we'd need to know more details about your scenario ---
preferably as bug report --- to analyse the issue ...

Stefan

On Thu, May 05, 2011 at 07:02:35AM -0600, Osmin Castillo wrote:
> Hi guys:
>
> We detected this issue at the Oct2010 release, updated to Mar2011 version
> but the problem still persist.We are using SQuirreL as sql client and the
> auto commit option it's checked by default.
>
>
>
> thanks for your help
>
> Osmin
>
> 2011/5/4 Henry Addington <hs.addington@gmail.com>
>
> > Hi Stefan:
> >
> > We have seen this problem with Nov-2010 release way back. In addition to
> > the deleted records showing up, we also saw significant degradation in query
> > performance after data updates. I think I recently saw an email from another
> > user (Vladimir Vlach) who reported the same issue with performance
> > degradation with updates. But, I am not sure if these issues still exist in
> > the post Nov-2010 releases because we developed elaborate work-arounds to
> > rebuild the db with new data instead of updating or deleting records from
> > MonetDB.
> >
> > Sorry that I don't have more details for you.... I do have a vague
> > recollection from a conversation that I had with my staff that there might
> > be a bug with the auto commit.
> >
> > Osmin, can you let us know what version of MonetDB that you are using and
> > whether you are using mclient and whether you have tried the deletes without
> > auto-commit?
> >
> > Henry
> >
> >
> >
> > On Wed, May 4, 2011 at 1:03 PM, <Stefan.Manegold@cwi.nl> wrote:
> >
> >> Hi,
> >>
> >> seems to work fine for me:
> >>
> >> ========
> >> sql>create table t (a int);
> >> operation successful
> >> sql>insert into t values (1);
> >> 1 affected row (96.067ms)
> >> sql>insert into t values (2);
> >> 1 affected row (22.964ms)
> >> sql>insert into t values (3);
> >> 1 affected row (22.058ms)
> >> sql>select * from t;
> >> +------+
> >> | a    |
> >> +======+
> >> |    1 |
> >> |    2 |
> >> |    3 |
> >> +------+
> >> 3 tuples (1.854ms)
> >> sql>select count(*) from t;
> >> +------+
> >> | L15  |
> >> +======+
> >> |    3 |
> >> +------+
> >> 1 tuple (1.853ms)
> >> sql>delete from t where a=2;
> >> 1 affected row (21.234ms)
> >> sql>select * from t;
> >> +------+
> >> | a    |
> >> +======+
> >> |    1 |
> >> |    3 |
> >> +------+
> >> 2 tuples (2.044ms)
> >> sql>select count(*) from t;
> >> +------+
> >> | L16  |
> >> +======+
> >> |    2 |
> >> +------+
> >> 1 tuple (1.979ms)
> >> ========
> >>
> >> In case you find the problem (easily?) reproducable, please file a
> >> detailed
> >> bug report via
> >> http://bugs.monetdb.org/
> >>
> >>
> >> Stefan
> >>
> >>
> >> On Wed, May 04, 2011 at 12:56:35PM -0600, Osmin Castillo wrote:
> >> > Anyone?
> >> >
> >> > 2011/4/28 Osmin Castillo <osmin.castillo@gmail.com>
> >> >
> >> > > Hi guys:
> >> > >
> >> > > After delete some records from a table using a "delete from table
> >> where
> >> > > field=value" criteria, why the "select cont(0) from table" still shows
> >> the
> >> > > same records count? Maybe the records are only "marked" and not really
> >> > > removed from the table? Thanks in advance
> >> > >
> >> > > Osmin
> >>
> >> >
> >> ------------------------------------------------------------------------------
> >> > WhatsUp Gold - Download Free Network Management Software
> >> > The most intuitive, comprehensive, and cost-effective network
> >> > management toolset available today.  Delivers lowest initial
> >> > acquisition cost and overall TCO of any competing solution.
> >> > http://p.sf.net/sfu/whatsupgold-sd
> >>
> >> > _______________________________________________
> >> > MonetDB-users mailing list
> >> > MonetDB-users@lists.sourceforge.net
> >> > https://lists.sourceforge.net/lists/listinfo/monetdb-users
> >>
> >>
> >> --
> >> | Stefan.Manegold @ CWI.nl | DB Architectures (INS1) |
> >> | http://CWI.nl/~manegold/ | Science Park 123 (L321) |
> >> | Tel.: +31 (0)20 592-4212 | 1098 XG Amsterdam  (NL) |
> >>
> >>
> >> ------------------------------------------------------------------------------
> >> WhatsUp Gold - Download Free Network Management Software
> >> The most intuitive, comprehensive, and cost-effective network
> >> management toolset available today.  Delivers lowest initial
> >> acquisition cost and overall TCO of any competing solution.
> >> http://p.sf.net/sfu/whatsupgold-sd
> >> _______________________________________________
> >> MonetDB-users mailing list
> >> MonetDB-users@lists.sourceforge.net
> >> https://lists.sourceforge.net/lists/listinfo/monetdb-users
> >>
> >
> >
> >
> > ------------------------------------------------------------------------------
> > WhatsUp Gold - Download Free Network Management Software
> > The most intuitive, comprehensive, and cost-effective network
> > management toolset available today.  Delivers lowest initial
> > acquisition cost and overall TCO of any competing solution.
> > http://p.sf.net/sfu/whatsupgold-sd
> > _______________________________________________
> > MonetDB-users mailing list
> > MonetDB-users@lists.sourceforge.net
> > https://lists.sourceforge.net/lists/listinfo/monetdb-users
> >
> >

> ------------------------------------------------------------------------------
> WhatsUp Gold - Download Free Network Management Software
> The most intuitive, comprehensive, and cost-effective network
> management toolset available today.  Delivers lowest initial
> acquisition cost and overall TCO of any competing solution.
> http://p.sf.net/sfu/whatsupgold-sd

> _______________________________________________
> MonetDB-users mailing list
> MonetDB-users@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/monetdb-users


--
| Stefan.Manegold @ CWI.nl | DB Architectures (INS1) |
| http://CWI.nl/~manegold/ | Science Park 123 (L321) |
| Tel.: +31 (0)20 592-4212 | 1098 XG Amsterdam  (NL) |

------------------------------------------------------------------------------
WhatsUp Gold - Download Free Network Management Software
The most intuitive, comprehensive, and cost-effective network
management toolset available today.  Delivers lowest initial
acquisition cost and overall TCO of any competing solution.
http://p.sf.net/sfu/whatsupgold-sd
_______________________________________________
MonetDB-users mailing list
MonetDB-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/monetdb-users