October 2008 - developers-list

[Monetdb-developers] mapi_fetch_reset
by Stefan de Konink 10 Oct '08

10 Oct '08

-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA512 Hi, Is it theoretically possible that the result set is too big for mapi_fetch_reset to work, so it is actually not brought back to the first result? (Opposed to the first result in the cache) I'm having the following optimalisation issue; I can request a set of points in one go, but I need to reprocess them in two outputs. - - I can place two queries, second one with a subselect - - I can place one query with a subselect (process it twice, see question one) - - I can store the outcome of query one, and then use an IN (...) pattern build from the first query output. - - I can store desired outcome in a temporary buffer, and place it on the end, using the one query subselect approach. Option one uses two queries, sounds slow, option two is related to quetion one, I have seen option 3 fail for huge sets, Option 4 can take up twice as much memory. Suggestions? :) Stefan -----BEGIN PGP SIGNATURE----- Version: GnuPG v2.0.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEAREKAAYFAkjvlPcACgkQYH1+F2Rqwn3BPQCfTCjR/aHwJw/tUU09oCl+W5xq 1cEAniPMIFArgNJjOdKAgmH2TUZHWEpG =l9k7 -----END PGP SIGNATURE-----

1 0

Re: [Monetdb-developers] [carl.nobile@gmail.com: Re: Data integrity bug]
by Stefan Manegold 07 Oct '08

07 Oct '08

On Tue, Oct 07, 2008 at 07:52:49PM +0200, Niels Nes wrote: > > Sjoerd, Stefan, > > Ik heb al een tijdje geen buildtools in een lege omgeving (ie geen > monetdb enviroment settings) geprobeert. Kan het zijn dat er dan > iets misgaat. Make distclean moeten we misschien ook nog aan de testing > toevoegen ? > > Niels > ----- Forwarded message from Carl Nobile <carl.nobile(a)gmail.com> ----- > > From: Carl Nobile <carl.nobile(a)gmail.com> > Subject: Re: [Monetdb-developers] Data integrity bug > To: Niels Nes <Niels.Nes(a)cwi.nl> > > Neils, > I got buildtools to compile. It seems that 'make distclean' > does not clean out any of the yacc crud. So, I just blew away > the tree and checked it out again. > Now when I run ./bootstrap in the clients code it complains the > following: > $ ./bootstrap > automake 1.9.6 is 1.7 or newer. Good. > autoconf 2.61 is 2.57 or newer. Good. > libtool 1.5.24Debian1.5.24-1ubuntu1 is 1.4 or newer. Good. > Python 2.5.1 is 2.0.0 or newer. Good. > Traceback (most recent call last): > File "/usr/local/MonetDB/bin/autogen.py", line 29, in > <module> > from autogen import am > ImportError: No module named autogen > I've looked for a python-autogen package, but have found > nothing in Ubuntu's repositories. from buildtools/README ======== [...] In particular, see autogen/README for how to set your PYTHONPATH in case ./bootstrap of MonetDB, sql, pathfinder, etc. fails to find the autogen module with the default settings. ======== from buildtools/autogen/README ======== To build, run python setup.py build then to install, run python setup.py install [--prefix=<install-dir-prefix>] Use python setup.py --help to get more information about the possible options. If you use the --prefix option or otherwise install autogen in a different place, you may have to set your PYTHONPATH environment variable accordingly. The directory which needs to be added is <install-dir-prefix>/lib(64)/python<python-version>/site-packages, where <python-version> is the version of Python used (e.g. 2.4). You can automatically get the respective directory via "<install-dir-prefix>/`python -c 'import distutils.sysconfig; print distutils.sysconfig.get_python_lib(0,0,"")'`". ( In case you are using Apple-built Python 2.5.1 on Mac OS X 10.5.* (Darwin 9.*), it can be that the above command yields "<install-dir-prefix>/Library/Python/2.5/site-packages", while Python's distutils.core.setup() actually installs the python modules into "<install-dir-prefix>/lib/python2.5/site-packages". In that case, replacing the above command by "<install-dir-prefix>/`python -c 'import distutils.sysconfig; print distutils.sysconfig.get_python_lib(0,1,"")'`/site-packages" might help to overcome this inconsistency. ) [...] ======== Stefan > -Carl > On Tue, Oct 7, 2008 at 12:35 PM, Niels Nes > <[1]Niels.Nes(a)cwi.nl> wrote: > > On Tue, Oct 07, 2008 at 12:24:38PM -0400, Carl Nobile wrote: > > Neils, > > I'm getting errors when I try to build buildtools. > > mel.cc:(.text+0x7f9): undefined reference to `yyin' > > My installed packages are: > > autoconf (GNU Autoconf) 2.61 > > automake (GNU automake) 1.9.6 > > autogen (GNU AutoGen) - The Automated Program Generator - > Ver. > > 5.9.1 > > ltmain.sh (GNU libtool) 1.5.24 Debian 1.5.24-1ubuntu1 > > (1.1220.2.456 2007/06/24 02:25:32) > > bison (GNU Bison) 2.3 > > flex 2.5.33 > > g++ (GCC) 4.1.3 20070929 (prerelease) (Ubuntu > 4.1.2-16ubuntu2) > > Carl > > yyin should be defined in mel/[2]mel.yy.cc, ie a flex > generated source > file. Maybe something went wrong with this file. We have for > almost > all tools the same version on our fedore 8 systems. > Niels (ie not Neils) > > On Tue, Oct 7, 2008 at 6:27 AM, Niels Nes > <[1][3]Niels.Nes(a)cwi.nl> > > > wrote: > > > > On Mon, Oct 06, 2008 at 03:07:02PM -0400, Carl Nobile > wrote: > > > Ross, > > > I forgot to answer one of your questions. Although my > web > > > service can delete data, running a delete doesn't > seen to > > be > > > involved with this issue. To make a long story short > it > > seems > > > everything is okay until I add a column during an > update. > > > > I just fixed the problem for Ross. Allthough the problem > was > > related to > > deletes which you do not have, it still may solve your > > problem as the > > fix was at a lower level than the sql delete statement. > > So could you test with a checkout from cvs head? > > Niels > > > > > -Carl > > > On Mon, Oct 6, 2008 at 2:50 PM, Carl Nobile > > > > > > <[1][2][4]carl.nobile(a)gmail.com> wrote: > > > > > > Yes, the same is true with my situation. It can work > fine > > 3 or > > > 4 times in a row then select from where equals just > stops > > > returning any data. > > > -Carl > > > On Mon, Oct 6, 2008 at 2:45 PM, Ross Bates > > > > > > <[2][3][5]rbates(a)gmail.com> wrote: > > > > > > Something definitely going on with the table stats, I > am > > seeing > > > the same strange behavior in that it doesn't occur on > a > > > consistent basis. > > > I have finally been able to recreate it using the > > following > > > steps. (also see my notes that follow) > > > -------------------------------------- > > > CREATE TABLE "sobi"."table1" > > > ( > > > cid integer, > > > p varchar(100), > > > c varchar(100), > > > lf integer, > > > rt integer, > > > lvl integer > > > ) > > > insert into sobi.table1 values (1,'foo','bar',9,12,1) > > > insert into sobi.table1 values > (1,'boo','blah',13,22,1) > > > CREATE TABLE "sobi"."table2" > > > ( > > > p varchar(100), > > > c varchar(100), > > > lf integer, > > > rt integer, > > > lvl integer > > > ) > > > insert into sobi.table2(p,c) (select p,c from > sobi.table1) > > > select * from sobi.table2 > > > select * from sobi.table2 where p = 'foo' > > > delete from sobi.table2 > > > -------------------------------------- > > > It might happen right away - or it might take 5-10 > times, > > but > > > if you iterate over the insert > select > delete > > statements > > > eventually the [p = 'foo'] select will stop returning > > rows. > > > On Mon, Oct 6, 2008 at 1:24 PM, Carl Nobile > > > > > > <[3][4][6]carl.nobile(a)gmail.com> wrote: > > > > > > Ross, > > > I have written a web service in Python that sets on > top of > > > MonetDB it is somewhat complicated, but I'll try to > > explain it > > > below. There are only three tables in the schema and > one > > view > > > over sys.tables and sys.columns. > > > BUSINESS_UNIT > > > BUSINESS_USER_COLUMN_STATS > > > BUSINESS_USER_PROFILE > > > BUSINESS_USER_PROFILE_COLUMNS > > > 1) START TRANSACTION > > > 2) Check if record exists to determine if an UPDATE > or an > > > INSERT needs to be done. > > > (SELECT count(*) FROM "BUSINESS_USER_PROFILE" WHERE > > > "USER_ID"=%(USER_ID)s AND > "BUSINESS_ID"=%(BUSINESS_ID)s) > > > 3) If an INSERT then check if the BUSINESS_UNIT is > > present. > > > SELECT count(*) FROM "BUSINESS_UNIT" WHERE > > > "BUSINESS_ID"=%(BUSINESS_ID)s > > > 4) If UPDATE see item 13 > > > 5) If INSERT then: INSERT INTO "BUSINESS_UNIT" > > ("BUSINESS_ID", > > > "CREATE_DT", "CREATE_CID") VALUES (%(BUSINESS_ID)s, > > > %(CREATE_DT)s, %(CREATE_CID)s > > > 6) Look to see if any new columns need to be added to > > > BUSINESS_USER_PROFILE by checking the view, if yes > then: > > ALTER > > > TABLE "BUSINESS_USER_PROFILE" ADD COLUMN "%s" > VARCHAR(240) > > NULL > > > 7) Commit then START TRANSACTION > > > 8) INSERT the record into the BUSINESS_USER_PROFILE > table > > (the > > > two %s constructs could be any number of columns and > > values): > > > INSERT INTO "BUSINESS_USER_PROFILE" (%s) VALUES (%s) > > > 9) Check to see if the column needs to be inserted or > > updated > > > to the stats table: SELECT "COLUMN_NAME" FROM > > > "BUSINESS_USER_COLUMN_STATS" > > > 10) Check the view again to see if the column already > > exists. > > > 11) If INSERT: INSERT INTO > "BUSINESS_USER_COLUMN_STATS" > > > ("COLUMN_NAME", "BUSINESS_ID", "LAST_UPDT_DT", > > "LAST_UPDT_CID") > > > VALUES (%(COLUMN_NAME)s, %(BUSINESS_ID)s, > > %(LAST_UPDT_DT)s, > > > %(LAST_UPDT_CID)s) > > > 12) If UPDATE: UPDATE "BUSINESS_USER_COLUMN_STATS" > SET > > > "BUSINESS_ID"=%(BUSINESS_ID)s, > > "LAST_UPDT_DT"=%(LAST_UPDT_DT)s, > > > "LAST_UPDT_CID"=%(LAST_UPDT_CID)s WHERE > > > "COLUMN_NAME"=%(COLUMN_NAME)s > > > 13) Do 6 and 7 above. > > > 14) UPDATE the record in the BUSINESS_USER_PROFILE > table > > (the > > > two %s constructs could be any number of columns or > > > conditions): 'UPDATE "BUSINESS_USER_PROFILE" SET %s > WHERE > > %s > > > 15) Do 9, 10, 11, and 12 > > > OK this may be over kill, but I had to think through > the > > > process myself before I could answer your question. > Any > > select > > > after doing a sequence of six inserts and six updates > > would > > > usually cause the issue. The operative word is > 'usually', > > > because sometimes it would work flawlessly. > > > -Carl > > > On Mon, Oct 6, 2008 at 1:23 PM, Ross Bates > > > > > > <[4][5][7]rbates(a)gmail.com> wrote: > > > > > > Carl - this bug continues to bother me as well as I > can't > > > reproduce the exact steps. > > > Does your application populate the table in question > using > > sql > > > like this? > > > insert into foo(col1,col2) (select col1,col2 from > bar) > > > Also, do you run any delete statements before > populating > > the > > > data? > > > I was thinking that the bug was related to the > > > addition/deletion of columns, but it appears to show > up > > more > > > often after a series of insert/delete statements > which > > follow > > > my alter/create table statements. > > > On Sun, Oct 5, 2008 at 4:19 PM, Stefan de Konink > > > > > > <[5][6][8]stefan(a)konink.de> wrote: > > > > > > -----BEGIN PGP SIGNED MESSAGE----- > > > Hash: SHA512 > > > > > > Ross Bates schreef: > > > > > > > Stefan - what is occurring in the alignment bug you > are > > > referring to? > > > > > > I had an issue before that occured after I had > added an > > > index (that in > > > MonetDB terms shouldn't do anything), where I was > able > > to > > > make mserver5 > > > crash on a string comparison. > > > But I know that this was fixed even before I had > > reported > > > it. > > > > > > Stefan > > > -----BEGIN PGP SIGNATURE----- > > > Version: GnuPG v2.0.9 (GNU/Linux) > > > Comment: Using GnuPG with Mozilla - > > > > > > [6][7][9]http://enigmail.mozdev.org > > > > > > > > > > > > iEYEAREKAAYFAkjpL2AACgkQYH1+F2Rqwn3k9gCeKg+p+n+nQE+dPTLDLnZe > > > 9OZ9 > > > SIAAnimb0zS4XvWtiKncWEwh3RU7DMJe > > > =zHeG > > > -----END PGP SIGNATURE----- > > > > > > > > > > References > > > > > > 1. mailto:[11][10]carl.nobile@gmail.com > > > 2. mailto:[12][11]rbates@gmail.com > > > > > 3. mailto:[13][12]carl.nobile@gmail.com > > > 4. mailto:[14][13]rbates@gmail.com > > > 5. mailto:[15][14]stefan@konink.de > > > 6. [16][15]http://enigmail.mozdev.org/ > > > > > 7. mailto:[17][16]carl.nobile@gmail.com > > > 8. mailto:[18][17]carl.nobile@gmail.com > > > 9. mailto:[19][18]carl.nobile@gmail.com > > > > > > > > > --------------------------------------------------------------- > > ---------- > > > This SF.Net email is sponsored by the Moblin Your Move > > Developer's challenge > > > Build the coolest Linux based applications with Moblin > SDK & > > win great prizes > > > Grand prize is a trip for two to an Open Source event > > anywhere in the world > > > > > > > [20][19]http://moblin-contest.org/redirect.php?banner_id=100 > &url=/ > > > _______________________________________________ > > > Monetdb-developers mailing list > > > [21][20]Monetdb-developers(a)lists.sourceforge.net > > > > > > [22][21]https://lists.sourceforge.net/lists/listinfo/monetdb > -develo > > > pers > > > > -- > > Niels Nes, Centre for Mathematics and Computer Science > (CWI) > > Kruislaan 413, 1098 SJ Amsterdam, The Netherlands > > room C0.02, phone ++31 20 592-4098, fax ++31 20 592-4312 > > > url: [23][22]http://www.cwi.nl/~niels e-mail: > > [24][23]Niels.Nes(a)cwi.nl > > > > > > > > References > > > > 1. mailto:[24]Niels.Nes@cwi.nl > > > 2. mailto:[25]carl.nobile@gmail.com > > > 3. mailto:[26]rbates@gmail.com > > > 4. mailto:[27]carl.nobile@gmail.com > > > 5. mailto:[28]rbates@gmail.com > > 6. mailto:[29]stefan@konink.de > > 7. [30]http://enigmail.mozdev.org/ > > > 8. mailto:[31]carl.nobile@gmail.com > > 9. mailto:[32]carl.nobile@gmail.com > > > 10. mailto:[33]carl.nobile@gmail.com > > 11. mailto:[34]carl.nobile@gmail.com > > 12. mailto:[35]rbates@gmail.com > > 13. mailto:[36]carl.nobile@gmail.com > > 14. mailto:[37]rbates@gmail.com > > 15. mailto:[38]stefan@konink.de > > 16. [39]http://enigmail.mozdev.org/ > > 17. mailto:[40]carl.nobile@gmail.com > > 18. mailto:[41]carl.nobile@gmail.com > > 19. mailto:[42]carl.nobile@gmail.com > > 20. > [43]http://moblin-contest.org/redirect.php?banner_id=100&url > =/ > > 21. mailto:[44]Monetdb-developers@lists.sourceforge.net > > 22. > [45]https://lists.sourceforge.net/lists/listinfo/monetdb-dev > elopers > > 23. [46]http://www.cwi.nl/%7Eniels > > 24. mailto:[47]Niels.Nes@cwi.nl > > 25. mailto:[48]carl.nobile@gmail.com > -- > > Niels Nes, Centre for Mathematics and Computer Science (CWI) > Kruislaan 413, 1098 SJ Amsterdam, The Netherlands > room C0.02, phone ++31 20 592-4098, fax ++31 20 592-4312 > url: [49]http://www.cwi.nl/~niels e-mail: > [50]Niels.Nes(a)cwi.nl > > References > > 1. mailto:Niels.Nes@cwi.nl > 2. http://mel.yy.cc/ > 3. mailto:Niels.Nes@cwi.nl > 4. mailto:carl.nobile@gmail.com > 5. mailto:rbates@gmail.com > 6. mailto:carl.nobile@gmail.com > 7. mailto:rbates@gmail.com > 8. mailto:stefan@konink.de > 9. http://enigmail.mozdev.org/ > 10. mailto:carl.nobile@gmail.com > 11. mailto:rbates@gmail.com > 12. mailto:carl.nobile@gmail.com > 13. mailto:rbates@gmail.com > 14. mailto:stefan@konink.de > 15. http://enigmail.mozdev.org/ > 16. mailto:carl.nobile@gmail.com > 17. mailto:carl.nobile@gmail.com > 18. mailto:carl.nobile@gmail.com > 19. http://moblin-contest.org/redirect.php?banner_id=100&url=/ > 20. mailto:Monetdb-developers@lists.sourceforge.net > 21. https://lists.sourceforge.net/lists/listinfo/monetdb-develo > 22. http://www.cwi.nl/%7Eniels > 23. mailto:Niels.Nes@cwi.nl > 24. mailto:Niels.Nes@cwi.nl > 25. mailto:carl.nobile@gmail.com > 26. mailto:rbates@gmail.com > 27. mailto:carl.nobile@gmail.com > 28. mailto:rbates@gmail.com > 29. mailto:stefan@konink.de > 30. http://enigmail.mozdev.org/ > 31. mailto:carl.nobile@gmail.com > 32. mailto:carl.nobile@gmail.com > 33. mailto:carl.nobile@gmail.com > 34. mailto:carl.nobile@gmail.com > 35. mailto:rbates@gmail.com > 36. mailto:carl.nobile@gmail.com > 37. mailto:rbates@gmail.com > 38. mailto:stefan@konink.de > 39. http://enigmail.mozdev.org/ > 40. mailto:carl.nobile@gmail.com > 41. mailto:carl.nobile@gmail.com > 42. mailto:carl.nobile@gmail.com > 43. http://moblin-contest.org/redirect.php?banner_id=100&url=/ > 44. mailto:Monetdb-developers@lists.sourceforge.net > 45. https://lists.sourceforge.net/lists/listinfo/monetdb-developers > 46. http://www.cwi.nl/%7Eniels > 47. mailto:Niels.Nes@cwi.nl > 48. mailto:carl.nobile@gmail.com > 49. http://www.cwi.nl/%7Eniels > 50. mailto:Niels.Nes@cwi.nl > > ----- End forwarded message ----- > > -- > > Niels Nes, Centre for Mathematics and Computer Science (CWI) > Kruislaan 413, 1098 SJ Amsterdam, The Netherlands > room C0.02, phone ++31 20 592-4098, fax ++31 20 592-4312 > url: http://www.cwi.nl/~niels e-mail: Niels.Nes(a)cwi.nl > -- | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl | | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ | | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | | The Netherlands | Fax : +31 (20) 592-4312 |

1 0

[Monetdb-developers] Data integrity bug
by Carl Nobile 07 Oct '08

07 Oct '08

I think I have found a data integrity bug. After working with the DB for a while I notice that the equals (=) operator no longer works, however, the LIKE operator will still retrieve data. The only way I have found to solve this issue is to rebuild the DB and import all the data. This is obviously not a very good solution. There is an application sitting on top of the DB that has been the cause of this, but as you can see below mclient also gives the wrong results. Bouncing the server does not fix it. You should know the only odd thing the application does is dynamically add and drop columns to one of the tables--the reason we are using MonetDB. This allows us to have an essential flat 2nd normal form table with 4th normal form capabilities. sql>SELECT "USERNAME","LAST_UPDT_CID","CREATE_DT" from "BUSINESS_USER_PROFILE" WHERE "USERNAME"='Carl'; sql>SELECT "USERNAME","LAST_UPDT_CID","CREATE_DT" from "BUSINESS_USER_PROFILE" WHERE "USERNAME" like 'Carl'; +-----------------+-------------------------+---------------------------------------+ | USERNAME |LAST_UPDT_CID |CREATE_DT | +==========+==============+======================+ | Carl |GVE |2008-10-04 20:47:54.275000 | +----------------- +------------------------+---------------------------------------+ We are still in development of this project, but need to release our code in a month or so. Any ideas what is going on here? ------------------------------------------------------------------------------- Carl J. Nobile (Software Engineer) carl.nobile(a)gmail.com -------------------------------------------------------------------------------

4 10