[MonetDB-users] Records deleted still appear in: select count(*) from table
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
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
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
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
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
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
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
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 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
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
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 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
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
participants (3)
-
Henry Addington -
Osmin Castillo -
Stefan.Manegold＠cwi.nl