[ADAP-726] [Feature] Remove references to pg catalogs in hard-coded queries

[jiezhen-chen]

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-redshift functionality, rather than a Big Idea better suited to a discussion

Describe the feature

Migrate away from using pg_ catalogs in hard-coded sql queries. If there is an existing redshift API call, leverage the redshift driver to fetch the data. Alternatively, re-write the hard-coded sql queries to use standardized data discovery catalogs such as information_schema.

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

[alison985]

I don't think we want to get rid of svv mentions at the least.

These seem to be the latest on this issue: See dbt-labs/dbt-core#17 dbt-labs/dbt-utils#752 dbt-labs/dbt-postgres#53

@jiezhen-chen when searching on this issue I think I saw you do a PR in relation to these issues, so please correct my understanding if I'm missing something. Or perhaps the description of this issue would benefit from an update for clarity?

[jiezhen-chen]

@alison985 you're right. Updated the description of this issue.
We're migrating away from using pg_ catalog tables as Redshift has migrated away from postgres. Instead, we're going to leverage information_schema, native redshift tables, or redshift_connector API calls.

[mikealfare]

I'm re-opening this; we only resolved one piece so far, get_columns_in_relation.