Re: [gnome-db] [Fwd: Re: Partial Meta Store update.]
Bas Driessen <[email protected]>
| Newsgroups | gmane.comp.gnome.db |
|---|---|
| Organization | Xobas Software |
| Message-ID | <[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. Bas. _______________________________________________ gnome-db-list mailing list [email protected] http://mail.gnome.org/mailman/listinfo/gnome-db-list