Using a Postgres Database

Options
FrancineFallara
FrancineFallara Posts: 3
in Leapfrog

Our master DB manager found a magical trick today. I thought it would be nice for me to share it with you.

Based on our advancement within Datamine with our work around with the manual SQL queries we were able to figure out the problem for all our other platforms.

The common bug blocking Datamine, gOcad, Leapfrog and Geoscience Analyst within our new postgres db was an identical problem caused within all four applications.

The ODBC error message pointed falsely to what we all thought permission problems (read/write) for the user. The actual problem was simply caused by the fact that all 4 platforms do not support ‘Schemas’ within the db; i.e. causing an error of not finding the table because they were not ‘loose’ within the db but structured within a schema.

 So the trick that has solved this error for all 4 platforms:

Add the following search path query within the user’s profile:
  1.  ALTER ROLE <your_login_role> SET search_path TO a,b,c;
  2. "a, b, c," refers to the schema in which the table is located

 Examle for my user profile:francine

ALTER ROLE francine  SET search_path TO "My_Schema";

  1. Where "My_Schema" is the schema within our postgres db which contains the DDH  tables

Once this is done, I went back to LF through the same steps as usual connecting to an ODBC db, and magically, this time, I could see all the fields of my tables.

Prior to this set-up, the process would cause an automatic ODBC error saying the table was not available.

Now with the ‘search path’, the fields are visible and no more ODBC errors, and I can just click the fields I need.

So, if you have any other postgres users who have schemas in their db this is the solution.

This should be shared to your development teams.

Tagged:

Categories