From: | Denisa Cirstescu <Denisa(dot)Cirstescu(at)tangoe(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Catching errors inside a LOOP is causing performance issues |
Date: | 2017-09-28 08:08:44 |
Message-ID: | CY1PR12MB002563C7A3CD207EB5E911A2E6790@CY1PR12MB0025.namprd12.prod.outlook.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(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<mailto: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
From | Date | Subject | |
---|---|---|---|
Next Message | Pavel Stehule | 2017-09-28 13:07:59 | Re: Catching errors inside a LOOP is causing performance issues |
Previous Message | francis cherat | 2017-09-28 07:43:57 | many many open files on pgsql_tmp with size 0 |