I am refactoring an SQL script that will eventually be called by another process.
Part of the script takes an input (hard coded in the example screenshot below).
The input is used to construct a schema name of the form base_nameYYYYmm (the schema is known to exist: it's created by Python when the data is fetched).
Since the 'target' schema changes every month, the script then does a set_config('search_path'), prepending the new schema name to the old search_path. This is a strategy I've used for a decade.
The screenshot below is from when - in response to DataGrip appearing to ignore the new search_path - I changed the set_config() so that it aims for a search_path that has nothing but the new schema and 'public' (where some custom functions are stored)
When I run 'SHOW search_path' after the set-config(), DataGrip claims that it's set how I want it, as shown.
Now... there's already a schema 'base_name'. That is to say, if the new schema is xyz201910, then the 'base_name' schema is xyz.
OK, so according to DataGrip, my search_path has nothing but the new schema and 'public'.
If I run `SELECT * from [table] LIMIT 20` where [table] exists in the new schema, I do not get results from that schema unless I fully-qualify the SELECT (i.e., use the schema name as well as the table name).
Instead, I get results from the 'base_name' schema, which is not even supposed to be in the goddamn search_path. (The green block below redacts the name of the 'base_name' schema; its usedinst table has portfolio components that are very distinct).
So question: why is this happening?
This is literally a deal-breaker for me: if I can't reliably parameterise the environment, then DataGrip is less useful than pgAdmin.(Sick burn, I know)
inb4 anyone suggests that I set the schemas by hand in the connection config every time: that's not a solution. Nor is using fully-qualified table names - there are 400 queries in some files: changing them by hand is a non-starter, and global replace is not good enough, particularly when search_path will do the job in production.
In the past I have frequently had weird results in DataGrip (i.e., results that didn't agree with what was fetched from the database by PHP, R or Python, using the same SQL queries run against the same database).
Now I surmise that DataGrip has always just been ignoring search_path directives, and has been using the wrong schema, except by coincidence (e.g., where the new schema appears first in an alphabetic list of schemas with the same table name in them).
So I let my subscription to all my JetBrains stuff lapse on the 21st, when this problem first became clear to me; I cannot have a tool that ignores configuration directives, because it can mean that the wrong schema gets its portfolio updated - which could get my firm sued to oblivion.
SERIOUSLY? No 'code' functionality in this interface? Sheesh.