Copy statement fails in 10.1, works in 9.0

mark_d_drake

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

mark_d_drake

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)

Hibiki

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,