On Mon, Oct 06, 2008 at 01:45:35PM -0500, Ross Bates 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
I found a problem within the monetdb kernel code which caused this
problem, ie its fixed now in the head of cvs.
Ross thanks for the script it really helps solving such bugs. I have
just one more request (general ie not just for you). Please file bug
reports on the sourceforge bugtracker. It make keeping track on
bugs so much easier.
Niels
> --------------------------------------
> 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
> <[EMAIL PROTECTED]> 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
> <[EMAIL PROTECTED]> 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
> <[EMAIL PROTECTED]> 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 -
> [4]http://enigmail.mozdev.org
>
> iEYEAREKAAYFAkjpL2AACgkQYH1+F2Rqwn3k9gCeKg+p+n+nQE+dPTLDLnZe
> 9OZ9
> SIAAnimb0zS4XvWtiKncWEwh3RU7DMJe
> =zHeG
> -----END PGP SIGNATURE-----
>
> --
> ---------------------------------------------------------------
> ----------------
> Carl J. Nobile (Software Engineer)
> [EMAIL PROTECTED]
> ---------------------------------------------------------------
> ----------------
>
> References
>
> 1. mailto:[EMAIL PROTECTED]
> 2. mailto:[EMAIL PROTECTED]
> 3. mailto:[EMAIL PROTECTED]
> 4. http://enigmail.mozdev.org/
> 5. mailto:[EMAIL PROTECTED]
> -------------------------------------------------------------------------
> 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
> http://moblin-contest.org/redirect.php?banner_id=100&url=/
> _______________________________________________
> Monetdb-developers mailing list
> [email protected]
> https://lists.sourceforge.net/lists/listinfo/monetdb-developers
--
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: [EMAIL PROTECTED]
-------------------------------------------------------------------------
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
http://moblin-contest.org/redirect.php?banner_id=100&url=/
_______________________________________________
Monetdb-developers mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/monetdb-developers
