10000 get_table_oid results in an error for external tables · Issue #304 · sqlalchemy-redshift/sqlalchemy-redshift · GitHub
[go: up one dir, main page]
Skip to content

get_table_oid results in an error for external tables #304

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Open
Vitor-Avila opened this issue Jun 19, 2024 · 0 comments · Fixed by Shiphero/sqlalchemy-redshift#6 · May be fixed by #305
Open

get_table_oid results in an error for external tables #304

Vitor-Avila opened this issue Jun 19, 2024 · 0 comments · Fixed by Shiphero/sqlalchemy-redshift#6 · May be fixed by #305

Comments

@Vitor-Avila
Copy link

Summary

When trying to get the oid for an external table, the query is producing an error rather than returning None. This breaks the metadata reflection for Apache Superset, preventing the creation of physical datasets.

How to reproduce the bug

  1. Create a datashare between two Redshift tenants.
  2. Create an external schema in the consumer tenant.
  3. Use the get_table_oid method for one of the external tables. Below script can be used for testing:
from sqlalchemy import create_engine
from sqlalchemy_redshift.dialect import RedshiftDialect

REDSHIFT_URI = 'redshift+psycopg2://user:password@host:port/database'
engine = create_engine(REDSHIFT_URI)

with engine.connect() as connection:
    
   dialect = RedshiftDialect()

    try:
        table_oid = dialect.get_table_oid(connection, "source_data", schema="test_schema")
    except Exception as e:
        print("An error occurred while retrieving the table OID:")
        print(e)

Expected Results

According to the method definition (listed below), it should return None for external tables:

@reflection.cache
def get_table_oid(self, connection, table_name, schema=None, **kw):
    """Fetch the oid for schema.table_name.
    Return null if not found (external table does not have table oid)"""
    schema_field = '"{schema}".'.format(schema=schema) if schema else ""

    result = connection.execute(
        sa.text(
            """
            select '{schema_field}"{table_name}"'::regclass::oid;
            """.format(
                schema_field=schema_field,
                table_name=table_name
            )
        )
    )

    return result.scalar()

Actual Results

An error is raised. When running sqlalchemy-redshift with psycopg2:

An error occurred while retrieving the table OID:
(psycopg2.errors.FeatureNotSupported) Operation not supported on external tables

[SQL: 
                select '"test_schema"."source_data"'::regclass::oid;
                ]
(Background on this error at: https://sqlalche.me/e/14/tw8g)

With redshift-connector:

An error occurred while retrieving the table OID:
(redshift_connector.error.ProgrammingError) {'S': 'ERROR', 'C': '0A000', 'M': 'Operation not supported on external tables', 'F': '../src/pg/src/backend/catalog/namespace.c', 'L': '238', 'R': 'LocalRangeVarGetRelid'}
[SQL: 
                select '"test_schema"."source_data"'::regclass::oid;
                ]
(Background on this error at: http://sqlalche.me/e/14/f405)
@Vitor-Avila Vitor-Avila changed the title Avoid raising an error when trying to get the oid for an external table get_table_oid results in an error for external tables Jun 19, 2024
@Vitor-Avila Vitor-Avila linked a pull request Jun 19, 2024 that will close this issue
Open
4 tasks
This was referenced Apr 15, 2025
Closed
Merged
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
1 participant
@Vitor-Avila
0