Skip to main content
  • EN

Dear,

 

I have a workspace that reads a in a postgis database with an SQLExecutor. It all runs smooth.

 

However I want to make my workspace generic, so that it can use other input data from different databases/schemas in the future. That is why I put everything to read in user parameters. That being said I encountered the following problem.

 

My workspace runs fine with the original data. However when I change the name of the schema of my input data, and I change it in the SQL executor accordingly, I get the following error: 'Error executing SQL command ('select version(), postgis_version()'): 'ERROR: function postgis_version() does not exist'

 

Error_message_changing_input_data 

When I try to read in the schema with the changed name with a PostgreSQL reader, it works just fine. However this is not desired, since my data contains geometry.

 

Does somebody has any clue what can be the cause for this?

 

Attached you can find the logfile and some screenshots. I tried running it both on the 2021 version of FME desktop as the 2022 version.

 

 

Thanks in advance,

Matthias

Most likely you haven't created PostGIS extension onto the database yet. Make sure that the database has the PostGIS extension with pgAdmin.

pgadmin-database-extensions" data-fileid="0694Q00000ACYb5QAHIf that's already been done, it sounds like grant issues. Make sure your user has sufficient rights.

To debug, try first to give the user full rights on the database schema. If that works, it's definitely a rights issue and you need to backtrack to find what's missing.

 

Another potential solution - updating the search_path for the user.

 

If i've provided an answer that you're happy with, please mark it as 'Best Answer'. This marks the post as answered and helps the community find the solution. If I didn't provide a solution, please let me know if I can help further. Thanks and take care!

 

 

Hi Carmijo,

 

To come back to you.

 

1) I have created a postgis extension. Like you can see in the screenshot I attached earlier. Essentially I'm also working from the same database and schema. The only thing I did is changed the name of the schema, and tried to read in the schema with a different name then public. There it went wrong.

 

2) I don't think it is a grant issue, because PUBLIC has access to read, write and usage. See image below. Moreover I just changed the name of the working schema public, and I didn't change any rights. Rights_schema 

3) I don't have much experience with setting the search path. I tried some things, but nothing seem to work. However why would I have to search path again if I just change the name of a working schema?

 

At the moment I still cannot solve the problem.

 

Kind regards,

Matthias

Hi Carmijo,

 

To come back to you.

 

1) I have created a postgis extension. Like you can see in the screenshot I attached earlier. Essentially I'm also working from the same database and schema. The only thing I did is changed the name of the schema, and tried to read in the schema with a different name then public. There it went wrong.

 

2) I don't think it is a grant issue, because PUBLIC has access to read, write and usage. See image below. Moreover I just changed the name of the working schema public, and I didn't change any rights. Rights_schema 

3) I don't have much experience with setting the search path. I tried some things, but nothing seem to work. However why would I have to search path again if I just change the name of a working schema?

 

At the moment I still cannot solve the problem.

 

Kind regards,

Matthias

Hi Matthias, It seems like you're doing all that's needed. i'm afraid i don't have much more to offer here. Sorry you're having issues and good luck in finding a solution.

Hi Carmijo,

 

Thank you anyway for your response.

I found the solution with some help of my colleague. The problem is the public schema. If I don't rename the public schema and just import my data in a new schema I am perfectly able to read in the data of that new schema.

 

In other words there seems to be going something wrong if you rename your public schema and then try to read in with a postgis reader. I don't know why exactly though.

Reply


Community Stats

30,162
Posts
114,508
Replies
37,818
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.