Re: [gnome-db] Partial Meta Store update.

[Vivien Malerba <vmalerba gmail com>]

2009/7/23 Vivien Malerba <vmalerba gmail com>

2009/7/23 Bas Driessen <bas driessen xobas com>

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 <bas driessen xobas com>

On Mon, 2009-07-20 at 13:49 +0200, Vivien Malerba wrote:

2009/7/20 Bas Driessen <bas driessen xobas com>

On Mon, 2009-07-20 at 10:52 +0200, Vivien Malerba wrote:

2009/7/20 Bas Driessen <bas driessen xobas com>

On Sun, 2009-07-19 at 13:31 +0200, Vivien Malerba wrote:

2009/7/19 Bas Driessen <bas driessen xobas com>

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