Thread: Segmentation Fault PG 14

Willian Colognesi <willian_colognesi@trimble.com> writes:
> I started to use version `14.5-2.pgdg20.04+2` for a dedicated database and
> I'm facing many segmentation faults during the day when the database has
> more heavy queries.

I take it things were okay with the version you used previously?
What was that exactly?  Has anything else changed?

> I could also get a little information from gdb, I'm not sure if it will
> help:

This looks pretty messed up.  Are you sure the debug symbols you're using
match the package?

Even better, can you construct a self-contained test case?

            regards, tom lane

On 11/7/22 10:36 AM, Willian Colognesi wrote:
> Hi Tom,
> 
> `I take it things were okay with the version you used previously?`
> Yes, it was working pretty well in another instance with pg version 
> `12.4-1.pgdg18.04+1`, and we had to make a migration of one database 
> that was running in this server to another using Logical Replication.

Actually you used dump/restore and logical replication. '

In below:

1) What versions of pg_dump and pg_restore did you use?

2) To be clear the subscription was started after the restore?

3) Where there any error messages issued at any point in below?

4) Are the database clusters on the same machine?

> 
> the process was basically this:
> |CREATE| |PUBLICATION my_database_pub ||FOR| |ALL| |TABLES;|
> |postgres@origin:~$ psql "dbname=<my_database> replication=database"
> |
> |
> |my_database=# CREATE_REPLICATION_SLOT <slot_name> LOGICAL pgoutput;|
> pg_dump -j4 -h <host> -p 5432 --no-subscriptions --no-publications -d 
> <my_database> --snapshot=<snapshot_generated> -Fd -U <my_user> -f 
> </mnt/dump>
> postgres@destination:/mnt/database$ pg_restore -d <my_database> -j 5 
> </mnt/dump>
> 
> CREATE SUBSCRIPTION <name_sub>
>         CONNECTION 'host=<host> dbname=<my_database> user=replica 
> password=?? port=5432'
>         PUBLICATION <name_pub>
>         WITH (slot_name=<slot_name>, create_slot=false, copy_data=false);
> |
> 
> 
> After this migration we started to have this kind of problem in both 
> replica and primary servers.
> 
> `This looks pretty messed up.  Are you sure the debug symbols you're using`
> What exactly do you mean? I'm not too familiar with this debug toolings, 
> the packages I've used were:
> 
> postgresql-14/focal-pgdg,now 14.5-2.pgdg20.04+2 arm64 [installed]
> postgresql-14-dbgsym/focal-pgdg,now 14.5-2.pgdg20.04+2 arm64 [installed]
> 
> `Even better, can you construct a self-contained test case?`:
> Actually I couldn't reproduce the problem because it's happening just in 
> a production database, and it doesn't look to have a pattern in the 
> cases when it happens.
> 
> Is there anything I could provide you to help the analysis ?
> 
> 
> 
> On Mon, Nov 7, 2022 at 3:08 PM Tom Lane <tgl@sss.pgh.pa.us 
> <mailto:tgl@sss.pgh.pa.us>> wrote:
> 
>     Willian Colognesi <willian_colognesi@trimble.com
>     <mailto:willian_colognesi@trimble.com>> writes:
>      > I started to use version `14.5-2.pgdg20.04+2` for a dedicated
>     database and
>      > I'm facing many segmentation faults during the day when the
>     database has
>      > more heavy queries.
> 
>     I take it things were okay with the version you used previously?
>     What was that exactly?  Has anything else changed?
> 
>      > I could also get a little information from gdb, I'm not sure if
>     it will
>      > help:
> 
>     This looks pretty messed up.  Are you sure the debug symbols you're
>     using
>     match the package?
> 
>     Even better, can you construct a self-contained test case?
> 
>                              regards, tom lane
> 
> 
> 
> -- 
> <http://www.trimble.com/>
> *Willian Cezar de O. Colognesi
> *
> Systems Analysis Specialist, Trimble Transportation Brazil
> Avenida Santos Dumont, 271 | Londrina, PR | 86039-090
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 11/7/22 10:57 AM, Willian Colognesi wrote:
> 1) What versions of pg_dump and pg_restore did you use?
> A: pg_dump and pg_restore was done using pg 14 (the same as the 
> destination was running)
> 
> 2) To be clear the subscription was started after the restore?
> A: Yes
> 
> 3) Where there any error messages issued at any point in below?
> A: no errors during the dump and restore.
> 
> 4) Are the database clusters on the same machine?
> A: No, the origin and destination were different servers at the same VPC.

Are servers using the same version of OS?


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 11/7/22 11:03 AM, Willian Colognesi wrote:
> No, the origin where the database was was running ubuntu 18.04.5 x86_64 
> and the destination ubuntu 20.04.5 aarch64

Where I was going was this:

https://wiki.postgresql.org/wiki/Locale_data_changes

Then I realized you had not done any binary upgrades, so that is a dead end.


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Willian Colognesi <willian_colognesi@trimble.com> writes:
> `I take it things were okay with the version you used previously?`

> Yes, it was working pretty well in another instance with pg version
> `12.4-1.pgdg18.04+1`, and we had to make a migration of one database that
> was running in this server to another using Logical Replication.

12.4 to 14.5 is kind of a big jump :-(.

The stack trace seems to indicate that ExecProcNode transferred control
to never-never land, which says that something clobbered the function
pointer it's trying to indirect through.  I don't recall having seen
any similar reports though.

Are you using any extensions besides those that come with core Postgres?
A build incompatibility with some third-party extension might explain
this, perhaps.

One thing I'm curious about is that the stack trace seems to imply that
there was an Append plan node immediately below another Append.  That
shouldn't happen AFAIK --- the planner tries to collapse out such
cases.  Can you get us an EXPLAIN for the problem query?

            regards, tom lane

On 11/7/22 12:15, Willian Colognesi wrote:
> All the extensions installed in this database are these:
> ```
>                                       List of installed extensions
>          Name        | Version |   Schema   |                       
>   Description
> --------------------+---------+------------+-----------------------------------------------------------
>   amcheck            | 1.3     | public     | functions for verifying 
> relation integrity
>   btree_gist         | 1.6     | public     | support for indexing 
> common datatypes in GiST
>   pg_stat_statements | 1.9     | public     | track execution statistics 
> of all SQL statements executed
>   pgcrypto           | 1.3     | public     | cryptographic functions
>   plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
> (5 rows)
> ```
> 
> I tried to execute a query with parameters the query was supposed to be 
> run (because I'm not sure exactly the values in the where clause that 
> made the segmentation fault).
> 
> here is the explain: https://explain.depesz.com/s/Tql3 
> <https://explain.depesz.com/s/Tql3> (Ps: I just had to suppress the real 
> table/index names)
> 
> Looks like since I've disable *jit* as Boris told, until now the 
> database did not restarted again... (not sure if it's coincidence)
> 

I did not see that post or suggestion.

What was the suggestion?

Are you saying the database does not start up now?

-- 
Adrian Klaver
adrian.klaver@aklaver.com

Willian Colognesi <willian_colognesi@trimble.com> writes:
> No, the database is running well, no problem until now after disabled *jit.*

Interesting.  Which version of LLVM is installed?

            regards, tom lane

Willian Colognesi <willian_colognesi@trimble.com> writes:
> There is no llvm installed on ubuntu server, postgresql was installed via
> apt package `apt install postgresql-14`

If there's no LLVM around, then disabling JIT wouldn't do anything,
because it depends on LLVM to compile code.

We should perhaps wait awhile to see if that really fixed it.

            regards, tom lane

On Mon, Nov 7, 2022 at 2:38 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Willian Colognesi <willian_colognesi@trimble.com> writes:
> > `I take it things were okay with the version you used previously?`
>
> > Yes, it was working pretty well in another instance with pg version
> > `12.4-1.pgdg18.04+1`, and we had to make a migration of one database that
> > was running in this server to another using Logical Replication.
>
> 12.4 to 14.5 is kind of a big jump :-(.
>
> The stack trace seems to indicate that ExecProcNode transferred control
> to never-never land, which says that something clobbered the function
> pointer it's trying to indirect through.  I don't recall having seen
> any similar reports though.

I'm just thinking out loud... I've seen the latest GCC do that on what
it believes to be dead code. Our problem was detailed at
https://github.com/weidai11/cryptopp/issues/1141 .

We identified the problem by building/running our self tests with
-fsanitize=unreachable .

Testing with -fsanitize=unreachable should confirm or rule out GCC and
Clang [incorrectly] removing code that is actually needed. If this is
the problem, then -fsanitize=unreachable will also provide a usable
stack trace and provide a useful debugging experience.

Jeff

On Tue, Nov 8, 2022 at 11:45 AM Willian Colognesi
<willian_colognesi@trimble.com> wrote:
> root@ip-10-x-x-x:/home/ubuntu# pg_config --configure
> ... --with-extra-version= (Ubuntu 14.5-2.pgdg20.04+2)' ...
> ... '--with-llvm' 'LLVM_CONFIG=/usr/bin/llvm-config-10' ...

> There is no llvm installed on ubuntu server, postgresql was installed via apt package `apt install postgresql-14`

We can see from the pg_config output that it's built with LLVM 10.
Also that looks like it's the usual pgdg packages which are certainly
built against LLVM and will install it automatically.

Willian Colognesi <willian_colognesi@trimble.com> writes:
> Looks like we can confirm that the jit disable fixed the problem, because
> since yesterday when I disabled jit, the database did not restarted again,
> and before it the database was restarting at least once per hour.

Hmm.  I now recall that we had a previous report of problems with
JIT on aarch64/Focal:

https://www.postgresql.org/message-id/flat/20220303150428.GA26036%40depesz.com

That was LLVM 9 not LLVM 10, but since we never identified the exact
issue, there's no real strong reason to suppose it's been fixed.

Probably keeping JIT off is the best answer for you --- it's hard to
say when we'll be able to make progress with this, given the lack of
reproducible test cases.

            regards, tom lane