BUG #16976: server crash when deleting via a trigger on a foreign table

I have experienced a server crash when deleting from a table that ahs a
before trigger on it. The before trigger calls a function that deletes from
a foreign (informix) table.

Hm, can you get a stack trace from that?

https://wiki.postgresql.org/wiki/Generating_a_stack_trace_of_a_PostgreSQL_backend

It seems fairly likely that the bug is in whatever FDW you're using
rather than in core Postgres. A stack trace would help to
confirm or refute that.

regards, tom lane

Hi,

I was in the process of reproducing the problem and following the
guidelines to produce the stack trace... but now it works.

I asked my colleagues if they changed anything and will let you know.

Thanks for the quick response, and I will keep you posted if I have any
news.

Kind regards,

Herwig

Hi,

I tried it again. With a previous test the trigger did not exists, that
is why it worked.

Anyway, with the trigger in place we encounter PG server crashes.

I am having trouble collection a trace file because when I attach gdb to
the process:

0x00007f99b46f7a07 in epoll_wait (epfd=5, events=0x561321b40630,
maxevents=maxevents@entry=1, timeout=timeout@entry=-1) at
../sysdeps/unix/sysv/linux/epoll_wait.c:30
30      ../sysdeps/unix/sysv/linux/epoll_wait.c: No such file or directory.
(gdb) quit

or

Reading symbols from /lib/x86_64-linux-gnu/libnss_files.so.2...Reading
symbols from
/usr/lib/debug//lib/x86_64-linux-gnu/libnss_files-2.27.so...done.
done.
0x00007fd77dc3343e in __libc_read (fd=0, buf=0x7ffc49e20010, nbytes=1)
at ../sysdeps/unix/sysv/linux/read.c:27
27      ../sysdeps/unix/sysv/linux/read.c: No such file or directory.
(gdb) quit
A debugging session is active.

        Inferior 1 [process 35694] will be detached.

And I cannot execute anything in the attached (psql) process,

it complains about a file not found , always in sysdeps. ( i do not find
this directory on the server)

Anyway when the trigger is fired, it crashes the server.

I can delete on the foreign table directly and I can delete on the
foreign table with a function that returns void instead of trigger. all
that works fine.

The function is:

 CREATE OR REPLACE FUNCTION metadblucht.test_herwig(p_enh_id int)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
            BEGIN
                delete from informix.t_water_eenheid
                where enh_id = p_enh_id;
            END;
        $function$
;

When I run this function, it works fine.

SELECT metadblucht.test_herwig(38);

and the record is deleted from the foriegn table.

I can delete from the foreign table directly:

delete from informix.t_water_eenheid  where enh_id = 40;

that works fine as well.

Can you continue zith this information or do you still need the trace
file. I have no idea what I can do to resolve the issue with sysdeps not
on the server.

Kind regards,

Herwig

On Thu, Apr 22, 2021 at 12:09:44PM +0200, Herwig Goemans wrote:

Anyway, with the trigger in place we encounter PG server crashes.

I am having trouble collection a trace file because when I attach gdb to the
process:

0x00007f99b46f7a07 in epoll_wait (epfd=5, events=0x561321b40630,
maxevents=maxevents@entry=1, timeout=timeout@entry=-1) at
../sysdeps/unix/sysv/linux/epoll_wait.c:30
30      ../sysdeps/unix/sysv/linux/epoll_wait.c: No such file or directory.
(gdb) quit

What does a simple "bt" print?
--
Michael

Hi,

I am not sure what you mean, I am not a developer.

What I did:

started gdb and then:

(gdb) set pagination 0
(gdb) attach 84465
Attaching to process 84465

0x00007f58bd33143e in __libc_read (fd=0, buf=0x7ffe09e53c80, nbytes=1)
at ../sysdeps/unix/sysv/linux/read.c:27
27����� ../sysdeps/unix/sysv/linux/read.c: No such file or directory.
(gdb) continue
Continuing.
[Inferior 1 (process 84465) exited normally]
(gdb) backtrace full
No stack.
(gdb) quit

that's it.

I think I have to get a stacktrace of the postgres server process , not
of psql but I do not how to do that.

Herwig

Michael Paquier <michael@paquier.xyz> writes:

On Thu, Apr 22, 2021 at 12:09:44PM +0200, Herwig Goemans wrote:

I am having trouble collection a trace file because when I attach gdb to the
process:
0x00007f99b46f7a07 in epoll_wait (epfd=5, events=0x561321b40630,
maxevents=maxevents@entry=1, timeout=timeout@entry=-1) at
../sysdeps/unix/sysv/linux/epoll_wait.c:30
30      ../sysdeps/unix/sysv/linux/epoll_wait.c: No such file or directory.

What does a simple "bt" print?

Yeah, bt should work anyway, though you won't get details about functions
for which the corresponding "debug symbols" package isn't installed.
The above is showing that you lack debug symbols for glibc, but it's
unlikely that that is going to be of interest here. What *is* of interest
is Postgres proper and the relevant FDW, so make sure that those symbols
are installed. (At least on Red Hat-based systems, gdb will tell you
which debug symbol packages you're missing when you first attach to
a process or core file.)

regards, tom lane

Herwig Goemans <herwig.goemans@gmail.com> writes:

I think I have to get a stacktrace of the postgres server process , not
of psql but I do not how to do that.

Right. The wiki page I pointed you to upthread has recipes for that.

https://wiki.postgresql.org/wiki/Generating_a_stack_trace_of_a_PostgreSQL_backend

regards, tom lane

Ok,

 I have a core dump file, It is not generated by a test of me, but that
does not matter, the sql statement was launched by a colleage of mine
and it generated a core dump.

The file is 50MB (compressed) how do I get it to you ?

Herwig

Herwig Goemans <herwig.goemans@gmail.com> writes:

 I have a core dump file, It is not generated by a test of me, but that
does not matter, the sql statement was launched by a colleage of mine
and it generated a core dump.
The file is 50MB (compressed) how do I get it to you ?

We don't want the core file; it'd be useless without an exact copy of your
postgres executables and system libraries. What we want you to do is
run gdb's "bt full" command on it and send us the output.

regards, tom lane

Via wetrasnfer:

https://we.tl/t-w3sPuA29Pu

core dump file

Right,

Sorry I sent the file to you via wetransfer but you can ignore it.

Anyway

root@o-oltpmilieu03-UB-201906:~# gdb /usr/lib/postgresql/12/bin/postgres
/home/local/VMM/h.goemans/_usr_lib_postgresql_12_bin_postgres.201.crash
GNU gdb (Ubuntu 8.1.1-0ubuntu1) 8.1.1
Copyright (C) 2018 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later
<http://gnu.org/licenses/gpl.html&gt;
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.  Type "show copying"
and "show warranty" for details.
This GDB was configured as "x86_64-linux-gnu".
Type "show configuration" for configuration details.
For bug reporting instructions, please see:
<http://www.gnu.org/software/gdb/bugs/&gt;.
Find the GDB manual and other documentation resources online at:
<http://www.gnu.org/software/gdb/documentation/&gt;.
For help, type "help".
Type "apropos word" to search for commands related to "word"...
Reading symbols from /usr/lib/postgresql/12/bin/postgres...Reading
symbols from
/usr/lib/debug/.build-id/ec/40c5b4738ba1aef9fb39dfbbf928f1bbe57743.debug...done.
done.
"/home/local/VMM/h.goemans/_usr_lib_postgresql_12_bin_postgres.201.crash"
is not a core dump: File format not recognized

It says the file is an ascii text file with very long lines...

I am lost (again)

Herwig

Herwig Goemans <herwig.goemans@gmail.com> writes:

"/home/local/VMM/h.goemans/_usr_lib_postgresql_12_bin_postgres.201.crash"
is not a core dump: File format not recognized

Hmm, that's a new one on me, but also that file name doesn't look
very much like a core file name --- usually they're just "core.NNNN"
or such. Maybe this is a version you'd base64-encoded to put onto
your web server, or something like that?

regards, tom lane

On Thu, Apr 22, 2021 at 10:50 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Herwig Goemans <herwig.goemans@gmail.com> writes:

"/home/local/VMM/h.goemans/_usr_lib_postgresql_12_bin_postgres.201.crash"
is not a core dump: File format not recognized

Hmm, that's a new one on me, but also that file name doesn't look
very much like a core file name --- usually they're just "core.NNNN"
or such. Maybe this is a version you'd base64-encoded to put onto

Hi Herwig,

On ubuntu you have to jump through a hoop to get the core file. I'd
recommend just unpacking it, with apport-unpack.

apport-unpack (crash file) (empty or nonexistent target directory for unpacking)
cd into the target directory, and you should see a CoreDump file,
which you can use with gdb.

Thanks,
Clinton

On Thu, Apr 22, 2021 at 10:09 PM Herwig Goemans
<herwig.goemans@gmail.com> wrote:

And I cannot execute anything in the attached (psql) process,

A couple of notes about the attach-then-crash approach: It should be
"postgres", not "psql", that you attach to, and then you need to type
"cont" to tell the debugger to allow execution to continue, or it'll
just block as you described. Sometimes it will stop execution when
signals arrive, and you can disable that with eg "handle SIGUSR1
nostop" and then go back to executing with "cont" again. Once you
make it crash, you'll be able to use "bt" to see exactly where, but as
mentioned it'll just be a bunch of big hex numbers unless the symbols
are installed; at the least it'll show which modules (postgres,
informix_fdw.so or such).

Hi Support,

The problem is  in ifx_fdw.c but I do not have dbg packages installed
for this nor do I know where to find these for ubuntu.

But is there no way you can simulate this issue ? There is a PG server
crash due to bad user code in a trigger function.

In the mean time we found out that the user code in the trigger function
was wrong: it has to work with old instead of new values:

     delete from informix.t_water_eenheid

                where enh_id = old.enh_id;

                return old;

instead of:

                delete from informix.t_water_eenheid

                where enh_id = new.enh_id;

                return new;

using old it works fine.

But regardless, the PG server should not crash due to bad user code. I
suppose you agree.

I only work 2 days a week for this client and this is taking up much of
my time so I was hoping you could simulate this.

Kind regards,

Herwig

Herwig Goemans <herwig.goemans@gmail.com> writes:

The problem is in ifx_fdw.c but I do not have dbg packages installed
for this nor do I know where to find these for ubuntu.

Well, at this point the right thing to do is contact the developer of
the informix FDW. The bug is most likely in that code rather than
the core server, and even if it isn't, we'd need their cooperation to
narrow it down. This list is primarily for handling bugs in the core
server, so it's likely the FDW's author doesn't read it (or at least
hasn't noticed this thread).

regards, tom lane

What mailing list do I need to use to get in contact with informix fdw ?

regards,

Herwig

Herwig Goemans <herwig.goemans@gmail.com> writes:

What mailing list do I need to use to get in contact with informix fdw ?

Perhaps filing an issue in their github project would do the trick.

https://github.com/credativ/informix_fdw

regards, tom lane

Hi Tom,

Thank you for your advice, I did as you asked and made a request (31) 8
days ago but nobody seems to take this issue under his/her care. Is
there anything that I can do to make someone look at it ?

Herwig