Re: Catching errors inside a LOOP is causing performance issues

2017-09-28 10:08 GMT+02:00 Denisa Cirstescu <Denisa(dot)Cirstescu(at)tangoe(dot)com>:

> 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?
>
It is same in Postgres - exception is exception - and exception handling is
same.

Some PostgreSQL commands doesn't raise NO_DATA_FOUND exception in default
usage. Then you can check ROW_COUNT without exception handling.

Regards

Pavel

>
>
> Thanks,
>
> Denisa Cîrstescu
>
>
>
> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: Wednesday, September 27, 2017 9:00 PM
> To: David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
> Cc: Denisa Cirstescu <Denisa(dot)Cirstescu(at)tangoe(dot)com>;
> pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] Catching errors inside a LOOP is causing
> performance issues
>
>
>
> "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)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
>