2009/7/23 Vivien Malerba <[email protected]> > > > 2009/7/23 Bas Driessen <[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 for the quick analysis, I'll look at this problem ASAP. > > In the meanwhile, you can run the very same SELECT statements which are > being run when doing a partial meta store update in a psql console and see > where the problem comes from. For this, all you have to do is set the > GDA_CONNECTION_EVENTS_SHOW environment variable to COMMAND, which will show > you all the statements run along with the variables' values when run (the > variable's values come first in the dump). > > I think the culprit is the I_STMT_TABLE_NAMED SQL statement which you'll > find at the beginning of the gda-postgres-meta.c file. I seems the problem > is with the permission denied to access the pg_authid catalog table. >
Maybe replacing pg_authid with pg_roles is OK. > > > Vivien > > >
_______________________________________________ gnome-db-list mailing list [email protected] http://mail.gnome.org/mailman/listinfo/gnome-db-list
