Subject: Re: Queries getting canceled inside a proc that seems to
 slow down randomly
To: Skarsol <skarsol@gmail.com>, "pgsql-performance@postgresql.org"
 <pgsql-performance@postgresql.org>
References: 
 <CAMt8e=FdOU682kP86A0eSw-Fnf-XWd6YMiJZeS2ifSRR22UOVw@mail.gmail.com>
From: Jim Nasby <Jim.Nasby@BlueTreble.com>
Message-ID: <56465B32.2070004@BlueTreble.com>
Date: Fri, 13 Nov 2015 15:50:42 -0600
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.9; rv:38.0)
 Gecko/20100101 Thunderbird/38.3.0
MIME-Version: 1.0
In-Reply-To: 
 <CAMt8e=FdOU682kP86A0eSw-Fnf-XWd6YMiJZeS2ifSRR22UOVw@mail.gmail.com>
Content-Type: text/plain; charset="utf-8"; format=flowed
Content-Transfer-Encoding: 7bit
Received-SPF: None (protection.outlook.com: bluetreble.com does not designate
 permitted sender hosts)
SpamDiagnosticOutput: 1:23
SpamDiagnosticMetadata: NSPM
X-MS-Exchange-CrossTenant-OriginalArrivalTime: 13 Nov 2015 21:50:47.0837 (UTC)
X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted
X-MS-Exchange-Transport-CrossTenantHeadersStamped: CY1PR11MB0682
X-Pg-Spam-Score: -1.9 (-)
List-Archive: <http://archives.postgresql.org/pgsql-performance>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-performance.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@postgresql.org>
List-Post: <mailto:pgsql-performance@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-performance>
List-Unsubscribe: 
 <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
X-Mailing-List: pgsql-performance
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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