Word of caution for those using Toad Data Point for Vertica: SEARCH_PATH does not work properly
Awhile back I'd reported a bug in how Toad Data Point handles Vertica's SEARCH_PATH.
http://www.toadworld.com/products/toad-data-point/f/36/t/31499
In a nutshell, until the bug is resolved, users should be aware that Toad Data Point will override user's SEARCH_PATH with the single schema in the dropdown. Literally every SQL command is preceded by this SEARCH_PATH override, so if my dropdown-schema is SCHEMA_FOO, running these two commands consecutively:
SET SEARCH_PATH SCHEMA_BAR;
SHOW SEARCH_PATH;
will return SEARCH_PATH of:
'SCHEMA_FOO, v_catalog, v_monitor, v_internal'
This is tangentially related to my previously-reported bug on UDAF schema in views. Combined, the TOAD Data Point SEARCH_PATH-override bug and the UDAF-schema bug prevent me from joining any two views which have UDAF from separate schemas (I can only point TOAD to one schema, and whichever I choose, the view with UDAF from the other schema will give error).
Comments
Just tried out the latest bits and I can confirm that the 'bug' is fixed.
If you launch your editor before selecting a particular schema in the navigation pane then search_path now works as the user requested. In other words the path comes from the 'set search_path to' statement instead of the dropdown.
However, if you first select a schema in the nav pane and then open your sql editor it will use the dropdown to determine the search path and ignore the 'set search_path to' statement.
In othe words, whether you control the search_path schemas by the dropdown list or by the 'set' statement all comes down to whether you had selected the generic 'Schema' or a particular named schema before you launched the SQL editor.
What version of TOAD, may I ask? The behavior of Version 3.8 is that you are forced to set a schema (schema drop-down is populated at all times, and there is no "high-level" choice that would represent all schemas). Even if I delete the entry in connection properties, as soon as I connect, TDP defaults the dropdown to the first schema per alphabetical ordering.
I used TDP version 4.0.0.613 and please look at the screenshot attached to my previous post to see what I mean by selecting the generic 'Schema' in the navigation pane panel which is usually on the lower left.
HAHA! Mystery solved!
Here's the deal (and hopefully some insight into my confusion).... If you are using the 'Tabbed' option for displaying objects in the Navigation Manager (the default option for my installation), then there is no "default" / high-level schema to select. You must always choose a specific schema. However, if you switch to the "Treelist" option, then the Navigation Manager is re-painted in a way that presents a high-level schema to select. Once put into 'Treelist' mode, your approach worked like a charm.
As a final/parting curiosity: can you confirm that the "unworkably buggy" behavior still exists in TDP 4.0 - i.e., if you set your Navigation Manager to use 'Tabbed':
1) you have no choice but to pick a specific schema of the database and
2) every SQL you try to run is usurped with a command that resets your search_path to match that value
?
I tried it out. In tabbed mode I found that it goes exclusively on what is selected in the dropdown list and ignores any SET statement and also ignores what you have selected in the tabbed navigation pane.