I use the api.invoiceline view to insert imported invoice lines. That view has a “rule” that provides _INSERT functionality. The rule just calls the function defined as "insertinvoicelineitem(new.*). That function has some problem code.
Near the top of the function it checks to see if the invoice header exists. It executes this code:
SELECT invchead_id INTO _check
FROM invchead
WHERE (invchead_id=getInvcheadId(pNew.invoice_number));
I think the query optimizer is having problems here (Posgres 9.6) The query as written never completes on my 290,000 record invchead table.
However this is a simple rewrite of that section:
SELECT gettinvcheadId(pNew.invoice_number) INTO _my_id;
SELECT invchead_id INTO _check
FROM invchead
WHERE (invchead_id= _my_id);
The new query completes in about 100ms.
I’ll make the change on my system but I request that this function be analyzed and modified to include this fix.
Jim