Re: proposal: additional error fields

On Tue, May 1, 2012 at 12:02 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>> I have some concerns about the performance cost of this.  Now, you may
>> think that this is a dumb thing to be concerned about, but some
>> testing I've done seems to indicate that MOST of the cost of rolling
>> back a subtransaction is the cost of generating the error string, and
>> this is why PL/pgsql exception blocks are slow, and I actually do
>> think that the slowness of PL/pgsql exception blocks is a real issue
>> for users.  It certainly has been for me, in the past.  So adding 9
>> more fields that will have to be populated on every error whether
>> someone cares about them or not is a little scary to me.  If, on the
>> other hand, we can arrange to generate these fields only when they'll
>> be used, that would be a lot more appealing, and obviously we might be
>> able to apply the same technique to the error message itself, which
>> would be neat, too.
>
> yes, it can has impact and I have to do some performance tests. But
> usually almost fields are NULL - and in typical use case are 2, 4, or
> 5 fields non empty. More - just copy string is used - so it is
> relative fast. Other possibility is preallocation, because all fields
> are limited by MAXNAMELEN. Same trick we can use for SQLSTATE variable
>
> create table ff(a int not null);
>
> CREATE OR REPLACE FUNCTION public.fx()
>  RETURNS void
>  LANGUAGE plpgsql
> AS $function$
> begin
>  for i in 1..100000
>  loop
>    begin
>      insert into ff values(null);
>    exception when others then
>      /* do nothing */
>    end;
>  end loop;
> end;
> $function$
>
> this is most worst case - 5 fields more
>
> patched 1500 ms
> master   1380 ms
>
> so this is about 8% slowdown for unoptimized code where any statement
> was raised. Any other statement in loop decrease slowdown to half and
> usually not all statements will raise exception. I think so there are
> some possibility haw to optimize it - minimize palloc calls

Yeah. I also noticed in my benchmarking that sprintf() seemed to be
very slow, to the point where I wondered whether we ought to have our
own minimal version of sprintf() just for error strings.

Another idea would be to pass a flag indicating whether the context
that's going to receive the error cares about the additional flags.
If not, we can skip generating the information. Not sure how much
that helps, but maybe...

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company