Buggy behavior: TDP 3.8 overwriting Vertica SEARCH_PATH with "default schema"

Toad Data Point & Toad Intelligence Central
1

Where most other databases use CURRENT_SCHEMA, HP Vertica uses a SEARCH_PATH. SEARCH_PATH allows users to list multiple schemas for which an object should be searched during a particular session.

So if my current session’s SEARCH_PATH is:

Schema1, Schema2, public

If I run SQL “SELECT * FROM FOO;” over that session, Vertica will attmpt to resolve the schema of FOO by looking for Schema1.FOO, then Schema2.FOO, and finally, public.FOO.

Toad Data Point has 3 bugs in how it handles Vertica’s SEARCH_PATH feature.

Bug#1 (minor): Connecting Toad Data Point to a Vertica Database requires an ODBC connection. One of the properties of a Toad Data Point ODBC connection is “Database”. For Vertica ODBC connections, this is an “invalid option” bug. For Vertica connections, the choice of database is built into ODBC source definition (the database is part of the ODBC URL). This option should either be invisible, or as a hard-coded/non-editable field that just lists echos the database portion of the Vertica connection URL.

Bug #2 (minor): In addition to being unnecessary, this “Database” field is also mislabeled. As evidenced by the dropdown list after clicking the […] button, the field is actually referring to “the default schema that is set after connection is established”. This bug (the label says Database when it means Default Schema) can be verified directly: when configuring a new Vertica ODBC connection in Toad Data Point, when I click the “…” next to “Database”, the drop-down of values is actually all the schemata in the Vertica database (not the “list” of databases which, as noted in Bug 1, can only be database string in the URL).

Bug #3 (major) The real problem is how Toad handles the “default schema” for ODBC-Vertica connections. Say I choose “Schema2” for the the “Database” option of a Vertica connection. Based on this setting, when I open a new connection, Toad sends the following SQL string over ODBC: “SET SEARCH_PATH=Schema2”.

What makes Bug#3 such a nasty bug is that

  1. This command gets run (automatically) after all other connection settings are applied. So if my Vertica user account has default SEARCH_PATH of “Schema1, Schema2, Schema3, public”, that default is overwritten by Toad Data Point to just “Schema2”. Even if I make my desired SEARCH_PATH using the ODBC Connection String, this bug overwrites that value.

In other words, if I put “ConnSettings=SET SEARCH_PATH TO Shema1, Schema2, Schema3, public” in Connection String, and set that same SEARCH_PATH for my user account, after I connect to Vertica, and run SHOW SEARCH_PATH;, I will see the search-path is just “Schema2”. The behavior can also be observed after connection is established. If the “default schema” (hover-text = “Select the schema/instance to associate with the statement”) is changed from Schema2 to Schema3, behind the scenens the command “SET SEARCH_PATH=Schema3” is sent.

  1. This behavior cannot be disabled. This is my main beef. The behavior pretty much completely prevents using SEARCH_PATH functionality in Toad Data Point; which effectively eliminates any ability to use Vertica’s SEARCH_PATH feature - a sufficiently large obstacle to entice me to find an alternate IDE for Vertica analysis (and recommend the same to colleagues).
2

Hi KevinAK,

thank you for your feedback. I agree with you that this should be annoying. I created task QAT-8944 for fix our Connection dialog and investigating of Search_path feature. I don’t see problem with setting schema in our connection dialog because if you leave Default option for Database (schema) connection string is without this option. Problem is that our Editor is designed for work with one schema in time and you have to choose one (drop down box on top of Editor). We will try find some solution.

Filip

3

Hi Filip - sorry for delayed reply. Glad to see this is getting proper attention. I would recommend, for consideration, simply offering an option to disable this feature (if not disabling by default for Vertica connections). So far as I can tell, the main behavior is that, when using the editor, every SQL statement is pre-empted with command to set the CURRENT_SCHEMA (in Vertica parlance, SEARCH_PATH) to match whatever is in the dropdown. If that behavior were disabled (for Vertica, it really is needless - each user can configure/customize their own SEARCH_PATH for their database account and/or for a given ODBC connection). SEARCH_PATH is really quite nice; it eliminates most all of the scenarios for which a given user would need to toggle their schema in other database products.

4

In discussing this issue on a Vertica forum, a workaround was developed:

  1. In Navigation Manager, change display option of Object Explorer tab to ‘Treelist’.

  2. In Connection Properties dialog, clear the “Database” box.

  3. Save settings and re-connect.

  4. Check that the Object Explorer tab of Navigation Manager now lists a high-level element, “List of Databases or Schemas [sic]”, and that that high-level element is selected. So long as this choice remains selected, Vertica’s SEARCH_PATH will work properly.

5

FYI, QAT-8944 got fixed and is part of TDP 4.1 release. This is due to be released Dec 8. We added in the SQL Editor a drop down that says “I will set the schema/database myself” and this bypasses the setting of the schema that we do. You just set the search path yourself in the script.

6

We provided a new feature in TDP 4.1 to address this set of issues. When connected to any ODBC type connection such as Vertica we now offer a drop down that prevents the SQL Editor for setting the current schema. Now your SET SEARCH_PATH will work.

do not set schema.png
do not set schema.png1339×219 16.9 KB