more quick actions
minimize quick actions
Topic starter
Hello Team,
One of our customer is dealing with ODBC big database (pgSQL)of greater than 9 GB. Initially when database size is less then PcVue can read and write the data inside database. When database size is big (more than 5 GB as per customer) PcVue is not able to read and write into database.
The event viewer shows an error as "2022/09/09,11:28:20.228,8,E,,14020,,1,His : SGBD YIL_BUSBAR (proBMSYIL) -> TRD : Error creating insert cursor ".
When we enable the ODBC traces, we found an error like "Out of memory while reading tuples.; memory allocation error??? (4)" in ODBC traces. After searching this error on google found a solution to enable "use Declare/Fetch" option in DSN
options.
https://karthikvankadara.com/2018/08/28/alteryx-postgresql-out-of-memory-while-reading-tuples/
After enabling this option PcVue can read and write into the database but the project startup and exit time is high than normal.
Has anyone else also faced such issue?
Thanks & Regards,
Nitesh Dongare
Posted : 26/09/2022 6:18 pm
Hello Nitesh,
It seems that the option you selected is part of the Postgre driver configuration.
This post gives you all information you need about the impact of using this option :
https://stackoverflow.com/questions/34009774/what-is-usedeclarefetch-postgresql-odbc
Instead of having a large buffer, it will fetch one by one each row. So little memory is necessary but treatments will take much longer.
The question is do they need to read all the data in this database ? If not It would be better to export or remove older data than change the driver configuration for performances.
Best regards,
Kévin
Posted : 27/09/2022 12:46 pm
Hello,
Actually, when the project started, no need to have this option.
But as the DB grows, it comes to a point PcVue will not be able to insert records anymore. No tests have been done to know if it is because of DB size greater than a specific value, because of fragmentation, ...
(As well, if only at restart of the project the error shows up)
When this happens, the ODBC archiving would stop working (without the setting).
So the first workaround is the advanced setting and then the advice given by Kevin. The customer needs to give a try to purge old archives.
Regards,
Edouard
Posted : 28/09/2022 9:56 am
Topic starter
Hello,
Thank you Edourd and Kevin for your reply.
I did some testing on my system by purging pgSQL database step by step to found at what database size we are getting error. We are getting this error after 6 GB and not getting error below 235 GB. Cant found the exact value, but nearly about 5 GB database we are getting this error.
Customer is having 5000 variables logging at 59 sec interval. They will need 4-5 months data which will be around 50-60 GB as per information received from customer.
Customer and Myself tried connect with 10 GB database by enabling the "use Declare/ fetch" option in ODBC setting. PcVue will for 15-10 minutes and then crashing. Crash report is showing below logs.
This is a datacenter site and need to resolve this issue on priority please help.
Thanks & Regards,
Nitesh Dongare
Posted : 04/10/2022 12:54 pm