> > > > > > > > > > > > > > > > > > > > > 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? Bas.
_______________________________________________ gnome-db-list mailing list [email protected] http://mail.gnome.org/mailman/listinfo/gnome-db-list
