PostgreSQL database console doesn't set search_path correctly

When connecting to my postgresql database using phpstorm, the search_path is only set to one schema. The database role is configured to set 3 schema in the search_path upon connecting, and this works as expected when connecting via psql. This is done at session startup when connecting, and is not a factor of any local resource file. The fact that search_path is not set when phpstorm connects also affects table name auto completion, as the tables in the other schema are not part of the auto completion list. I've been trying to find some documentation regarding this and have come up empty, hence this post. I can confirm that the 3 schema are checked in the schema tab under database properties, and "resolve unqualified references" is checked. Checking and unchecking these, then hitting apply has no effect.

One thing I did notice was that upon initial creation of the database connection, the search_path was set correctly, but this is lost after restarting phpstorm.

Thoughts?

7 comments
Comment actions Permalink

While running the following in the database console allows it to resolve tables without specifying the schema (as expected):

set search_path to schema1, schema2, schema3;

It does nothing for table name auto completion when typing table names without a schema prefix, which is what I desire. Adding currentSchema to the jdbc connection string also has no effect.

0
Comment actions Permalink

Progress: I unchecked the other schema from the schema tab, leaving only 1 checked, and now it seems to be accepting what is coming from the server. The search_path is correct, and auto completion works as expected. Seems there is a weird bug here somewhere.

0
Comment actions Permalink

Once I tick schemas on the Schemas tab and add some of them to the Database tool window, I can get table names autocompleted even if there are some other schemas ticked too:

 

On the screenshot, I have three different schemas ticked on the Schemas tab, and 1 added to the tool window.

Are you ably to reproduce the issue since then?

0
Comment actions Permalink

Yes, I'm still experiencing the problem. The DB connection lists a total of 7 schema. All the tables I work with are in 3 of them, so I check them off along with the "Resolve unqualified references" option:

I've noticed that everything works fine upon creating the DB connection and setting the schema checkboxes at creation time, but when I restart phpstorm, the problems begin. Once I create the DB connection and restart phpstorm, I do:

In a sql editor, I run this:

Which returns this:

Upon typing a query which will use a table named "dataset", that I know to be in the gcm_admin schema, the completion options don't offer it:

if I complete the query manually and run it, it fails, since the search_path does not include the schema:

As you can see, the 3 schema appear in the database tool window:

I can fix the search_path at query time problem by simply setting it in the sql editor:

But this doesn't fix the completion issue. Can you please try creating the DB connection, setting the schema checkboxes, then restarting phpstorm and testing? I've been able to recreate this problem on both windows and Linux. I'm using the latest phpstorm 2016.1.2.

Thanks

0
Comment actions Permalink

I tried that, search_path does look different after restart, but I still can get the tables names autocompleted.
A suggestion: it seems that the Schemas workflow has been significantly re-worked in 2016.2 (you'll see the difference), would you mind installing a EAP version alongside with 2016.1.2 and check if the issue persists there?

0
Comment actions Permalink

It looks like these issues are all fixed in phpstorm 2016.2. Thanks for the attention.

0
Comment actions Permalink

I'm still encountering problems with this but I've manged to resolve them via trial and error. I wish I would have known that I had to add the schema to the database tool window (see above screen shot for what I'm calling the database tool window) before they'd be considered for completion candidates. I thought everything was managed in the config UI, but it seems that only sets the search path (the so-called "unqualified references"). Adding a note here in case anyone else runs into this problem.

0

Please sign in to leave a comment.