Обсуждение: sqlite_fdw crashes & errors
Hi,
I installed sqlite_fdw(https://github.com/pgspider/sqlite_fdw) on my postgresql v9.6.
As mentioned on the github page, the extension supports writes.
However, When I try to insert record into my foreign table my postgresql crushes :
sqlite> .schema MARIEL_TEST
CREATE TABLE MARIEL_TEST( cola INTEGER PRIMARY KEY, colb TEXT, colc INTEGER);
db=# CREATE FOREIGN TABLE MARIEL_TEST_foreign(cola integer, colbtext,colc integer) SERVER sqlite_server OPTIONS (table 'MARIEL_TEST');
CREATE FOREIGN TABLE
afa=# insert into MARIEL_TEST_foreign values(1,1,1);
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!>
!> .exit
-> ;
You are currently not connected to a database.
As you can see the database crashed. Does the reason is the extension or something that I should investigate on the database itself ?
Moreover I tried to update the table and I'm getting the following error :
update MARIEL_TEST_foreign set ip=5 where vipid=2;
ERROR: no primary key column specified for foreign table
DETAIL: For UPDATE or DELETE, at least one foreign table column must be marked as primary key column.
HINT: Set the option "key" on the columns that belong to the primary key.
foreign tables dont support primary key :
CREATE FOREIGN TABLE MARIEL_TEST_foreign(id int primary key ) SERVER sqlite_server OPTIONS (table 'MARIEL_TEST');
ERROR: primary key constraints are not supported on foreign tables
so how can I update/delete a foreign table that has primary key ? OR with that extension it isnt an option ?
Thanks , Mariel.
Hi,
I solved the error with with primary key by specifying the option (key:true) in the creation of the foreign table :
CREATE FOREIGN TABLE mariel_test(id integer OPTIONS (key 'true'),b text) SERVER sqlite_server OPTIONS (table 'MARIEL_TEST');
However, my database crashes when I try to insert/update/delete the table :
insert into mariel_Test values(2,2);
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
Any idea what I can check ?
בתאריך יום א׳, 16 בספט׳ 2018 ב-15:01 מאת Mariel Cherkassky <mariel.cherkassky@gmail.com>:
Hi,I installed sqlite_fdw(https://github.com/pgspider/sqlite_fdw) on my postgresql v9.6.As mentioned on the github page, the extension supports writes.However, When I try to insert record into my foreign table my postgresql crushes :sqlite> .schema MARIEL_TESTCREATE TABLE MARIEL_TEST( cola INTEGER PRIMARY KEY, colb TEXT, colc INTEGER);db=# CREATE FOREIGN TABLE MARIEL_TEST_foreign(cola integer, colbtext,colc integer) SERVER sqlite_server OPTIONS (table 'MARIEL_TEST');CREATE FOREIGN TABLEafa=# insert into MARIEL_TEST_foreign values(1,1,1);server closed the connection unexpectedlyThis probably means the server terminated abnormallybefore or while processing the request.The connection to the server was lost. Attempting reset: Failed.!>!> .exit-> ;You are currently not connected to a database.As you can see the database crashed. Does the reason is the extension or something that I should investigate on the database itself ?Moreover I tried to update the table and I'm getting the following error :update MARIEL_TEST_foreign set ip=5 where vipid=2;ERROR: no primary key column specified for foreign tableDETAIL: For UPDATE or DELETE, at least one foreign table column must be marked as primary key column.HINT: Set the option "key" on the columns that belong to the primary key.foreign tables dont support primary key :CREATE FOREIGN TABLE MARIEL_TEST_foreign(id int primary key ) SERVER sqlite_server OPTIONS (table 'MARIEL_TEST');ERROR: primary key constraints are not supported on foreign tablesso how can I update/delete a foreign table that has primary key ? OR with that extension it isnt an option ?Thanks , Mariel.
Forgot to mention the error from the postgresql log :
2018-09-16 08:43:20 EDT 6027 DETAIL: Failed process was running: insert into mariel_Test values(2,2);
2018-09-16 08:43:20 EDT 6027 LOG: terminating any other active server processes
2018-09-16 08:43:20 EDT db1 23253 WARNING: terminating connection because of crash of another server process
2018-09-16 08:43:20 EDT db1 23253 DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2018-09-16 08:43:20 EDT db1 23253 HINT: In a moment you should be able to reconnect to the database and repeat your command.
2018-09-16 08:43:20 EDT 22311 WARNING: terminating connection because of crash of another server process
2018-09-16 08:43:20 EDT 22311 DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2018-09-16 08:43:20 EDT 22311 HINT: In a moment you should be able to reconnect to the database and repeat your command.
2018-09-16 08:43:20 EDT db2 22316 WARNING: terminating connection because of crash of another server process
2018-09-16 08:43:20 EDT db2 22316 DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2018-09-16 08:43:20 EDT db2 22316 HINT: In a moment you should be able to reconnect to the database and repeat your command.
2018-09-16 08:43:20 EDT db2 22318 WARNING: terminating connection because of crash of another server process
2018-09-16 08:43:20 EDT db2 22318 DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2018-09-16 08:43:20 EDT db2 22318 HINT: In a moment you should be able to reconnect to the database and repeat your command.
2018-09-16 08:43:20 EDT 6027 LOG: all server processes terminated; reinitializing
2018-09-16 08:43:21 EDT 26960 LOG: database system was interrupted; last known up at 2018-09-16 08:39:06 EDT
2018-09-16 08:43:21 EDT 26960 LOG: database system was not properly shut down; automatic recovery in progress
2018-09-16 08:43:21 EDT 26960 LOG: invalid record length at 142/3B33FC8: wanted 24, got 0
2018-09-16 08:43:21 EDT 26960 LOG: redo is not required
2018-09-16 08:43:21 EDT 26960 LOG: checkpoint starting: end-of-recovery immediate
בתאריך יום א׳, 16 בספט׳ 2018 ב-15:40 מאת Mariel Cherkassky <mariel.cherkassky@gmail.com>:
Hi,I solved the error with with primary key by specifying the option (key:true) in the creation of the foreign table :CREATE FOREIGN TABLE mariel_test(id integer OPTIONS (key 'true'),b text) SERVER sqlite_server OPTIONS (table 'MARIEL_TEST');However, my database crashes when I try to insert/update/delete the table :insert into mariel_Test values(2,2);server closed the connection unexpectedlyThis probably means the server terminated abnormallybefore or while processing the request.The connection to the server was lost. Attempting reset: Failed.Any idea what I can check ?בתאריך יום א׳, 16 בספט׳ 2018 ב-15:01 מאת Mariel Cherkassky <mariel.cherkassky@gmail.com>:Hi,I installed sqlite_fdw(https://github.com/pgspider/sqlite_fdw) on my postgresql v9.6.As mentioned on the github page, the extension supports writes.However, When I try to insert record into my foreign table my postgresql crushes :sqlite> .schema MARIEL_TESTCREATE TABLE MARIEL_TEST( cola INTEGER PRIMARY KEY, colb TEXT, colc INTEGER);db=# CREATE FOREIGN TABLE MARIEL_TEST_foreign(cola integer, colbtext,colc integer) SERVER sqlite_server OPTIONS (table 'MARIEL_TEST');CREATE FOREIGN TABLEafa=# insert into MARIEL_TEST_foreign values(1,1,1);server closed the connection unexpectedlyThis probably means the server terminated abnormallybefore or while processing the request.The connection to the server was lost. Attempting reset: Failed.!>!> .exit-> ;You are currently not connected to a database.As you can see the database crashed. Does the reason is the extension or something that I should investigate on the database itself ?Moreover I tried to update the table and I'm getting the following error :update MARIEL_TEST_foreign set ip=5 where vipid=2;ERROR: no primary key column specified for foreign tableDETAIL: For UPDATE or DELETE, at least one foreign table column must be marked as primary key column.HINT: Set the option "key" on the columns that belong to the primary key.foreign tables dont support primary key :CREATE FOREIGN TABLE MARIEL_TEST_foreign(id int primary key ) SERVER sqlite_server OPTIONS (table 'MARIEL_TEST');ERROR: primary key constraints are not supported on foreign tablesso how can I update/delete a foreign table that has primary key ? OR with that extension it isnt an option ?Thanks , Mariel.
On 17/09/18 00:01, Mariel Cherkassky wrote: > Hi, > I installed sqlite_fdw(https://github.com/pgspider/sqlite_fdw) on my > postgresql v9.6. > As mentioned on the github page, the extension supports writes. > > However, When I try to insert record into my foreign table my > postgresql crushes : > > sqlite> .schema MARIEL_TEST > CREATE TABLE MARIEL_TEST( cola INTEGER PRIMARY KEY, colb TEXT, colc > INTEGER); > > db=# CREATE FOREIGN TABLE MARIEL_TEST_foreign(cola integer, > colbtext,colc integer) SERVER sqlite_server OPTIONS (table 'MARIEL_TEST'); > CREATE FOREIGN TABLE > afa=# insert into MARIEL_TEST_foreign values(1,1,1); > server closed the connection unexpectedly > This probably means the server terminated abnormally > before or while processing the request. > The connection to the server was lost. Attempting reset: Failed. > !> > !> .exit > -> ; Just tried this (Postgres 9.6.10 src build and sqlite_fdw on Ubuntu 18.04). Works for me. Hmmm interesting. I did one thing different from you - my sqlite db is under Postgres home dir (/home/postgres/sqlite). Thinking for a bit ... did you create the sqlite db as the postgres user or someone else? If the latter then that might explain the crash (e.g permission denied). Cheers Mark
What version of sqlite did you use ?
בתאריך יום ג׳, 18 בספט׳ 2018 ב-3:49 מאת Mark Kirkwood <mark.kirkwood@catalyst.net.nz>:
On 17/09/18 00:01, Mariel Cherkassky wrote:
> Hi,
> I installed sqlite_fdw(https://github.com/pgspider/sqlite_fdw) on my
> postgresql v9.6.
> As mentioned on the github page, the extension supports writes.
>
> However, When I try to insert record into my foreign table my
> postgresql crushes :
>
> sqlite> .schema MARIEL_TEST
> CREATE TABLE MARIEL_TEST( cola INTEGER PRIMARY KEY, colb TEXT, colc
> INTEGER);
>
> db=# CREATE FOREIGN TABLE MARIEL_TEST_foreign(cola integer,
> colbtext,colc integer) SERVER sqlite_server OPTIONS (table 'MARIEL_TEST');
> CREATE FOREIGN TABLE
> afa=# insert into MARIEL_TEST_foreign values(1,1,1);
> server closed the connection unexpectedly
> This probably means the server terminated abnormally
> before or while processing the request.
> The connection to the server was lost. Attempting reset: Failed.
> !>
> !> .exit
> -> ;
Just tried this (Postgres 9.6.10 src build and sqlite_fdw on Ubuntu
18.04). Works for me. Hmmm interesting. I did one thing different from
you - my sqlite db is under Postgres home dir (/home/postgres/sqlite).
Thinking for a bit ... did you create the sqlite db as the postgres user
or someone else? If the latter then that might explain the crash (e.g
permission denied).
Cheers
Mark
Hi, On 18/09/18 18:40, Mariel Cherkassky wrote: > What version of sqlite did you use ? $ sqlite3 --version 3.22.0 2018-01-22 18:45:57 0c55d179733b46d8d0ba4d88e01a25e10677046ee3da1d5b1581e86726f2alt1
I used version 3.6.24 maybe this is the reason ?
Moreover, I got a core dump and the output is :
root@node1_priviledged data]# gdb -q -c core
[New Thread 15599]
warning: no loadable sections found in added symbol-file system-supplied DSO at 0x7ffec2934000
Core was generated by `postgres: postgres afa [local] INSERT '.
Program terminated with signal 11, Segmentation fault.
#0 0x00007f128d3269df in ?? ()
any idea ?
בתאריך יום ג׳, 18 בספט׳ 2018 ב-11:24 מאת Mark Kirkwood <mark.kirkwood@catalyst.net.nz>:
Hi,
On 18/09/18 18:40, Mariel Cherkassky wrote:
> What version of sqlite did you use ?
$ sqlite3 --version
3.22.0 2018-01-22 18:45:57
0c55d179733b46d8d0ba4d88e01a25e10677046ee3da1d5b1581e86726f2alt1
Hi, On 18/09/18 20:36, Mariel Cherkassky wrote: > I used version 3.6.24 maybe this is the reason ? > Moreover, I got a core dump and the output is : > root@node1_priviledged data]# gdb -q -c core > [New Thread 15599] > > warning: no loadable sections found in added symbol-file > system-supplied DSO at 0x7ffec2934000 > Core was generated by `postgres: postgres afa [local] INSERT '. > Program terminated with signal 11, Segmentation fault. > #0 0x00007f128d3269df in ?? () > > You might need the debug symbols packages installed for Sqlite and Postgres (how to do this depends on your OS). The version difference is worth eliminating as a factor - maybe spin up a VM of Ubuntu 18.04 and test (or build Sqlite 3.22 on your OS with dev headers and libs etc). BTW , what is your OS and HW? (could be a factor)! regards Mark
I installed the 3.22 version but got the same crash on the postgresql side. I'm using CentOS release 6.10.
What is HW ? can you guide me regarding the debug symbols ?
בתאריך יום ג׳, 18 בספט׳ 2018 ב-11:59 מאת Mark Kirkwood <mark.kirkwood@catalyst.net.nz>:
Hi,
On 18/09/18 20:36, Mariel Cherkassky wrote:
> I used version 3.6.24 maybe this is the reason ?
> Moreover, I got a core dump and the output is :
> root@node1_priviledged data]# gdb -q -c core
> [New Thread 15599]
>
> warning: no loadable sections found in added symbol-file
> system-supplied DSO at 0x7ffec2934000
> Core was generated by `postgres: postgres afa [local] INSERT '.
> Program terminated with signal 11, Segmentation fault.
> #0 0x00007f128d3269df in ?? ()
>
>
You might need the debug symbols packages installed for Sqlite and
Postgres (how to do this depends on your OS).
The version difference is worth eliminating as a factor - maybe spin up
a VM of Ubuntu 18.04 and test (or build Sqlite 3.22 on your OS with dev
headers and libs etc). BTW , what is your OS and HW? (could be a factor)!
regards
Mark
the core content :
Core was generated by `postgres: postgres afa [local] INSERT '.
Program terminated with signal 11, Segmentation fault.
#0 0x00007f128d3269df in __strlen_sse42 () from /lib64/libc.so.6
Missing separate debuginfos, use: debuginfo-install audit-libs-2.4.5-6.el6.x86_64 cyrus-sasl-lib-2.1.23-15.el6_6.2.x86_64 glibc-2.12-1.212.el6.x86_64 keyutils-libs-1.4-5.el6.x86_64 krb5-libs-1.10.3-65.el6.x86_64 libcom_err-1.41.12-24.el6.x86_64 libselinux-2.0.94-7.el6.x86_64 libxml2-2.7.6-21.el6_8.1.x86_64 nspr-4.19.0-1.el6.x86_64 nss-3.36.0-8.el6.x86_64 nss-softokn-freebl-3.14.3-23.3.el6_8.x86_64 nss-util-3.36.0-1.el6.x86_64 openldap-2.4.40-16.el6.x86_64 openssl-1.0.1e-57.el6.x86_64 pam-1.1.1-24.el6.x86_64 sqlite-3.6.20-1.el6_7.2.x86_64 zlib-1.2.3-29.el6.x86_64
(gdb) bt
#0 0x00007f128d3269df in __strlen_sse42 () from /lib64/libc.so.6
#1 0x00000000007fdac1 in MemoryContextStrdup (context=0x1fd61d0, string=0x0) at mcxt.c:1155
#2 0x00007f11c1800e24 in sqlitefdw_report_error (elevel=<value optimized out>, stmt=0x20013f8, conn=<value optimized out>, sql=0x0, rc=1) at connection.c:288
#3 0x00007f11c1804efe in sqliteExecForeignInsert (estate=<value optimized out>, resultRelInfo=<value optimized out>, slot=0x200a338, planSlot=<value optimized out>) at sqlite_fdw.c:1167
#4 0x00000000005e8e89 in ExecInsert (node=0x200a008) at nodeModifyTable.c:332
#5 ExecModifyTable (node=0x200a008) at nodeModifyTable.c:1512
#6 0x00000000005ceb28 in ExecProcNode (node=0x200a008) at execProcnode.c:396
#7 0x00000000005cc62a in ExecutePlan (queryDesc=0x2001018, direction=<value optimized out>, count=0) at execMain.c:1566
#8 standard_ExecutorRun (queryDesc=0x2001018, direction=<value optimized out>, count=0) at execMain.c:338
#9 0x00000000006e491f in ProcessQuery (plan=0x1fec8f8, sourceText=0x1fbbf18 "insert into vip_foreign_3_22 values (2,2,2);", params=0x0, dest=<value optimized out>, completionTag=0x7ffec280b960 "")
at pquery.c:187
#10 0x00000000006e4b6a in PortalRunMulti (portal=0x2004a78, isTopLevel=1 '\001', setHoldSnapshot=0 '\000', dest=0x1fec9d8, altdest=0x1fec9d8, completionTag=0x7ffec280b960 "") at pquery.c:1303
#11 0x00000000006e5234 in PortalRun (portal=0x2004a78, count=9223372036854775807, isTopLevel=1 '\001', dest=0x1fec9d8, altdest=0x1fec9d8, completionTag=0x7ffec280b960 "") at pquery.c:815
#12 0x00000000006e1ed1 in exec_simple_query (query_string=0x1fbbf18 "insert into vip_foreign_3_22 values (2,2,2);") at postgres.c:1086
#13 0x00000000006e359c in PostgresMain (argc=<value optimized out>, argv=<value optimized out>, dbname=0x1f0eb78 "afa", username=<value optimized out>) at postgres.c:4072
#14 0x0000000000682ed5 in BackendRun (argc=<value optimized out>, argv=<value optimized out>) at postmaster.c:4342
#15 BackendStartup (argc=<value optimized out>, argv=<value optimized out>) at postmaster.c:4016
#16 ServerLoop (argc=<value optimized out>, argv=<value optimized out>) at postmaster.c:1721
#17 PostmasterMain (argc=<value optimized out>, argv=<value optimized ou
בתאריך יום ג׳, 18 בספט׳ 2018 ב-14:37 מאת Mariel Cherkassky <mariel.cherkassky@gmail.com>:
I installed the 3.22 version but got the same crash on the postgresql side. I'm using CentOS release 6.10.What is HW ? can you guide me regarding the debug symbols ?בתאריך יום ג׳, 18 בספט׳ 2018 ב-11:59 מאת Mark Kirkwood <mark.kirkwood@catalyst.net.nz>:Hi,
On 18/09/18 20:36, Mariel Cherkassky wrote:
> I used version 3.6.24 maybe this is the reason ?
> Moreover, I got a core dump and the output is :
> root@node1_priviledged data]# gdb -q -c core
> [New Thread 15599]
>
> warning: no loadable sections found in added symbol-file
> system-supplied DSO at 0x7ffec2934000
> Core was generated by `postgres: postgres afa [local] INSERT '.
> Program terminated with signal 11, Segmentation fault.
> #0 0x00007f128d3269df in ?? ()
>
>
You might need the debug symbols packages installed for Sqlite and
Postgres (how to do this depends on your OS).
The version difference is worth eliminating as a factor - maybe spin up
a VM of Ubuntu 18.04 and test (or build Sqlite 3.22 on your OS with dev
headers and libs etc). BTW , what is your OS and HW? (could be a factor)!
regards
Mark
I did everything again, the core changed a bit :
[root@node1_priviledged sqlite-autoconf-3220000]# /usr/local/bin/sqlite3 /var/lib/pgsql/main.sqlite
SQLite version 3.6.20
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table mariel_Test (id int);
sqlite> insert into mariel_test values(1);
sqlite> select * from mariel_Test;
1
sqlite> .schema mariel_test
CREATE TABLE mariel_Test (id int)
postgresql side :
create server sqlite_server foreign data wrapper sqlite_fdw options(database '/var/lib/pgsql/main.sqlite');
CREATE SERVER
db1=# create foreign table mariel_test_foreign (id integer) server sqlite_server options(table 'mariel_test');
CREATE FOREIGN TABLE
db1=# select * from mariel_test_foreign ;
id
1
(1 row)
db1=# insert into mariel_test_foreign values(1);
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
I had a core that was created and its content :
the core content :
[root@node1_priviledged data]# gdb -q -c core-postmaster-11-22870-1537273706
BFD: Warning: /data/pgsql/data/core-postmaster-11-22870-1537273706 is truncated: expected core file size >= 3268317184, found: 3141574656.
[New Thread 22870]
Failed to read a valid object file image from memory.
Core was generated by `postgres: postgres db1 [local] INSERT '.
Program terminated with signal 11, Segmentation fault.
#0 0x00007f128d3269df in ?? ()
(gdb) bt
#0 0x00007f128d3269df in ?? ()
Cannot access memory at address 0x7ffec280b4b8
(gdb) bt full
#0 0x00007f128d3269df in ?? ()
No symbol table info available.
Cannot access memory at address 0x7ffec280b4b8
בתאריך יום ג׳, 18 בספט׳ 2018 ב-15:05 מאת Mariel Cherkassky <mariel.cherkassky@gmail.com>:
the core content :Core was generated by `postgres: postgres afa [local] INSERT '.Program terminated with signal 11, Segmentation fault.#0 0x00007f128d3269df in __strlen_sse42 () from /lib64/libc.so.6Missing separate debuginfos, use: debuginfo-install audit-libs-2.4.5-6.el6.x86_64 cyrus-sasl-lib-2.1.23-15.el6_6.2.x86_64 glibc-2.12-1.212.el6.x86_64 keyutils-libs-1.4-5.el6.x86_64 krb5-libs-1.10.3-65.el6.x86_64 libcom_err-1.41.12-24.el6.x86_64 libselinux-2.0.94-7.el6.x86_64 libxml2-2.7.6-21.el6_8.1.x86_64 nspr-4.19.0-1.el6.x86_64 nss-3.36.0-8.el6.x86_64 nss-softokn-freebl-3.14.3-23.3.el6_8.x86_64 nss-util-3.36.0-1.el6.x86_64 openldap-2.4.40-16.el6.x86_64 openssl-1.0.1e-57.el6.x86_64 pam-1.1.1-24.el6.x86_64 sqlite-3.6.20-1.el6_7.2.x86_64 zlib-1.2.3-29.el6.x86_64(gdb) bt#0 0x00007f128d3269df in __strlen_sse42 () from /lib64/libc.so.6#1 0x00000000007fdac1 in MemoryContextStrdup (context=0x1fd61d0, string=0x0) at mcxt.c:1155#2 0x00007f11c1800e24 in sqlitefdw_report_error (elevel=<value optimized out>, stmt=0x20013f8, conn=<value optimized out>, sql=0x0, rc=1) at connection.c:288#3 0x00007f11c1804efe in sqliteExecForeignInsert (estate=<value optimized out>, resultRelInfo=<value optimized out>, slot=0x200a338, planSlot=<value optimized out>) at sqlite_fdw.c:1167#4 0x00000000005e8e89 in ExecInsert (node=0x200a008) at nodeModifyTable.c:332#5 ExecModifyTable (node=0x200a008) at nodeModifyTable.c:1512#6 0x00000000005ceb28 in ExecProcNode (node=0x200a008) at execProcnode.c:396#7 0x00000000005cc62a in ExecutePlan (queryDesc=0x2001018, direction=<value optimized out>, count=0) at execMain.c:1566#8 standard_ExecutorRun (queryDesc=0x2001018, direction=<value optimized out>, count=0) at execMain.c:338#9 0x00000000006e491f in ProcessQuery (plan=0x1fec8f8, sourceText=0x1fbbf18 "insert into vip_foreign_3_22 values (2,2,2);", params=0x0, dest=<value optimized out>, completionTag=0x7ffec280b960 "")at pquery.c:187#10 0x00000000006e4b6a in PortalRunMulti (portal=0x2004a78, isTopLevel=1 '\001', setHoldSnapshot=0 '\000', dest=0x1fec9d8, altdest=0x1fec9d8, completionTag=0x7ffec280b960 "") at pquery.c:1303#11 0x00000000006e5234 in PortalRun (portal=0x2004a78, count=9223372036854775807, isTopLevel=1 '\001', dest=0x1fec9d8, altdest=0x1fec9d8, completionTag=0x7ffec280b960 "") at pquery.c:815#12 0x00000000006e1ed1 in exec_simple_query (query_string=0x1fbbf18 "insert into vip_foreign_3_22 values (2,2,2);") at postgres.c:1086#13 0x00000000006e359c in PostgresMain (argc=<value optimized out>, argv=<value optimized out>, dbname=0x1f0eb78 "afa", username=<value optimized out>) at postgres.c:4072#14 0x0000000000682ed5 in BackendRun (argc=<value optimized out>, argv=<value optimized out>) at postmaster.c:4342#15 BackendStartup (argc=<value optimized out>, argv=<value optimized out>) at postmaster.c:4016#16 ServerLoop (argc=<value optimized out>, argv=<value optimized out>) at postmaster.c:1721#17 PostmasterMain (argc=<value optimized out>, argv=<value optimized ouבתאריך יום ג׳, 18 בספט׳ 2018 ב-14:37 מאת Mariel Cherkassky <mariel.cherkassky@gmail.com>:I installed the 3.22 version but got the same crash on the postgresql side. I'm using CentOS release 6.10.What is HW ? can you guide me regarding the debug symbols ?בתאריך יום ג׳, 18 בספט׳ 2018 ב-11:59 מאת Mark Kirkwood <mark.kirkwood@catalyst.net.nz>:Hi,
On 18/09/18 20:36, Mariel Cherkassky wrote:
> I used version 3.6.24 maybe this is the reason ?
> Moreover, I got a core dump and the output is :
> root@node1_priviledged data]# gdb -q -c core
> [New Thread 15599]
>
> warning: no loadable sections found in added symbol-file
> system-supplied DSO at 0x7ffec2934000
> Core was generated by `postgres: postgres afa [local] INSERT '.
> Program terminated with signal 11, Segmentation fault.
> #0 0x00007f128d3269df in ?? ()
>
>
You might need the debug symbols packages installed for Sqlite and
Postgres (how to do this depends on your OS).
The version difference is worth eliminating as a factor - maybe spin up
a VM of Ubuntu 18.04 and test (or build Sqlite 3.22 on your OS with dev
headers and libs etc). BTW , what is your OS and HW? (could be a factor)!
regards
Mark
>>>>> "Mariel" == Mariel Cherkassky <mariel.cherkassky@gmail.com> writes: >> #2 0x00007f11c1800e24 in sqlitefdw_report_error (elevel=<value optimized >> out> , stmt=0x20013f8, conn=<value optimized out>, sql=0x0, rc=1) at >> connection.c:288 This is a bug in sqlite_fdw - it's assuming it can use sqlite3_sql to get the sql source text back from a statement, but that's only true when the statement was prepared using a different API than the one actually being used. So sqlite3_sql ends up returning null, and pstrdup naturally crashes on that. -- Andrew (irc:RhodiumToad)
So, how exactly it works for other people ? Shouldnt everyone who use this extension should have suffered from this bug ? And why for selects it works fine ?
בתאריך יום ג׳, 18 בספט׳ 2018 ב-16:13 מאת Andrew Gierth <andrew@tao11.riddles.org.uk>:
>>>>> "Mariel" == Mariel Cherkassky <mariel.cherkassky@gmail.com> writes:
>> #2 0x00007f11c1800e24 in sqlitefdw_report_error (elevel=<value optimized
>> out> , stmt=0x20013f8, conn=<value optimized out>, sql=0x0, rc=1) at
>> connection.c:288
This is a bug in sqlite_fdw - it's assuming it can use sqlite3_sql to
get the sql source text back from a statement, but that's only true when
the statement was prepared using a different API than the one actually
being used. So sqlite3_sql ends up returning null, and pstrdup naturally
crashes on that.
--
Andrew (irc:RhodiumToad)
>>>>> "Mariel" == Mariel Cherkassky <mariel.cherkassky@gmail.com> writes: Mariel> So, how exactly it works for other people ? Shouldnt everyone Mariel> who use this extension should have suffered from this bug ? And Mariel> why for selects it works fine ? The bug is in error reporting, so it kicks in only when an error occurs and then only when a null value for the sql text is passed to the reporting function. -- Andrew (irc:RhodiumToad)
On 19/09/18 01:33, Andrew Gierth wrote:
>>>>>> "Mariel" == Mariel Cherkassky <mariel.cherkassky@gmail.com> writes:
> Mariel> So, how exactly it works for other people ? Shouldnt everyone
> Mariel> who use this extension should have suffered from this bug ? And
> Mariel> why for selects it works fine ?
>
> The bug is in error reporting, so it kicks in only when an error occurs
> and then only when a null value for the sql text is passed to the
> reporting function.
>
Ah right - I guess I was not tickling the bug correctly:
$ chmod 444 sqlite/test.db
$
$ psql
=# insert into MARIEL_TEST_foreign values(4,1,1);
ERROR: failed to execute remote SQL: rc=1 SQL logic error
sql=INSERT INTO main."MARIEL_TEST"("cola", "colb", "colc") VALUES (?,
?, ?)
regards
Mark
But in my case i'm not getting the error you got. Instead, my postgres instance crashes.
בתאריך יום ד׳, 19 בספט׳ 2018 ב-2:02 מאת Mark Kirkwood <mark.kirkwood@catalyst.net.nz>:
On 19/09/18 01:33, Andrew Gierth wrote:
>>>>>> "Mariel" == Mariel Cherkassky <mariel.cherkassky@gmail.com> writes:
> Mariel> So, how exactly it works for other people ? Shouldnt everyone
> Mariel> who use this extension should have suffered from this bug ? And
> Mariel> why for selects it works fine ?
>
> The bug is in error reporting, so it kicks in only when an error occurs
> and then only when a null value for the sql text is passed to the
> reporting function.
>
Ah right - I guess I was not tickling the bug correctly:
$ chmod 444 sqlite/test.db
$
$ psql
=# insert into MARIEL_TEST_foreign values(4,1,1);
ERROR: failed to execute remote SQL: rc=1 SQL logic error
sql=INSERT INTO main."MARIEL_TEST"("cola", "colb", "colc") VALUES (?,
?, ?)
regards
Mark
On 19/09/18 17:08, Mariel Cherkassky wrote: > But in my case i'm not getting the error you got. Instead, my postgres > instance crashes. > > > > > The bug is in error reporting, so it kicks in only when an error > occurs > > and then only when a null value for the sql text is passed to the > > reporting function. > ^^^ My method of making it error appears to have sql text, somehow in your case there is none (which is interesting), but maybe not worth investigating as there is obviously a bug in the error handling - we can retest when it is patched! regards Mark
I opened a ticket in the git page of the extension. It seems like a bug in the extension, the extension`s developer is working on it..
בתאריך יום ד׳, 19 בספט׳ 2018 ב-10:50 מאת Mark Kirkwood <mark.kirkwood@catalyst.net.nz>:
On 19/09/18 17:08, Mariel Cherkassky wrote:
> But in my case i'm not getting the error you got. Instead, my postgres
> instance crashes.
>
> >
> > The bug is in error reporting, so it kicks in only when an error
> occurs
> > and then only when a null value for the sql text is passed to the
> > reporting function.
>
^^^ My method of making it error appears to have sql text, somehow in
your case there is none (which is interesting), but maybe not worth
investigating as there is obviously a bug in the error handling - we can
retest when it is patched!
regards
Mark