[MonetDB-users] Records deleted still appear in: select count(*) from table

newer
[MonetDB-users] wrong results for...

Osmin Castillo

28 Apr 2011 28 Apr '11

7:49 p.m.

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

Attachments:

attachment.html (text/html — 349 bytes)

Show replies by date

Osmin Castillo

4 May 4 May

6:56 p.m.

New subject: [MonetDB-users] Records deleted still appear in: select count(*) from table

Anyone? 2011/4/28 Osmin Castillo

...

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

Stefan.Manegold＠cwi.nl

8:03 p.m.

New subject: [MonetDB-users] Records deleted still appear in: select count(*) from table

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

...
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

Henry Addington

5 May 5 May

1:28 a.m.

New subject: [MonetDB-users] Records deleted still appear in: select count(*) from table

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, 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

...
Anyone?

2011/4/28 Osmin Castillo

...
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

On Wed, May 04, 2011 at 12:56:35PM -0600, Osmin Castillo wrote: 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

Osmin Castillo

1:02 p.m.

New subject: [MonetDB-users] Records deleted still appear in: select count(*) from table

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

...

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, 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

...
Anyone?

2011/4/28 Osmin Castillo

...
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

On Wed, May 04, 2011 at 12:56:35PM -0600, Osmin Castillo wrote: 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

Stefan.Manegold＠cwi.nl

1:11 p.m.

New subject: [MonetDB-users] Records deleted still appear in: select count(*) from table

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

...
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, 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

...
Anyone?

2011/4/28 Osmin Castillo

...
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

On Wed, May 04, 2011 at 12:56:35PM -0600, Osmin Castillo wrote: 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

Osmin Castillo

1:34 p.m.

New subject: [MonetDB-users] Records deleted still appear in: select count(*) from table

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

...

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

...
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, 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

...
Anyone?

2011/4/28 Osmin Castillo

...
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

On Wed, May 04, 2011 at 12:56:35PM -0600, Osmin Castillo wrote: 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

Stefan.Manegold＠cwi.nl

2:02 p.m.

New subject: [MonetDB-users] Records deleted still appear in: select count(*) from table

Unless you already tired that, would you have the option to (1) deleted all 1,600,000 records for period 201102, (2) check whether they are indeed gone (3) re-insert the 800,000 for that period, again and report whether that works fine or fails ? MonetDB does not create/keep any aggregated temporal tables. Stefan ps: Did you by any chance check whether your initial delete worked without problems/error messages? Did you by any chance check, whether after your initial delete the delete records were indeed "gone", i.e., did no longer show with either/all of select count(*) from table; select count(*) from table group by period; select count(*) from table where period = 201102; before you re-inserted them? For that re-insert as well as your regular monthly inserts, do you use pre-record INSERT statements or a "bulk" COPY INTO (from FILE or STDIN)? On Thu, May 05, 2011 at 07:34:26AM -0600, Osmin Castillo wrote:

...

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

...
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

...
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, 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

...
Anyone?

2011/4/28 Osmin Castillo

> 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

On Wed, May 04, 2011 at 12:56:35PM -0600, Osmin Castillo wrote: 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

...

------------------------------------------------------------------------------ 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

2:12 p.m.

New subject: [MonetDB-users] Records deleted still appear in: select count(*) from table

On Thu, May 05, 2011 at 04:02:33PM +0200, Stefan.Manegold@cwi.nl wrote:

...

Unless you already tired that, would you have the option to

(1) deleted all 1,600,000 records for period 201102, (2) check whether they are indeed gone (3) re-insert the 800,000 for that period, again

and report whether that works fine or fails ?

if that does not help, you run the following queries and share the output? TRACE select count(*) from table; TRACE select count(*) from table group by period; TRACE select count(*) from table where period = 201102; Thanks, Stefan

...

MonetDB does not create/keep any aggregated temporal tables.

Stefan

ps: Did you by any chance check whether your initial delete worked without problems/error messages? Did you by any chance check, whether after your initial delete the delete records were indeed "gone", i.e., did no longer show with either/all of select count(*) from table; select count(*) from table group by period; select count(*) from table where period = 201102; before you re-inserted them?

For that re-insert as well as your regular monthly inserts, do you use pre-record INSERT statements or a "bulk" COPY INTO (from FILE or STDIN)?

On Thu, May 05, 2011 at 07:34:26AM -0600, Osmin Castillo wrote:

...
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

...
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

...
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, 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 > > > 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

...
------------------------------------------------------------------------------ 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

4985

Age (days ago)

4992

Last active (days ago)

List overview

Download

8 comments

3 participants

participants (3)

Henry Addington
Osmin Castillo
Stefan.Manegold＠cwi.nl