Re: Queries getting canceled inside a proc that seems to slow down randomly

On 11/11/15 11:57 AM, Skarsol wrote:
> Are we doing anything weird with this procedure? Is there anything more
> I can do to get more info as to why/how the cancellation is happening or
> why the function would slow down seemingly randomly?
>
> ERROR: canceling statement due to user request
> CONTEXT: PL/pgSQL function chooselast(character varying,character
> varying) line 1 at IF
> SQL statement "INSERT INTO partition_2015 VALUES (NEW.*)"
> PL/pgSQL function table1_insert_trigger() line 4 at SQL statement
> STATEMENT: INSERT into table1 (create_time,cusid,last1) Values
> ('NOW','8175','ROBERT'')

The error tells you what's causing this; it's a client-side interrupt.
Actually, looking at the code, you might get the same request if someone
sent a signal to the relevant backend, either at the OS level or via
pg_cancel_backend(). You can test that and see what error you get. A
statement_timeout expiration would give you a different error.

As for the hang, maybe someone is ALTERing or replacing the function?

BTW, you could write that function in the SQL language, which might
allow the optimizer to inline it. Even if it couldn't, you'd probably
still see a performance gain from not firing up plpgsql on every row.
Though, if you didn't allow empty strings in last1, you could also just
replace that whole function with coalesce(). I see the function is
marked IMMUTABLE, which is good.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com