Date: Thu, 3 Mar 2022 17:44:01 +0100
From: hubert depesz lubaczewski <depesz@depesz.com>
Sender: depesz@depesz.com
To: PostgreSQL General <pgsql-general@lists.postgresql.org>
Subject: Re: Interesting fail when migrating Pg from Ubuntu Bionic to Focal
Message-ID: <20220303164401.GA13056@depesz.com>
Reply-To: depesz@depesz.com
References: <20220303150428.GA26036@depesz.com>
 <20220303151156.GB26036@depesz.com>
 <20220303163921.GA10776@depesz.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <20220303163921.GA10776@depesz.com>
User-Agent: Mutt/1.10.1 (2018-07-13)
Archived-At: 
 <https://www.postgresql.org/message-id/20220303164401.GA13056%40depesz.com>
Precedence: bulk

On Thu, Mar 03, 2022 at 05:39:21PM +0100, hubert depesz lubaczewski wrote:
> On Thu, Mar 03, 2022 at 04:11:56PM +0100, hubert depesz lubaczewski wrote:
> > On Thu, Mar 03, 2022 at 04:04:28PM +0100, hubert depesz lubaczewski wrote:
> > > and it worked, so I'm kinda at loss here.
> > 
> > based on some talk on IRC, I was able to get stack trace from fail:
> 
> Based on the stack trace I was able to get it to break using simple
> query:
> select p.proname, (SELECT rolname from pg_catalog.pg_roles where oid = p.proowner) from pg_proc p;
> 
> I took a simple look at ranges of oid/prowner, and they look fine:
> 
> =# select min(proowner), max(proowner), count(*) from pg_proc;
>  min │ max │ count 
> ─────┼─────┼───────
>   10 │  10 │  2970
> (1 row)
> 
> 16:38:34 db: postgres@postgres, pid:1991057
> =# select min(oid), max(oid), count(*) from pg_roles;
>  min │    max    │ count 
> ─────┼───────────┼───────
>   10 │ 310235824 │   244
> (1 row)
> 
> 
> Also, as I didn't mention it before: it's Pg 12.9.

OK. Traced it back to JIT. With JIT enabled:

=# show jit;
 jit 
─────
 on
(1 row)

=# explain select p.proname, (SELECT rolname from pg_catalog.pg_roles where oid = p.proowner) from pg_proc p;
                                          QUERY PLAN                                          
──────────────────────────────────────────────────────────────────────────────────────────────
 Seq Scan on pg_proc p  (cost=0.00..156507.84 rows=63264 width=128)
   SubPlan 1
     ->  Index Scan using pg_authid_oid_index on pg_authid  (cost=0.14..2.36 rows=1 width=64)
           Index Cond: (oid = p.proowner)
 JIT:
   Functions: 8
   Options: Inlining false, Optimization false, Expressions true, Deforming true
(7 rows)

=# select p.proname, (SELECT rolname from pg_catalog.pg_roles where oid = p.proowner) from pg_proc p;
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
16:42:44 canvas test, cluster 999, standalone db: @, pid:2014255
?!> 


But when I disable jit - query works fine.

versions of things that I think are relevant:

=$ dpkg -l | grep -E 'llvm|clang|gcc|glibc'
ii  gcc                                4:9.3.0-1ubuntu2                      arm64        GNU C compiler
ii  gcc-10-base:arm64                  10.3.0-1ubuntu1~20.04                 arm64        GCC, the GNU Compiler Collection (base package)
ii  gcc-9                              9.3.0-17ubuntu1~20.04                 arm64        GNU C compiler
ii  gcc-9-base:arm64                   9.3.0-17ubuntu1~20.04                 arm64        GCC, the GNU Compiler Collection (base package)
ii  libgcc-9-dev:arm64                 9.3.0-17ubuntu1~20.04                 arm64        GCC support library (development files)
ii  libgcc-s1:arm64                    10.3.0-1ubuntu1~20.04                 arm64        GCC support library
ii  libllvm9:arm64                     1:9.0.1-12                            arm64        Modular compiler and toolchain technologies, runtime library

Best regards,

depesz