Re: [GENERAL] Catching errors inside a LOOP is causing performance issues - Mailing list pgsql-general

Hi Tom,

 

You said that trapping an arbitrary exception is a “fairly expensive mechanism”.

What if the:

 

begin

    ….

exception when others

    then null;

end;

 

would be replaced with

 

begin

    ….

exception when NO_DATA_FOUND

    then null;

 

end;

 

When the code is catching a certain exception: NO_DATA_FOUND does this make any difference?

Or it’s all about the process of setting up and ending a subtransaction?

 

Thanks,

Denisa Cîrstescu

 

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wednesday, September 27, 2017 9:00 PM
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: Denisa Cirstescu <Denisa.Cirstescu@tangoe.com>; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Catching errors inside a LOOP is causing performance issues

 

"David G. Johnston" <david.g.johnston@gmail.com> 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 need."

> https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.p

> ostgresql.org%2Fdocs%2Fcurrent%2Fstatic%2Fplpgsql-control-structures.h

> tml%23PLPGSQL-ERROR-TRAPPING&data=01%7C01%7CDenisa.Cirstescu%40tangoe.

> com%7C6243898de8ae4141290a08d505d194e6%7C3ba137049b66408a9fb9db51aba57

> 9e4%7C0&sdata=iTBlh1PpcvJQiBZNPjDxsu7ExT%2BP%2BAirqr9Upz9sbJQ%3D&reser

> ved=0

 

> I'm somewhat doubting "plan caching" has anything to do with this; I

> suspect its basically that there is high memory and runtime overhead

> to deal with the possibilities of needing to convert a exception into

> a branch instead of allowing it to be fatal.

 

Yeah, it's about the overhead of setting up and ending a subtransaction.

That's a fairly expensive mechanism, but we don't have anything cheaper that is able to recover from arbitrary errors.

 

                                             regards, tom lane