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

Copy statement fails in 10.1, works in 9.0

The following statement

copy "WWI_Application1"."People" ("PersonID","FullName","PreferredName","SearchName","IsPermittedToLogon","LogonName","IsExternalLogonProvider", "YADAMU_COL_008" FILLER long varchar(32000000), "HashedPassword" as YADAMU.LONG_HEX_TO_BINARY("YADAMU_COL_008"),"IsSystemUser", "IsEmployee" ,"IsSalesperson","UserPreferences","PhoneNumber","FaxNumber","EmailAddress","YADAMU_COL_016" FILLER long varchar(32000000), "Photo" as YADAMU.LONG_HEX_TO_BINARY("YADAMU_COL_016"), "CustomFields","OtherLanguages","LastEditedBy","ValidFrom","ValidTo") from '/mnt/shared/vertica/YST-FD631E4F82501D 130D03E729D14069D2' PARSER fcsvparser(type='rfc4180', header=false, trim=false) NULL '' REJECTED DATA AS TABLE "YRT-CD152B959F7D2AF241A07B692F456E97" NO COMMIT
Fails with

Insufficient resources to execute plan on pool general [Request Too Large:Memory(KB) Exceeded: Requested = 59324016, Free = 54918343 (Limit = 54918343, Used = 0)]

In CE Vertica Analytic Database v10.1.0-0, regardless of how may rows are passed (eg it will fail with only one row)..

In CE Vertica Analytic Database v9.0.0-0 it appears to execute without problems.

I have not changed any parameters in either version. My 10.x is based on the docker image https://github.com/jbfavre/docker-vertica, and the 9.0 is from the docker image "dataplatform/docker-vertica"

I strongly suspect that the root cause of the problem is the rather ugly solution I have to handling LONG VARBINARY columns embedded inside as CSV file via COPY which is the YADAMU.LONG_HEX_TO_BINARY() function.

CREATE OR REPLACE FUNCTION YADAMU.LONG_HEX_TO_BINARY_L1(HEX_VALUE LONG VARCHAR(650000)) RETURN LONG VARBINARY(325000) AS BEGIN RETURN ( HEX_TO_BINARY(SUBSTR(HEX_VALUE,(65000*0) + 1,65000)::VARCHAR(65000))::LONG VARBINARY(32500) || HEX_TO_BINARY(SUBSTR(HEX_VALUE,(65000*1) + 1,65000)::VARCHAR(65000))::LONG VARBINARY(32500) || HEX_TO_BINARY(SUBSTR(HEX_VALUE,(65000*2) + 1,65000)::VARCHAR(65000))::LONG VARBINARY(32500) || HEX_TO_BINARY(SUBSTR(HEX_VALUE,(65000*3) + 1,65000)::VARCHAR(65000))::LONG VARBINARY(32500) || HEX_TO_BINARY(SUBSTR(HEX_VALUE,(65000*4) + 1,65000)::VARCHAR(65000))::LONG VARBINARY(32500) || HEX_TO_BINARY(SUBSTR(HEX_VALUE,(65000*5) + 1,65000)::VARCHAR(65000))::LONG VARBINARY(32500) || HEX_TO_BINARY(SUBSTR(HEX_VALUE,(65000*6) + 1,65000)::VARCHAR(65000))::LONG VARBINARY(32500) || HEX_TO_BINARY(SUBSTR(HEX_VALUE,(65000*7) + 1,65000)::VARCHAR(65000))::LONG VARBINARY(32500) || HEX_TO_BINARY(SUBSTR(HEX_VALUE,(65000*8) + 1,65000)::VARCHAR(65000))::LONG VARBINARY(32500) || HEX_TO_BINARY(SUBSTR(HEX_VALUE,(65000*9) + 1,65000)::VARCHAR(65000))::LONG VARBINARY(32500) ); END;

CREATE OR REPLACE FUNCTION YADAMU.LONG_HEX_TO_BINARY_L2(HEX_VALUE LONG VARCHAR(6500000)) RETURN LONG VARBINARY(3250000) AS BEGIN RETURN ( YADAMU.LONG_HEX_TO_BINARY_L1(SUBSTR(HEX_VALUE,(650000*0) + 1,650000)::LONG VARCHAR(650000))::LONG VARBINARY(325000) || YADAMU.LONG_HEX_TO_BINARY_L1(SUBSTR(HEX_VALUE,(650000*1) + 1,650000)::LONG VARCHAR(650000))::LONG VARBINARY(325000) || YADAMU.LONG_HEX_TO_BINARY_L1(SUBSTR(HEX_VALUE,(650000*2) + 1,650000)::LONG VARCHAR(650000))::LONG VARBINARY(325000) || YADAMU.LONG_HEX_TO_BINARY_L1(SUBSTR(HEX_VALUE,(650000*3) + 1,650000)::LONG VARCHAR(650000))::LONG VARBINARY(325000) || YADAMU.LONG_HEX_TO_BINARY_L1(SUBSTR(HEX_VALUE,(650000*4) + 1,650000)::LONG VARCHAR(650000))::LONG VARBINARY(325000) || YADAMU.LONG_HEX_TO_BINARY_L1(SUBSTR(HEX_VALUE,(650000*5) + 1,650000)::LONG VARCHAR(650000))::LONG VARBINARY(325000) || YADAMU.LONG_HEX_TO_BINARY_L1(SUBSTR(HEX_VALUE,(650000*6) + 1,650000)::LONG VARCHAR(650000))::LONG VARBINARY(325000) || YADAMU.LONG_HEX_TO_BINARY_L1(SUBSTR(HEX_VALUE,(650000*7) + 1,650000)::LONG VARCHAR(650000))::LONG VARBINARY(325000) || YADAMU.LONG_HEX_TO_BINARY_L1(SUBSTR(HEX_VALUE,(650000*8) + 1,650000)::LONG VARCHAR(650000))::LONG VARBINARY(325000) || YADAMU.LONG_HEX_TO_BINARY_L1(SUBSTR(HEX_VALUE,(650000*9) + 1,650000)::LONG VARCHAR(650000))::LONG VARBINARY(325000) ); END;

CREATE OR REPLACE FUNCTION YADAMU.LONG_HEX_TO_BINARY(HEX_VALUE LONG VARCHAR(32000000)) RETURN LONG VARBINARY(32000000) AS BEGIN RETURN ( YADAMU.LONG_HEX_TO_BINARY_L2(SUBSTR(HEX_VALUE,(6500000*0) + 1,6500000)::LONG VARCHAR(6500000))::LONG VARBINARY(3250000) || YADAMU.LONG_HEX_TO_BINARY_L2(SUBSTR(HEX_VALUE,(6500000*1) + 1,6500000)::LONG VARCHAR(6500000))::LONG VARBINARY(3250000) || YADAMU.LONG_HEX_TO_BINARY_L2(SUBSTR(HEX_VALUE,(6500000*2) + 1,6500000)::LONG VARCHAR(6500000))::LONG VARBINARY(3250000) || YADAMU.LONG_HEX_TO_BINARY_L2(SUBSTR(HEX_VALUE,(6500000*3) + 1,6500000)::LONG VARCHAR(6500000))::LONG VARBINARY(3250000) || YADAMU.LONG_HEX_TO_BINARY_L2(SUBSTR(HEX_VALUE,(6500000*4) + 1,6500000)::LONG VARCHAR(6500000))::LONG VARBINARY(3250000) ); END;

So if there is a better way of loading a LONG VARBINARY via COPY I would love to know it. I've tried casting the intermediate value to LONG BINARY but that does not work, as the it will not treat the string as HEX BINARY..

-Mark

Answers

  • Looking at some other questions around copy and memory issues the following query seems to help provide some degress of insight into what is going on ?

    10.1

    docker=> SELECT memory_size_kb, memory_size_actual_kb, memory_inuse_kb, queueing_threshold_kb, max_memory_size_kb, planned_concurrency, max_concurrency, query_budget_kb FROM resource_pool_status WHERE pool_name = 'general'; memory_size_kb | memory_size_actual_kb | memory_inuse_kb | queueing_threshold_kb | max_memory_size_kb | planned_concurrency | max_concurrency | query_budget_kb ----------------+-----------------------+-----------------+-----------------------+--------------------+---------------------+-----------------+----------------- 54918343 | 54918343 | 0 | 52172428 | 54918343 | 8 | | 6521553 (1 row)

    9/0

    docker=> SELECT memory_size_kb, memory_size_actual_kb, memory_inuse_kb, queueing_threshold_kb, max_memory_size_kb, planned_concurrency, max_concurrency, query_budget_kb FROM resource_pool_status WHERE pool_name = 'general'; memory_size_kb | memory_size_actual_kb | memory_inuse_kb | queueing_threshold_kb | max_memory_size_kb | planned_concurrency | max_concurrency | query_budget_kb ----------------+-----------------------+-----------------+-----------------------+--------------------+---------------------+-----------------+----------------- 59893778 | 59893778 | 0 | 56899088 | 59893778 | 8 | | 7112386 (1 row)

  • HibikiHibiki Employee

    Hi @mark_d_drake,

    So the max memory size in 9.0 is around 5GB larger than 10.0. How about the physical memory size on these servers?

    Best regards,

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.