Subject: Re: Query on pg_stat_activity table got stuck
To: Tom Lane <tgl@sss.pgh.pa.us>, neeraj kumar <neeru.cse@gmail.com>
CC: <pgsql-admin@postgresql.org>, <pgsql-general@postgresql.org>
References: 
 <CAPR3Wj5Z17=+eeyrn_ZDG3NQGYgMEOY6JV6Y-WRRhGgwc16U3Q@mail.gmail.com>
 <15877.1557178386@sss.pgh.pa.us>
 <CAPR3Wj5CUVNqF2r6fNnrueDgn2k+qyADCvYZ2878SCytTrPPng@mail.gmail.com>
 <21502.1557339176@sss.pgh.pa.us>
 <CAPR3Wj4fT1hOumweROsAifTAMLT7oM+YejxBAcpPsXuMAecBZw@mail.gmail.com>
 <29660.1557352732@sss.pgh.pa.us>
From: Jeremy Schneider <schnjere@amazon.com>
Message-ID: <e99f759e-4d18-d297-9e1a-639c7379faf2@amazon.com>
Date: Thu, 9 May 2019 12:46:32 -0700
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.12; rv:60.0)
 Gecko/20100101 Thunderbird/60.6.1
MIME-Version: 1.0
In-Reply-To: <29660.1557352732@sss.pgh.pa.us>
Content-Type: text/plain; charset="utf-8"
Content-Language: en-US
Content-Transfer-Encoding: 7bit
Precedence: Bulk
Precedence: bulk

On 5/8/19 14:58, Tom Lane wrote:
> neeraj kumar <neeru.cse@gmail.com> writes:
>> Yes we use SSL to connect to DB.
> 
> Hm.  I'm suspicious that one of the functions that fetch data for
> an SSL connection threw an error.  In particular, it doesn't look
> to be hard at all to make X509_NAME_to_cstring fall over --- an
> encoding conversion failure would do it, even without any stretchy
> assumptions about OOM this early in backend start.  Have you got
> any SSL certificates floating around with non-ASCII subject name
> or issuer name?

Crazy timing. We just had a report come in from a database in the RDS
fleet that's hitting this same issue.  It was one of the Aurora systems,
but there wasn't anything Aurora-specific that I could see in the
relevant bits of code.

Seems to me that at a minimum, this loop shouldn't go on forever. Even
having an arbitrary, crazy high, hard-coded number of attempts before
failure (like a million) would be better than spinning on the CPU
forever - which is what we are seeing.

Would be even cooler to detect and correct a broken slot in
PgBackendStatus... if I have a good idea I'll post/try it.  :)

-Jeremy

-- 
Jeremy Schneider
Database Engineer
Amazon Web Services