Re: [GENERAL] Catching errors inside a LOOP is causing performance issues

2017-09-28 Thread David G. Johnston
On Thu, Sep 28, 2017 at 1:08 AM, Denisa Cirstescu < denisa.cirste...@tangoe.com> wrote: > Hi Tom, > > You said that trapping an *arbitrary* exception is a “fairly expensive > mechanism”. > ​I suppose a better (though maybe not perfectly accurate) wording is that setting up the pl/pgsql execution

Re: [GENERAL] Catching errors inside a LOOP is causing performance issues

2017-09-28 Thread Pavel Stehule
an check ROW_COUNT without exception handling. Regards Pavel > > > Thanks, > > Denisa Cîrstescu > > > > -Original Message- > From: Tom Lane [mailto:t...@sss.pgh.pa.us] > Sent: Wednesday, September 27, 2017 9:00 PM > To: David G. Johnston <david.g.j

Re: [GENERAL] Catching errors inside a LOOP is causing performance issues

2017-09-28 Thread Denisa Cirstescu
<david.g.johns...@gmail.com> Cc: Denisa Cirstescu <denisa.cirste...@tangoe.com>; pgsql-general@postgresql.org Subject: Re: [GENERAL] Catching errors inside a LOOP is causing performance issues "David G. Johnston" <david.g.johns...@gmail.com<mailto:david.g.johns...@gmai

Re: [GENERAL] Catching errors inside a LOOP is causing performance issues

2017-09-27 Thread Tom Lane
"David G. Johnston" writes: > ​Not sure how much detail you are looking for but the docs say this: > "​Tip: A block containing an EXCEPTION clause is significantly more > expensive to enter and exit than a block without one. Therefore, don't use > EXCEPTION without

Re: [GENERAL] Catching errors inside a LOOP is causing performance issues

2017-09-27 Thread David G. Johnston
On Mon, Sep 25, 2017 at 9:13 AM, Denisa Cirstescu < denisa.cirste...@tangoe.com> wrote: > > Can someone please explain to me why this worked? > > What happened behind the scenes? > > I suspect that when you catch exceptions inside of a LOOP and the code > ends up generating an exception, Postgres

Re: [GENERAL] Catching errors inside a LOOP is causing performance issues

2017-09-27 Thread Denisa Cirstescu
I am adding an example that is runnable in order to demonstrate my point. CREATE OR REPLACE FUNCTION initialVersion() RETURNS VOID AS $$ declare testDate DATE; begin for i in 1..99 loop begin select now() into strict testDate where 1=0; exception when others then null;

[GENERAL] Catching errors inside a LOOP is causing performance issues

2017-09-27 Thread Denisa Cirstescu
Hi all, I had a function with a performance issue: totalCharge := 0; FOR myRecord IN ... LOOP .. IF severalConditionsAreMet THEN BEGIN SELECT t1.charge INTO STRICT recordCharge