Re: [gnome-db] Partial Meta Store update.
Bas Driessen <[email protected]>
| Newsgroups | gmane.comp.gnome.db |
|---|---|
| Organization | Xobas Software |
| Message-ID | <[email protected]> |
On Wed, 2009-07-22 at 21:14 +1000, Bas Driessen wrote: > On Wed, 2009-07-22 at 10:10 +1000, Bas Driessen wrote: > > > On Tue, 2009-07-21 at 21:34 +0200, Vivien Malerba wrote: > > > > > > > > > > > 2009/7/20 Bas Driessen <[email protected]> > > > > > > > > > On Mon, 2009-07-20 at 13:49 +0200, Vivien Malerba wrote: > > > > > > > > > > > > > > > 2009/7/20 Bas Driessen <[email protected]> > > > > > > > > > > > > On Mon, 2009-07-20 at 10:52 +0200, Vivien > > > > Malerba wrote: > > > > > > > > > > > > > > > > > > > 2009/7/20 Bas Driessen > > > > > <[email protected]> > > > > > > > > > > On Sun, 2009-07-19 at 13:31 +0200, > > > > > Vivien Malerba wrote: > > > > > > > > > > > > > > > > > > > > > > > 2009/7/19 Bas Driessen > > > > > > <[email protected]> > > > > > > > > > > > > Hello, > > > > > > > > > > > > Question regarding partial > > > > > > meta store update. I found > > > > > > that the following to update > > > > > > a single table does not > > > > > > appear to work: > > > > > > > > > > > > g_print ("\nPartial > > > > > > metastore update for table > > > > > > '%s'...\n", TABLE_NAME); > > > > > > GdaMetaContext > > > > > > mcontext = {"_tables", 1, > > > > > > NULL, NULL}; > > > > > > > > > > > > mcontext.column_names = > > > > > > g_new (gchar *, 1); > > > > > > > > > > > > mcontext.column_names[0] = > > > > > > "table_name"; > > > > > > > > > > > > mcontext.column_values = > > > > > > g_new (GValue *, 1); > > > > > > g_value_set_string > > > > > > ((mcontext.column_values[0] > > > > > > = gda_value_new > > > > > > (G_TYPE_STRING)), > > > > > > TABLE_NAME); > > > > > > if (! > > > > > > gda_connection_update_meta_store (connection, &mcontext, &error)) > > > > > > return -1; > > > > > > > > > > > > After this, my meta store is > > > > > > NOT updated. > > > > > > > > > > > > > > > > > > This may depend on the actual value > > > > > > of TABLE_NAME, specifically if it is > > > > > > a mix of upper and lower case, or if > > > > > > it's a reserved keyword. The proper > > > > > > way is: > > > > > > tmp = gda_sql_identifier_quote > > > > > > (TABLE_NAME, cnc, NULL, TRUE, > > > > > > FALSE); // or with the last argument > > > > > > being TRUE, see the doc > > > > > > g_value_set_string > > > > > > ((mcontext.column_values[0] = > > > > > > gda_value_new (G_TYPE_STRING)), > > > > > > tmp); > > > > > > g_free (tmp); > > > > > > > > > > > > Note that this is brand new (just > > > > > > been sorted out) and will require > > > > > > the master or LIBGDA_4.0 branches up > > > > > > to date. > > > > > > > > > > > > > > > > > > > > > > > > > > > > Thanks Vivien, but this unfortunately > > > > > is not still not working. Let me > > > > > explain my testing scenario and if I > > > > > understand this the correct way. > > > > > > > > > > -1 I have a database with a table > > > > > called "groups" > > > > > -2 I run the > > > > > gda_connection_update_meta_store to > > > > > update the meta store. > > > > > -3 I look into the meta store with > > > > > sqlite3 and execute "select * from > > > > > _tables". I can see the "groups" table > > > > > in that list. > > > > > > > > > > > > > > > So far so good:) > > > > > > > > > > > > > > > > > > > > -4 I add a table called "parts" > > > > > -5 I run the code above (with your > > > > > modification) where the TABLE_NAME is > > > > > set to "parts" (without quotes). > > > > > -6 I execute "select * from _tables" > > > > > and I expect to see the "parts" entry, > > > > > but it is not there. > > > > > > > > > > > > > > > It should be there... > > > > > > > > > > > > > > > > > > > > -7 I execute the > > > > > gda_connection_update_meta_store to do > > > > > the full update. > > > > > -8 I execute "select * from _tables" > > > > > and I expect to see the "parts" entry > > > > > and now it is there. > > > > > > > > > > My questions: > > > > > > > > > > -1 Is my understanding of how this > > > > > mechanism should work correct? > > > > > > > > > > > > > > > Ye, it is. > > > > > > > > > > > > > > > > > > > > -2 Anything (obvious) that is not in > > > > > the code above? > > > > > -3 I use > > > > > "gda_meta_store_new_with_file" to open > > > > > my (sqlite3) meta store database. Is > > > > > it possible that somehow it is mixing > > > > > up the internal (memory) database with > > > > > the external one? > > > > > > > > > > > > > > > I don't think so, but I'd prefer to have some > > > > > actual code to look at before I can say... > > > > > > > > > > If you send me a standlone program which shows > > > > > the bug, I'll make the necessary corrections > > > > > (either to Libgda or to your code). > > > > > > > > > > > > > > > > > > > > Attached a small application that demonstrates the single table problem. Set up as follows: > > > > > > > > > > > > (postgresql) database with 1 more tables > > > > data source called "stocksql" (or change the > > > > connection name in the source). > > > > > > > > The program performs the following steps: > > > > > > > > -1 Open the connection > > > > -2 Open the meta store > > > > -3 Drop table "xparts" (if exists) > > > > -4 Build complete meta store > > > > -5 Create table "xparts" > > > > -6 Update SINGLE table meta store. > > > > > > > > > > > > After this open: sqlite3 /tmp/single.db > > > > > > > > select * from _tables > > > > > > > > There should be an entry for "xparts", but it is > > > > not there. > > > > > > > > Is this enough input? > > > > > > > > As a comment, I quickly put this together, so > > > > the error handling and program style is not the > > > > nicest :) Also I did not use DDL calls for drop > > > > table etc as that is not the focus anyway. > > > > > > > > > > > > > > > > > > > > It works for me (I've had to modify "gchar tableName;" > > > > to "gchar *tableName;" but it's the only modification > > > > I've done): > > > > > > > > c0> select * from _tables where table_name = 'xparts'; > > > > table_catalog | table_schema | table_name | table_type | > > > > is_insertable_into | table_comments | table_short_name | > > > > table_full_name | table_owner > > > > --------------+--------------+------------+------------+--------------------+----------------+------------------+-----------------+------------ > > > > stocksql | public | xparts | BASE TABLE | > > > > TRUE | | xparts | > > > > public.xparts | vivien > > > > (1 row) > > > > > > > > > > > > > > > You are right about the *tableName of course. Not sure why > > > that * got dropped. > > > > > > > > > > > > > Which version of PostgreSQL and libgda are you using? > > > > > > > > > > > > > > > > I am on the latest LIBGDA_4.0 git branch. (so I am NOT on > > > master, perhaps that may be the issue?) > > > > > > I am using postgresql 8.3.7 (default with Fedora 11) > > > > > > $ rpm -qa | grep postgresql > > > postgresql-devel-8.3.7-1.fc11.x86_64 > > > postgresql-python-8.3.7-1.fc11.x86_64 > > > postgresql-libs-8.3.7-1.fc11.x86_64 > > > postgresql-server-8.3.7-1.fc11.x86_64 > > > postgresql-8.3.7-1.fc11.x86_64 > > > > > > What additional permissions (grants) do I need in > > > Postgresql to be able to retrieve the data that is used > > > the build the meta store? For testing now I have assigned > > > an administrator role, but that is probably not a good > > > idea in production? > > > > > > > > > No specific permission is required, except that the pg_catalog and > > > information_schema schemas need to be readable (but I think it's > > > the case all the time). Moreover, the meta data retreival works > > > when updating all (calling gda_connection_update_meta_store() with > > > a NULL context). > > > > > > I don't see why it does not work... > > > > > > What you can do is uncomment the line 2248 of gda-meta-store.c to > > > define DEBUG_STORE_MODIFY, recompile and run your code again. > > > You'll then have a lot of information about the update process of > > > the GdaMetaStore, and maybe you'll see what's wrong. Be aware that > > > when running gda_connection_update_meta_store(), the whole process > > > takes place in a transaction so if something fails, then the meta > > > store is reverted to what it was before the call. > > > > > > If you want you can send me the output it produces so I can have a > > > look. > > > > > > Output as follows: > > > > CURRENT: > > catalog_name > > ------------ > > stock > > (1 row) > > ------- BEGIN > > FIND row 0(/0) returned row 0 (unchanged) > > Suggest update data into table '_schemata': [catalog_name => stock] > > CURRENT: > > catalog_name | schema_name | schema_owner | schema_internal > > -------------+-------------+--------------+---------------- > > (0 rows) > > NEW for table _schemata: > > catalog_name | schema_name | schema_owner | case > > -------------+-------------+--------------+----- > > (0 rows) > > wrapped as: > > catalog_name | schema_name | schema_owner | case > > -------------+-------------+--------------+----- > > (0 rows) > > ------- COMMIT > > > > > > This does not look healthy as all is empty. Should this list > > something from the current Meta Store? > > > OK, found the issue. It appears to be a permission problem in > Postgresql. If I configure the data source (cnc) to connect as user > "postgres" (administrator account), then the partial update works OK. > If I do it as the "normal" user, it does not work. This is strange as > the FULL update does work for that user. What additional permission is > required for the 'normal' user? This may be a little bit out of the > libgda scope, but half kind of overlapping as libgda should either > fail for all cases if it can not reach the system tables or work in > all cases. Further to this. Tried to work it out from Postgres, but the only way the libgda partial meta store updates work is if I set the normal user as superuser: ALTER USER bas with SUPERUSER then all works OK, however it is not a good idea to have superuser rights to a normal user for the obvious reasons. When I switch back to normal user mode: ALTER USER bas with NOSUPERUSER then all fails again with the empty debug table output above. I need to work out which Postgresql system tables libgda tries to access when doing a partial update. Any pointers of how to get this done quickly/which area to look? Any other postgresql users on this list who has come across this? Thanks, Bas. _______________________________________________ gnome-db-list mailing list [email protected] http://mail.gnome.org/mailman/listinfo/gnome-db-list