shirabez commented 4 years ago

Postgresql: 12.1 tds-fdw: 2.0.1

I have a PostgreSQL plpgsql function that does a SELECT INTO X COUNT(*) on a SQL Server foreign table where X is a local variable declared in the function. The function runs fine the first time it is executed. On the second execution, it causes a seg fault. It's very consistent. That is, every second run of the function causes the Postgres server to raise a signal 11 seg fault.

Other details:

If I run the select outside of the function it's fine.
If I re-write the function so that it populates a temp table with all the records in the foreign table and then do a count(*) on the temp table, then that is fine, too.
If I re-write the function to use postgres_fdw and select from a Postgres foreign table, it's also fine.
I can reproduce the problem on Postgres 9.6.16 and tds-fdw 2.0.0-alpha.1.
I can reproduce the problem if I just do a select on a single column on the foreign table (without the count, but still using a function and a local variable)

So I'm thinking the issue is with tds-fdw...? Not sure if this is related to issue #51, but I've set match_column_names to both 1 or 0 in the foreign table definition and it seems to make no difference. It always crashes on the second run. Any ideas? Let me know what other info I can provide. Thank you very much.

GeoffMontee commented 4 years ago

Hi @shirabez ,

Would you be able to enable core dumps, then reproduce the issue, and then extract the backtrace of the crash from the resulting core dump?

Information on how to enable core dumps can be found in issue #218.

i.e.:

Install debuginfo:

sudo yum install postgresql12-debuginfo

Set an infinite limit on core dumps:

sudo tee /etc/systemd/system/postgresql-12.service.d/limitcore.conf <<EOF
[Service]

LimitCORE=infinity
EOF
sudo systemctl daemon-reload

Restart PostgreSQL:

sudo systemctl restart postgresql-12

Set whatever system parameters are relevant:

sudo tee /etc/sysctl.d/postgres_core.conf <<EOF
# Set the path to the core dumps
kernel.core_pattern = /core_dumps

# Add the PID to the end of the file name
kernel.core_uses_pid = 1

# Allow setuid processes to dump core. Is this necessary for Postgres?
fs.suid_dumpable = 2
EOF

Make sure the core path exists:

mkdir /core_dumps
chmod 0777 /core_dumps

You may need to recompile tds_fdw with -ggdb in PG_CPPFLAGS.
When you have a core dump, the backtraces can be extracted like this:

sudo gdb --batch --eval-command="thread apply all bt full" $(which postmaster) ${core_file_path}

shirabez commented 4 years ago

Thanks for the swift reply, Geoff. Hoping to get this to you this week.

adamb111 commented 4 years ago

Hi, I confirm this bug. I use PostgreSQL 10 on Windows. PostgreSQL v. 10.11 Microsoft SQL Server 2017 Express Edition tds_fwd v.2.0.1 compiled with MS Visual C++ 2010 Unfortunately I don't know how to get core dump on Windows. Thank you Adam

adamb111 commented 4 years ago

I found small workaroud: change SELECT INTO X COUNT() FROM Y to dynamic EXECUTE 'SELECT COUNT() FROM Y' INTO X

juliogonzalez commented 4 years ago

@adamb111 if you have clear instructions about how to build tds_fdw at Windows, can you share them?

We could add them to a README, and maybe it could help us reproducing the problems.

shirabez commented 4 years ago

@GeoffMontee Here is the backtrace from a core dump:

[New LWP 3557] [Thread debugging using libthread_db enabled] Using host libthread_db library "/lib/x86_64-linux-gnu/libthread_db.so.1". Core was generated by `postgres: 12/main: postgres [REDACTED DATABASE NAME] [local] SELECT '. Program terminated with signal SIGSEGV, Segmentation fault.

0 0x000055a69f53dbbb in pfree ()

Thread 1 (Thread 0x7f209e6f3840 (LWP 3557)):

0 0x000055a69f53dbbb in pfree ()