Re: proposal: additional error fields

2012/5/1 Robert Haas <robertmhaas@gmail.com>:
> On Tue, May 1, 2012 at 8:21 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
>> I have to goals for 9.3. First goal is plpgsql_check_function, second
>> goal is enhancing ErrorData and error management to support new
>> fields: COLUMN_NAME, CONSTRAINT_NAME, CONSTRAINT_SCHEMA, SCHEMA_NAME,
>> TABLE_NAME, ROUTINE_NAME, ROUTINE_SCHEMA, TRIGGER_NAME and
>> TRIGGER_SCHEMA
>>
>> previous discussion  is in thread
>> http://postgresql.1045698.n5.nabble.com/patch-for-9-2-enhanced-errors-td4470837.html
>
> 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 voidLANGUAGE 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

Regards

Pavel




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