Thread: postgres server process crashes when using odbc_fdw
Version 10.5 AWS Linux Foreign server: Db2 on Linux 10.5.9 carto_odbc unix_odbc I am able to build odbc_fdw extension, register the extension and also create foreign server. I also created foreign table. When I run a sql 'select * from odbctest' postgres crashes, generate a core file. 2018-10-16 20:03:42.494 EDT [4639] LOG: listening on IPv4 address "127.0.0.1", port 5432 2018-10-16 20:03:42.498 EDT [4639] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432" 2018-10-16 20:03:42.513 EDT [4640] LOG: database system was shut down at 2018-10-16 20:00:05 EDT 2018-10-16 20:03:42.517 EDT [4639] LOG: database system is ready to accept connections 2018-10-16 20:05:11.777 EDT [4639] LOG: server process (PID 4787) was terminated by signal 11: Segmentation fault 2018-10-16 20:05:11.777 EDT [4639] DETAIL: Failed process was running: select * from odbctest ; 2018-10-16 20:05:11.777 EDT [4639] LOG: terminating any other active server processes 2018-10-16 20:05:11.777 EDT [4644] WARNING: terminating connection because of crash of another server process 2018-10-16 20:05:11.777 EDT [4644] DETAIL: The postmaster has commanded this server process to roll back the current transactionand exit, because another server process exited abnormally and possibly corrupted shared memory. 2018-10-16 20:05:11.777 EDT [4644] HINT: In a moment you should be able to reconnect to the database and repeat your command. 2018-10-16 20:05:11.777 EDT [4816] WARNING: terminating connection because of crash of another server process 2018-10-16 20:05:11.777 EDT [4816] DETAIL: The postmaster has commanded this server process to roll back the current transactionand exit, because another server process exited abnormally and possibly corrupted shared memory. 2018-10-16 20:05:11.777 EDT [4816] HINT: In a moment you should be able to reconnect to the database and repeat your command. 2018-10-16 20:05:11.778 EDT [4639] LOG: all server processes terminated; reinitializing 2018-10-16 20:05:11.847 EDT [4818] LOG: database system was interrupted; last known up at 2018-10-16 20:03:42 EDT 2018-10-16 20:05:11.860 EDT [4818] LOG: database system was not properly shut down; automatic recovery in progress 2018-10-16 20:05:11.863 EDT [4818] LOG: redo starts at 5/7F9971A0 2018-10-16 20:05:11.863 EDT [4818] LOG: invalid record length at 5/7F9971D8: wanted 24, got 0 2018-10-16 20:05:11.863 EDT [4818] LOG: redo done at 5/7F9971A0 2018-10-16 20:05:11.877 EDT [4639] LOG: database system is ready to accept connections
I enabled ODBC trace . Attached trace log. Had to edit connection details and snipped few repetitive lines as I ran
thesame sql multiple times
Connection Out [DSN=BLUE;UID=*;PWD=***;DATABASE=***;HOST...]
[ODBC][4248][1539734369.611042][SQLGetInfo.c][554]
Entry:
Connection = 0x2150e90
Info Type = SQL_IDENTIFIER_QUOTE_CHAR (29)
Info Value = 0x7ffdaf8700ee
Buffer Length = 2
StrLen = (nil)
[ODBC][4248][1539734369.611055][SQLGetInfo.c][617]
Exit:[SQL_SUCCESS]
[ODBC][4248][1539734369.611067][SQLGetInfo.c][554]
Entry:
Connection = 0x2150e90
Info Type = SQL_CATALOG_NAME_SEPARATOR (41)
Info Value = 0x7ffdaf8700ee
Buffer Length = 2
StrLen = (nil)
[ODBC][4248][1539734369.611075][SQLGetInfo.c][617]
Exit:[SQL_SUCCESS]
[ODBC][4248][1539734369.611092][SQLAllocHandle.c][540]
Entry:
Handle Type = 3
Input Handle = 0x2150e90
[ODBC][4248][1539734369.611122][SQLAllocHandle.c][1081]
Exit:[SQL_SUCCESS]
Output Handle = 0x2167610
[ODBC][4248][1539734369.611138][SQLExecDirect.c][240]
Entry:
Statement = 0x2167610
SQL = [select fld1,fld2 from rkrishna.fdwtest][length = 38 (SQL_NTS)]
[ODBC][4248][1539734369.612221][SQLExecDirect.c][503]
Exit:[SQL_SUCCESS]
[ODBC][4248][1539734369.612242][SQLNumResultCols.c][156]
Entry:
Statement = 0x2167610
Column Count = 0x7ffdaf87015e
[ODBC][4248][1539734369.612257][SQLNumResultCols.c][248]
Exit:[SQL_SUCCESS]
Count = 0x7ffdaf87015e -> 2
[ODBC][4248][1539734369.612282][SQLFetch.c][162]
Entry:
Statement = 0x2167610
[ODBC][4248][1539734369.612303][SQLFetch.c][348]
Exit:[SQL_SUCCESS]
[ODBC][4248][1539734369.612314][SQLNumResultCols.c][156]
Entry:
Statement = 0x2167610
Column Count = 0x7ffdaf8701ca
[ODBC][4248][1539734369.612321][SQLNumResultCols.c][248]
Exit:[SQL_SUCCESS]
Count = 0x7ffdaf8701ca -> 2
[ODBC][4248][1539734369.612334][SQLDescribeCol.c][247]
Entry:
Statement = 0x2167610
Column Number = 1
Column Name = 0x2164b78
Buffer Length = 255
Name Length = 0x7ffdaf8701cc
Data Type = 0x7ffdaf8701ce
Column Size = 0x7ffdaf8701d8
Decimal Digits = 0x7ffdaf8701d0
Nullable = 0x7ffdaf8701d2
[ODBC][4248][1539734369.612394][SQLDescribeCol.c][497]
Exit:[SQL_SUCCESS]
Column Name = [FLD1]
Data Type = 0x7ffdaf8701ce -> 4
Column Size = 0x7ffdaf8701d8 -> 10
Decimal Digits = 0x7ffdaf8701d0 -> 0
Nullable = 0x7ffdaf8701d2 -> 1
[ODBC][4248][1539734369.612409][SQLDescribeCol.c][247]
Entry:
Statement = 0x2167610
Column Number = 2
Column Name = 0x2164b78
Buffer Length = 255
Name Length = 0x7ffdaf8701cc
Data Type = 0x7ffdaf8701ce
Column Size = 0x7ffdaf8701d8
Decimal Digits = 0x7ffdaf8701d0
Nullable = 0x7ffdaf8701d2
[ODBC][4248][1539734369.612419][SQLDescribeCol.c][497]
Exit:[SQL_SUCCESS]
Column Name = [FLD2]
Data Type = 0x7ffdaf8701ce -> 1
Column Size = 0x7ffdaf8701d8 -> 1
Decimal Digits = 0x7ffdaf8701d0 -> 0
Nullable = 0x7ffdaf8701d2 -> 1
[ODBC][4787][1539734703.466709][__handles.c][460]
Exit:[SQL_SUCCESS]
Environment = 0x2432cd0
[ODBC][4787][1539734703.466760][SQLSetEnvAttr.c][189]
Entry:
Environment = 0x2432cd0
Attribute = SQL_ATTR_ODBC_VERSION
Value = 0x3
StrLen = 0
[ODBC][4787][1539734703.466769][SQLSetEnvAttr.c][363]
Exit:[SQL_SUCCESS]
[ODBC][4787][1539734703.466776][SQLAllocHandle.c][375]
Entry:
Handle Type = 2
Input Handle = 0x2432cd0
[ODBC][4787][1539734703.466783][SQLAllocHandle.c][493]
Exit:[SQL_SUCCESS]
Output Handle = 0x24bc7f0
[ODBC][4787][1539734703.466794][SQLDriverConnect.c][726]
Entry:
Connection = 0x24bc7f0
Window Hdl = (nil)
Str In = [DSN=BLUE][length = 8 (SQL_NTS)]
Str Out = 0x7ffec5a2d370
Str Out Max = 1024
Str Out Ptr = 0x7ffec5a2d34e
Completion = 1
UNICODE Using encoding ASCII 'ISO8859-1' and UNICODE ''
connection details removed.
[ODBC][4787][1539734705.934065][SQLAllocHandle.c][540]
Entry:
Handle Type = 3
Input Handle = 0x24bc7f0
[ODBC][4787][1539734705.934091][SQLAllocHandle.c][1081]
Exit:[SQL_SUCCESS]
Output Handle = 0x24fc9e0
[ODBC][4787][1539734705.934106][SQLExecDirect.c][240]
Entry:
Statement = 0x24fc9e0
SQL = [select count(*) from rkrishna.fdwtest][length = 37 (SQL_NTS)]
[ODBC][4787][1539734705.935348][SQLExecDirect.c][503]
Exit:[SQL_SUCCESS]
[ODBC][4787][1539734705.935389][SQLFetch.c][162]
Entry:
Statement = 0x24fc9e0
[ODBC][4787][1539734705.935431][SQLFetch.c][348]
Exit:[SQL_SUCCESS]
[ODBC][4787][1539734705.935466][SQLGetData.c][237]
Entry:
Statement = 0x24fc9e0
Column Number = 1
Target Type = -27 SQL_C_SBIGINT
Buffer Length = 0
Target Value = 0x7ffec5a2d7d0
StrLen Or Ind = 0x7ffec5a2d7d8
[ODBC][4787][1539734705.935515][SQLGetData.c][501]
Exit:[SQL_SUCCESS]
Buffer = [Data...]
Strlen Or Ind = 0x7ffec5a2d7d8 -> 8
[ODBC][4787][1539734705.935552][SQLFreeHandle.c][381]
Entry:
Handle Type = 3
Input Handle = 0x24fc9e0
[ODBC][4787][1539734705.936176][SQLFreeHandle.c][491]
Exit:[SQL_SUCCESS]
[ODBC][4787][1539734705.936195][SQLFreeHandle.c][284]
Entry:
Handle Type = 2
Input Handle = 0x24bc7f0
[ODBC][4787][1539734705.936205][SQLFreeHandle.c][298]Error: HY010
[ODBC][4787][1539734705.936222][SQLFreeHandle.c][219]
Entry:
Handle Type = 1
Input Handle = 0x2432cd0
[ODBC][4787][1539734705.936233][SQLFreeHandle.c][233]Error: HY010
[ODBC][4787][1539734705.936362][__handles.c][460]
Exit:[SQL_SUCCESS]
Environment = 0x24fbb30
[ODBC][4787][1539734705.936407][SQLSetEnvAttr.c][189]
Entry:
Environment = 0x24fbb30
Attribute = SQL_ATTR_ODBC_VERSION
Value = 0x3
StrLen = 0
[ODBC][4787][1539734705.936419][SQLSetEnvAttr.c][363]
Exit:[SQL_SUCCESS]
[ODBC][4787][1539734705.936431][SQLAllocHandle.c][375]
Entry:
Handle Type = 2
Input Handle = 0x24fbb30
[ODBC][4787][1539734705.936453][SQLAllocHandle.c][493]
Exit:[SQL_SUCCESS]
Output Handle = 0x2526b90
[ODBC][4787][1539734705.936466][SQLDriverConnect.c][726]
Entry:
Connection = 0x2526b90
Window Hdl = (nil)
Str In = [DSN=BLUE][length = 8 (SQL_NTS)]
Str Out = 0x7ffec5a2d3a0
Str Out Max = 1024
Str Out Ptr = 0x7ffec5a2d37e
Completion = 1
UNICODE Using encoding ASCII 'ISO8859-1' and UNICODE ''
[ODBC][4787][1539734708.340223][SQLExecDirect.c][240]
Entry:
Statement = 0x24fe2e0
SQL = [select count(*) from rkrishna.fdwtest][length = 37 (SQL_NTS)]
[ODBC][4787][1539734708.380473][SQLExecDirect.c][503]
Exit:[SQL_SUCCESS]
[ODBC][4787][1539734708.380501][SQLFetch.c][162]
Entry:
Statement = 0x24fe2e0
[ODBC][4787][1539734708.380523][SQLFetch.c][348]
Exit:[SQL_SUCCESS]
[ODBC][4787][1539734708.380536][SQLGetData.c][237]
Entry:
Statement = 0x24fe2e0
Column Number = 1
Target Type = -27 SQL_C_SBIGINT
Buffer Length = 0
Target Value = 0x7ffec5a2d800
StrLen Or Ind = 0x7ffec5a2d808
[ODBC][4787][1539734708.380555][SQLGetData.c][501]
Exit:[SQL_SUCCESS]
Buffer = [Data...]
Strlen Or Ind = 0x7ffec5a2d808 -> 8
[ODBC][4787][1539734708.380567][SQLFreeHandle.c][381]
Entry:
Handle Type = 3
Input Handle = 0x24fe2e0
[ODBC][4787][1539734708.381263][SQLFreeHandle.c][491]
Exit:[SQL_SUCCESS]
[ODBC][4787][1539734708.381281][SQLFreeHandle.c][284]
Entry:
Handle Type = 2
Input Handle = 0x2526b90
[ODBC][4787][1539734708.381292][SQLFreeHandle.c][298]Error: HY010
[ODBC][4787][1539734708.381314][SQLFreeHandle.c][219]
Entry:
Handle Type = 1
Input Handle = 0x24fbb30
[ODBC][4787][1539734708.381326][SQLFreeHandle.c][233]Error: HY010
[ODBC][4787][1539734708.381400][__handles.c][460]
Exit:[SQL_SUCCESS]
Environment = 0x2519280
[ODBC][4787][1539734708.381418][SQLSetEnvAttr.c][189]
Entry:
Environment = 0x2519280
Attribute = SQL_ATTR_ODBC_VERSION
Value = 0x3
StrLen = 0
[ODBC][4787][1539734708.381429][SQLSetEnvAttr.c][363]
Exit:[SQL_SUCCESS]
[ODBC][4787][1539734708.381440][SQLAllocHandle.c][375]
Entry:
Handle Type = 2
Input Handle = 0x2519280
[ODBC][4787][1539734708.381452][SQLAllocHandle.c][493]
Exit:[SQL_SUCCESS]
Output Handle = 0x253d320
[ODBC][4787][1539734708.381465][SQLDriverConnect.c][726]
Entry:
Connection = 0x253d320
Window Hdl = (nil)
Str In = [DSN=BLUE][length = 8 (SQL_NTS)]
Str Out = 0x7ffec5a2d5b0
Str Out Max = 1024
Str Out Ptr = 0x7ffec5a2d58e
Completion = 1
UNICODE Using encoding ASCII 'ISO8859-1' and UNICODE ''
It turned out that enabling ODBC trace was causing PG to crash. Once disabled it started working, but found another issue. All object names in DB2 is assumed to be upper case. odbc_fdw sends queries like this select "fld1","fld2" from "schema_name"."table_name". So the foreign table in PG has to created in upper case within quotes. It is bit of an annoyance.
Hi, On 2018-10-17 11:17:11 -0400, Ravi Krishna wrote: > It turned out that enabling ODBC trace was causing PG to crash. Once disabled it started working, but found another issue. > All object names in DB2 is assumed to be upper case. odbc_fdw sends queries like this > > > select "fld1","fld2" from "schema_name"."table_name". > > So the foreign table in PG has to created in upper case within quotes. It is bit of an annoyance. Please note that odbc_fdw is not maintained by the postgresql developers, but a separate project. Greetings, Andres Freund
> > Please note that odbc_fdw is not maintained by the postgresql developers, but a separate project. Translation: You are on your own. We are hoping this will make our migration out of DB2 quicker. Oh well.
On 10/17/18 10:57 AM, Ravi Krishna wrote: > >> >> Please note that odbc_fdw is not maintained by the postgresql developers, but a separate project. > > > Translation: You are on your own. We are hoping this will make our migration out of DB2 quicker. Oh well. > No it means you need to take this up with the project maintainer(s) as they are the folks that can determine whether it is fixable or not: https://github.com/ZhengYang/odbc_fdw -- Adrian Klaver adrian.klaver@aklaver.com
On October 17, 2018 10:57:37 AM PDT, Ravi Krishna <srkrishna1@aol.com> wrote: > >> >> Please note that odbc_fdw is not maintained by the postgresql >developers, but a separate project. > > >Translation: You are on your own. We are hoping this will make our >migration out of DB2 quicker. Oh well. Come on. We can't realistically support & debug random postgres extending projects, nor do we have control over them. Andyou're not necessarily on your own, you could report the issue to odbcfdw's authors/github tracker. Or pay a companyfor support. Andres -- Sent from my Android device with K-9 Mail. Please excuse my brevity.
> > Come on. We can't realistically support & debug random postgres extending projects, nor do we have control over them. Andyou're not necessarily on your own, you could report the issue to odbcfdw's authors/github tracker. Or pay a companyfor support. > On a related note is fdw for Oracle and SQLServer supported by the community ?
Hi, On 2018-10-17 14:12:01 -0400, Ravi Krishna wrote: > > > > Come on. We can't realistically support & debug random postgres extending projects, nor do we have control over them.And you're not necessarily on your own, you could report the issue to odbcfdw's authors/github tracker. Or pay a companyfor support. > > > > On a related note is fdw for Oracle and SQLServer supported by the community ? They're not postgresql.org projects if that's what you're asking. IOW, they probably have their own communities, it's just not this. Greetings, Andres Freund
On 10/17/18 11:12 AM, Ravi Krishna wrote: >> >> Come on. We can't realistically support & debug random postgres extending projects, nor do we have control over them.And you're not necessarily on your own, you could report the issue to odbcfdw's authors/github tracker. Or pay a companyfor support. >> > > On a related note is fdw for Oracle and SQLServer supported by the community ? > For FDW info see: https://wiki.postgresql.org/wiki/Fdw -- Adrian Klaver adrian.klaver@aklaver.com
On 2018-10-17 11:02:40 -0700, Adrian Klaver wrote: > On 10/17/18 10:57 AM, Ravi Krishna wrote: > > > > > > > > Please note that odbc_fdw is not maintained by the postgresql developers, but a separate project. > > > > > > Translation: You are on your own. We are hoping this will make our migration out of DB2 quicker. Oh well. > > > > No it means you need to take this up with the project maintainer(s) as they > are the folks that can determine whether it is fixable or not: > > https://github.com/ZhengYang/odbc_fdw Looks like https://github.com/CartoDB/odbc_fdw is more recently maintained.
Ravi Krishna wrote: > On a related note is fdw for Oracle and SQLServer supported by the community ? The PostgreSQL Global Development Group, specifically these mailing lists, will provide support for "core PostgreSQL", that is the source code you can download from www.postgresql.org and binaries built from that source. The support here does not provide any guarantees, but it also has no well-defined limits. So if you ask well, people will also try to help you with versions that are "out of support" or third-party products like the foreign data wrappers you mention above. Likewise, "the community" has no well-defined limits: in a way, people who provide third-party open source extensions also are a part of the community, or at least the communities overlap. That said, it is a good idea to contact the maintainers of those third-party extensions first: usually, they know more about their product than the general public, and it also keeps mails off the main list that are of little interest to most people. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com