Skip site navigation (1) Skip section navigation (2)

Re: proposal: additional error fields

From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: Robert Haas <robertmhaas(at)gmail(dot)com>
Cc: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: proposal: additional error fields
Date: 2012-05-01 16:02:18
Message-ID: CAFj8pRDSjBvwJ_VW-6G0pD4CTa9Dd7D5N-0qhFD8bmjSCRUjLw@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
2012/5/1 Robert Haas <robertmhaas(at)gmail(dot)com>:
> On Tue, May 1, 2012 at 8:21 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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 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

Regards

Pavel




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

In response to

Responses

pgsql-hackers by date

Next:From: Merlin MoncureDate: 2012-05-01 16:05:06
Subject: Re: JSON in 9.2 - Could we have just one to_json() function instead of two separate versions ?
Previous:From: Joey AdamsDate: 2012-05-01 15:49:28
Subject: Re: JSON in 9.2 - Could we have just one to_json() function instead of two separate versions ?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group