Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Relation "schema.tablename" does not exist

We are using ssis packages to import data in Vertica using copy command that are creating hundreds of records in error_messages table with message: Relation "schema.tablename" does not exist.

The transaction generating the messages is a select from the v_catalog.odbc_columns table.

query_request shows the statement completed successful however error_count is populated.

The jobs are completing successfully but this seems like a lot of noise and overhead on the system. Has anybody seen this before?

Comments

  • Jim_KnicelyJim_Knicely Administrator

    Hi,

    Is the actual error message: Relation "schema.tablename" does not exist?

    The only way I can think of getting that error is if you have a schema named "schema" but no table named "tablename" in that schema:

    dbadmin=> create schema "schema";
    CREATE SCHEMA
    
    dbadmin=> select * from "schema".tablename;
    ERROR 4568:  Relation "schema.tablename" does not exist
    
  • Hi Jim, thanks for responding.

    No, each error message is different and reflects an actual schema/table name from our database. Here is a few actual examples from message in error_messages:
    Relation "temp.mcf_results_new" does not exist
    Relation "bi.AG_CustomerParentStatic" does not exist
    Relation "kamino.vDP_map_all_dsp_temp" does not exist
    Relation "kamino.FT_VPR_Hist_SRC" does not exist

    The query that is generating the errors (based on transaction_id) is a select on v_catalog.odbc_columns but the odd thing is the query includes WHERE clause for the specific schema and table that the ssis package is working on. If i take the query and run it it completes successfully. So the error messages are odd. I'm going to open a case with support.

    SELECT
    *
    FROM
    (
    SELECT
    'abc' AS catalog_name,
    schema_name,
    table_name,
    column_name,
    CASE
    WHEN (data_type = 1)
    THEN -8
    WHEN (data_type = 12)
    THEN -9
    ELSE data_type
    END AS data_type,
    CASE
    WHEN (UPPER(data_type_name) = 'CHAR')
    THEN 'CHAR'
    WHEN (UPPER(data_type_name) = 'VARCHAR')
    THEN 'VARCHAR'
    ELSE data_type_name
    END AS data_type_name,
    column_size,
    buffer_length,
    decimal_digits,
    num_prec_radix,
    nullable,
    remarks,
    column_default AS column_def,
    CASE
    WHEN (sql_type_id = 1)
    THEN -8
    WHEN (sql_type_id = 12)
    THEN -9
    ELSE sql_type_id
    END AS sql_data_type,
    sql_datetime_sub,
    char_octet_length,
    ordinal_position,
    is_nullable
    FROM
    v_catalog.odbc_columns
    ORDER BY
    catalog_name,
    schema_name,
    table_name,
    ordinal_position ) AS vmd
    WHERE
    catalog_name ilike E'abc' ESCAPE E'\'
    AND schema_name ilike E'edw' ESCAPE E'\'
    AND table_name ilike E'Geo_ZipCode_SWAP' ESCAPE E'\'

  • Jim_KnicelyJim_Knicely Administrator
    edited October 2017

    Hi,

    There can be many queries that run in a transaction, including those that error. You can not see the queries in the database system tables (i.e. query_requests or dc_requests_issued) that generate errors (i.e. The "relation does not exist" error). You have to go to the Vertica log and check it. For example, search for one one of your errors: Relation "temp.mcf_results_new" does not exist and look at the line right before. It should be the query that generated the error.

  • Yep, Vertica.log confirms the query (based on session_id) is select on v_catalog.odbc_columns. I have opened case with support, lets see what they say.

  • scottpedersoli, can you let us know what is the support case # you opened with support? we would like to know details of your environment, specific products, driver and OS versions.

  • Case# 71925. We are on Vertica version 8.1.1-6. We are using driver version 7.01.01. I'n in the process now of upgrading lower environments to 8.01.01.06 before I do production boxes.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.