[Feature][Spike] Allow adapters to specify multiple namespaces

[colin-rogers-dbt]

Is this your first time submitting a feature request?

• I have read the expectations for open source contributors
• I have searched the existing issues, and I could not find an existing issue for this feature
• I am requesting a straightforward extension of existing dbt functionality, rather than a Big Idea better suited to a discussion

Describe the feature

Currently an adapter can supply only one namespace prefix for macros. This poses a challenge if we want to sever/deprecate inheritance relationships for adapters while maintaining backwards compatibility (i.e. dbt-redshift and dbt-postgres). Instead we'd like an adapter to be able to supply the deprecated parent adapters namespace along with it's own.

Relevant code section: https://github.com/dbt-labs/dbt-core/blob/main/core/dbt/context/providers.py#L143

Describe alternatives you've considered

No response

Who will this benefit?

No response

Are you interested in contributing this feature?

No response

Anything else?

No response