Relation "schema.tablename" does not exist

scottpedersoli

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?

Jim_Knicely

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

scottpedersoli

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_Knicely

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.

scottpedersoli

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.

Connyrt-emp1

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.

scottpedersoli

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.