[postgresql] How to not change search_path when selecting database

[michalk-k]

System information:

• Win/Linux
• 7.3.5

Connection specification:

• Postgresql
• org.postgresql.Driver
• No

Describe the problem you're observing:

How to disable changing search_path every time when selecting database if several databases are contained by postgresql cluster? The only workflow I know to change database is to use of this selector:

1. select database from database selector (from drop list or from popup window)
2. using right drop-box of popup window, select database then schema, clicking on the latter to apply database change

Clicking on schema makes sending 'SET search_path = 'selected_schema'; query to database. I would like to stick with default database setting which is set on database side.

How can I achieve that?

[kseniiaguzeeva]

You describe the expected DBeaver behaviour. We use search_path for selecting database. If you have any ideas how to do it by different way, we would be happy to know it.

[michalk-k]

We use search_path for selecting database

search_path is not a command to select database. It sets one or more schemas which are looked into for objects which are not fully qualified (lacks of schema name in identifier).

example

SET search_path = '';
SELECT * FROM some_table; -- error
SELECT * FROM public.some_table; -- ok
SET search_path = 'public';
SELECT * FROM some_table; - ok
-- mutliple schemas:
SET search_path = 'public,my_schema';
SELECT * FROM my_table_in_my_schema;

As you can see, it has nothing to do with database.

Default search_path is set in server configuration. If programmer, or code depends on that, changing search_path (ie in DBeaver during selecting database) might make the code not working at all.

Personally I encourage anyone to use fully qualified identifiers always. But postgres has other features which depends on search_path.

From my point of view it's unwanted and unneeded behaviour. I can assume it comes from mysql world where back in time databases turned into schemas but it's not the same and should not be treated the same way. Mostly because of mentioned fact that it changes default behaviour set by the server. In postgresql you can connect to database without explicitly saying which schema to connect to (unlike in mysql where you has to issue USE command which connects to database, but at the same time all databases behaves like schema - from SQL point of view)

If someone needs this (I can imagine such scenarios) it could be available as option. But by default SET seach_path should not be called during connection to database.

TBH the need of clicking on schema just to connect database seems to awkward too. It likely comes from mysql workflow too, isnt' it? In case of postgres, connecting to database should be executed by clicking on database (not on schema). In such case there is no reason to issue SET seach_path at all.

Setting search_path by selecting schema can be still available by clicking on schema, but keep in mind, it will overwrite current search_path setting with single schema name, while search_path can handle multiple schemas. So such functionality is still limited. For example it might points to some utility schema, then user probably would like to append selected schema to the default instead of overwriting it. Current workflow brakes it too.

So.. there are a few options

1. redesign workflow of connecting to the database, getting rid of need of selecting schema.
2. consider removing schema selector from connection window at all. I think it would be my personal choice.
3. when opting for previous point, you can add optional schema selector for them who really want that (I guess it would work for users/programmers who depend mostly on user-based schemas to refer own objects without fully qualified identifier)
4. add option "disable search path call when connecting to database", or better make it opposite: by default don't issue SET search path, but add option 'Set search_path when connecting database', with alternate option to allow overwriting current search_path or to add schema to default (server-defined) one

with regards

[michalk-k]

guys, I have provided response.

[michalk-k]

Guys, is it resolved somehow or just closed because it is not going to be changed? I cannot find any related code changes.

Anyway in v21.0.4 I found a change in the behavior which I seemts to be even worse than before: While changing schemas, all subsequent ones are added to search path. I doubt it is expected behavior. I've chast checked with some old version (v7) and this odd behavior is was here before. I would really consider it as a bug.

Also when you have more databases in cluster, database change requires selecting the schema. And this schema is added to search_path too - which is not expected behavior considering that the initial goal was to change database.

[serge-rider]

Sorry, missed that. Not sure why this ticket was closed.

I can't reproduce "all subsequent ones are added to search path" issue. We leave original search path which was set after connection instantiation. And then add selected schema to the beginning of the search_path. Or replace previously selected one.

Anyhow, at the moment we don't have plans to change current behavior. It is the same for all databases - there is a concept of "default schema". It makes sense and can be changed only for SQL editors to simplify user's life (use short table names). How is this harmful? A lot of people use this feature all the time.. (including me).

[michalk-k]

As I already described some postgres functions/constructs returns different results depending on search_path. Also developer who is not aware about search_path change can write a code working on his desk, but failing when run outside DBeaver. Both might cause damage if not caught early.

I admit that this is rather inconsistency in postgres native functions but at the same time it's a database feature which we cannot change. On the other hand, I as programmer don't want the application changing environment configuration on its own. When I select database I don't want/expect changing the search path.

What about adding double click in db selector to connect to db without need to select schema?

I think, with not too much work it could satisfy those who used to selecting schema/changing search_path, as well as provide option to connect database without selecting a schema (thus without changing search_path)

I'll try to reproduce the issue mentioned in my previous post. Then create bug report. Edit: done already

[bazmap]

Hi,

I've encountered the same trouble as @michalk-k mentionned here.

I will describe a very specific example :

1. Create a database with two schema : "public" and "postgis"
2. Create the PostGIS extension into the "postgis" schema (it's always a good practice to install big extension into there own schema)
3. Set the search_path at the connection of the database to "$user, public, postgis" to allow using postgis function without qualifiying it.
4. Create a work schema "postgis_test" to try some new functions

If one of your testing function have the same name as one in the "postgis" schema : bam problem ! Ok it's very uncommon.

Another case : In my company, we developp softwares that install there own psql functions into the public schema (to be used without qualifying the function). We always encourage using qualified identifier but when in developpment it's really common to not qualifying public's function and qualifying function from other schemas. And the default behaviour of DBeaver is really annoying because it can select function of unwanted schema.

I'm aware that this behaviour of defining the actual schema into the path can be very useful but not in every situation.

In my opinion, we need a parameter in the connexion option to choose :

• keep the default schema fixed by the database
• always add the working schema to the search path, if so :
  • put the working schema at the start of the search path
  • put the working schema at the end of the search path (behaviour very interesting in my second described case)

[michalk-k]

Guys, this issue getting old, but is so annoying. It impacts every time I convert an object oid to its literal presentation.

For example, if while selecting database I click on schema some_schema then query statistics:

SELECT funcid::regprocedure::TEXT, * 
FROM pg_stat_user_functions
ORDER BY 1

All functions located in some_schema misses the schema name in their fully qualified identifier. And this is one out of hundreds of examples.

Could you please do something against changing search_path by DBeaver to the selected schema?

Or in other words: when I select database I don't want to touch default search_path (which is set by db itself)

[michalk-k]

Hello Could we change this issue from question to feature request at least? The answer on the question from the subject is already known: there is no way or option.

The current behavior of DBeaver changes connection default settings implicitly which may be problematic in specific use cases (see provided examples above). Those use cases are not only related to users workflow but are Postgres specific (see cast from oid to identifiers)

[bwoostdam]

I would like to confirm the request for the search_path behaviour change. DBeaver is wonderful but the interpretation of the use of PostgreSQL's search_path variable(setting) is indeed incorrect en is currently for me the reason not to switch to DBeaver as my 'go-to' DB-tool, although I seriously would have liked to. I administer Oracle, MSSQL, MySQL and PortgreSQL(GIS) databases.

[OlegUfaev]

According to PostgreSQL documentation (https://www.postgresql.org/docs/15/ddl-schemas.html), the default value of search_path is "$user", public. The database administrator can change the value of this parameter according to the strategies described in section "5.9.6. Usage Patterns".

The value chosen by the database administrator for the search_path parameter is security critical and the client (DBeaver) should respect it and should not change it - as this may pose a direct threat to the user and data.

Of course, the user can change the search_path value himself at any time, but it will be his explicit wish.

Currently, DBeaver completely ignores the default search_path value. For example, when the default value is search_path="$user", public, then after connecting to the database via DBeaver to the public schema, the search_path value changes to public,public,"$user". This is NOT the right behavior.

For example, pgAdmin respects the search_path value and does not change it when connecting to the database.

pgAdmin: DBeaver:

[bazmap]

Yeah, the more I think of this issue/feature request, the more I'm telling to myself that this is a really serious issue for the integrity of the database as it is really easy to made a mistake.