Seg fault when selecting from SQL Server foreign table in a plpgsql function with local variable

[shirabez]

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]

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]

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

[adamb111]

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]

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

[juliogonzalez]

@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]

@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 () No symbol table info available. #1 0x00007f1f8bf83344 in tdsEndForeignScan (node=) at src/tds_fdw.c:1931 old_cxt = festate = 0x55a6a0396038 estate = 0x55a6a03943a0 func = "tdsEndForeignScan" #2 0x000055a69f2bcad3 in ExecEndForeignScan () No symbol table info available. #3 0x000055a69f28f8f7 in standard_ExecutorEnd () No symbol table info available. #4 0x000055a69f2c4874 in ?? () No symbol table info available. #5 0x000055a69f2c4f94 in SPI_execute_plan_with_paramlist () No symbol table info available. #6 0x00007f1f8c1acca9 in ?? () from /usr/lib/postgresql/12/lib/plpgsql.so No symbol table info available. #7 0x00007f1f8c1ae123 in ?? () from /usr/lib/postgresql/12/lib/plpgsql.so No symbol table info available. #8 0x00007f1f8c1b0b64 in ?? () from /usr/lib/postgresql/12/lib/plpgsql.so No symbol table info available. #9 0x00007f1f8c1b105c in ?? () from /usr/lib/postgresql/12/lib/plpgsql.so No symbol table info available. #10 0x00007f1f8c1adf9b in ?? () from /usr/lib/postgresql/12/lib/plpgsql.so No symbol table info available. #11 0x00007f1f8c1b071b in plpgsql_exec_function () from /usr/lib/postgresql/12/lib/plpgsql.so No symbol table info available. #12 0x00007f1f8c1a36bd in plpgsql_call_handler () from /usr/lib/postgresql/12/lib/plpgsql.so No symbol table info available. #13 0x000055a69f297545 in ExecMakeTableFunctionResult () No symbol table info available. #14 0x000055a69f2a5da1 in ?? () No symbol table info available. #15 0x000055a69f296771 in ExecScan () No symbol table info available. #16 0x000055a69f28e23d in standard_ExecutorRun () No symbol table info available. #17 0x000055a69f3ead56 in ?? () No symbol table info available. #18 0x000055a69f3ec388 in PortalRun () No symbol table info available. #19 0x000055a69f3e7eff in ?? () No symbol table info available. #20 0x000055a69f3e9eb7 in PostgresMain () No symbol table info available. #21 0x000055a69f37053b in ?? () No symbol table info available. #22 0x000055a69f371513 in PostmasterMain () No symbol table info available. #23 0x000055a69f0e7cf6 in main () No symbol table info available.

[GeoffMontee]

Thanks, @shirabez. That seems odd. It seems to be segfaulting when freeing the connection's query variable:

https://github.com/tds-fdw/tds_fdw/blob/v2.0.1/src/tds_fdw.c#L1929

[adamb111]

My instruction, how to compile tds_fdw on Windows. compilation _tds_fdw_on_windows.txt

[juliogonzalez]

Thanks, the instructions are really clear.

I will see if I can create a generic README for Windows, or even better, some kind of recipe to ease stuff and prevent errors :-)

[spk-615]

Hello, I have the same error as @shirabez on: CentOS-7, PostgreSQL 10.7 Moreover it happens also when I execute same prepared statement twice. (as far as i know Postgres executes sql in plpgsql as prepared statements, so i think it may be the same issue). I'll be happy to provide more information if needed. Regards.