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 14:14 +0200, Vivien Malerba wrote: > > > > 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)? OK, I will give it a go over the weekend. _______________________________________________ gnome-db-list mailing list [email protected] http://mail.gnome.org/mailman/listinfo/gnome-db-list