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 - Select Field - 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
    
  • scottpedersoliscottpedersoli Vertica Customer

    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 - Select Field - 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.

  • scottpedersoliscottpedersoli Vertica Customer

    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.

  • scottpedersoliscottpedersoli Vertica Customer

    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