2009/7/24 Bas Driessen <[email protected]> > On Fri, 2009-07-24 at 13:55 +0200, Vivien Malerba wrote: > > > > 2009/7/24 Bas Driessen <[email protected]> > > > On Fri, 2009-07-24 at 13:47 +0200, Vivien Malerba wrote: > > > > 2009/7/24 Bas Driessen <[email protected]> > > > > 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. > > > > > ... > ... > ... > > > 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. > > > 1 step closer. Yes, the pg_authid was a problem as well, but I bypassed > that for now by giving it select access for the end-user (but yes this > should be addressed as well) > > > The following SQL: > > select * from information_schema.schemata; > > This returns nothing when running as normal user in the psql shell, but as > a superuser returns the following: > > # select * from information_schema.schemata; > catalog_name | schema_name | schema_owner | > default_character_set_catalog | default_character_set_schema | > default_character_set_name | sql_path > > --------------+--------------------+--------------+-------------------------------+------------------------------+----------------------------+---------- > stock | pg_toast | postgres > | | > | | > stock | pg_temp_1 | postgres > | | > | | > stock | pg_toast_temp_1 | postgres > | | > | | > stock | public | postgres > | | > | | > stock | information_schema | postgres > | | > | | > stock | pg_catalog | postgres > | | > | | > (6 rows) > > > This is correct from a Postgresql point of view. They are not "supposed to > provide the same results". Per SQL99, the schemata view is supposed to > identify the schemata in a catalog that is owned by a given user. The SQL > definition in the spec makes it clear that it only shows schemas owned by > CURRENT_USER or a role that CURRENT_USER is a member of. Can this be > resolved in libgda not to use the schemata table? > > > Yes, instead we could use the pg_catalog.pg_namespace table, along with > the pg_catalog.pg_roles as a replacement for pg_authid. In fact the > information_schema.schemata is defined as: > > SELECT current_database()::information_schema.sql_identifier AS > catalog_name, n.nspname::information_schema.sql_identifier AS schema_name, > u.rolname::information_schema.sql_identifier AS schema_owner, > NULL::character varying::information_schema.sql_identifier AS > default_character_set_catalog, NULL::character > varying::information_schema.sql_identifier AS default_character_set_schema, > NULL::character varying::information_schema.sql_identifier AS > default_character_set_name, NULL::character > varying::information_schema.character_data AS sql_path > FROM pg_namespace n, pg_authid u > WHERE n.nspowner = u.oid AND pg_has_role(n.nspowner, 'USAGE'::text); > > > > Just did a quick test with your SQL statement (SELECT > current_database..... etc) , but same problem. postgres user returns data. > Normal user returns no data. > > > This is expected as the SQL above is the definition of > information_schema.schemata. This was just to illustrate the filtering on > the current role. > > > Understood. pg_catalog.pg_namespace table and pg_catalog.pg_roles are > accessible to the normal user (just tested to confirm), so it has my thumbs > up. >
Ok, then I'll do this ASAP, unless you want to do it yourself (all the statements are located in the beginning of the gda-postgres-meta.c file)? Vivien
_______________________________________________ gnome-db-list mailing list [email protected] http://mail.gnome.org/mailman/listinfo/gnome-db-list
