From: Jim Nasby <Jim.Nasby@BlueTreble.com>
To: Skarsol <skarsol@gmail.com>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Queries getting canceled inside a proc that seems to slow down randomly
Date: Fri, 13 Nov 2015 15:50:42 -0600
Message-ID: <56465B32.2070004@BlueTreble.com> (raw)
In-Reply-To: <CAMt8e=FdOU682kP86A0eSw-Fnf-XWd6YMiJZeS2ifSRR22UOVw@mail.gmail.com>
References: <CAMt8e=FdOU682kP86A0eSw-Fnf-XWd6YMiJZeS2ifSRR22UOVw@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: Jim.Nasby@BlueTreble.com, skarsol@gmail.com
Subject: Re: Queries getting canceled inside a proc that seems to slow down randomly
In-Reply-To: <56465B32.2070004@BlueTreble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox