babelfish-for-postgresql / babelfish_extensions / 13546613638

77%

Build Type

push

github

Committed by web-flow

Commit Message

fix object resolution inside pltsql routines (#3394)

When an non fully qualified object is used inside a pltsql routine, it must get resolved to the logical database that contains the routine being executed. If the schema is also not specified then the object must be first looked inside the procedure's schema then the dbo schema of the database.

For DDL and EXEC statements the order of schemas is different
<default_schema_of_user>, <dbo_schema_of_database>, sys, pg_catalog

We achieve this behaviour by maintain two global static variables.

    current_db_search_path - search path for the current active logical database for the current login
    pltsql_search_path - search path of the inner most executing routine

Now based on the statement being executed, we will decided what the search path should be inside dispatch_stmt().
This is needed since search path is a transactional GUC and setting it once inside a procedure is not enough since any rollback or error could reset the search path to its previous value. Also attempts at making search path a scope based GUC failed since engine internally sets the search path for some cases and expects it work transactionally.

Some other changes included in this PR

    Merged set_session_properties function into set_cur_user_db_and_path. Just needed pass one more argument.
    "sp_" prefixed procedures called like db.dbo.sp_ & db..sp_ must also look into dbo schema of master database and sys schema for the procedure even though the db name and schema has been specified.
    We fix this by removing the db name and schema name at ANTLR parsing.
    Non pltsql "sp_" system procedure in master.dbo schema will not have tsql error handling. After the fix we did in 2, we can just remove this sp_ procedures in master.dbo schema. Since master.dbo.sp_* will anyway look into sys schema as well now.
    db_id() & db_name() were defined as immutable which is not correct, since db_id can change in the same session.
    Use... (continued)

Run Details

179 of 184 new or added lines in 12 files covered. (97.28%)

2 existing lines in 2 files now uncovered.

47134 of 62837 relevant lines covered (75.01%)

188139.78 hits per line