sqlite_fdw crashes & errors
sqlite_fdw crashes & errors
Re: 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_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.
Re: sqlite_fdw crashes & errors
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.
Re: sqlite_fdw crashes & errors
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
Re: sqlite_fdw crashes & errors
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
Re: sqlite_fdw crashes & errors
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
Re: sqlite_fdw crashes & errors
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
Re: sqlite_fdw crashes & errors
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
Re: sqlite_fdw crashes & errors
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
Re: sqlite_fdw crashes & errors
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
Re: sqlite_fdw crashes & errors
[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
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
Re: sqlite_fdw crashes & errors
>>>>> "Mariel" == Mariel Cherkassky writes: >> #2 0x00007f11c1800e24 in sqlitefdw_report_error (elevel=> out> , stmt=0x20013f8, conn=, 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)
Re: sqlite_fdw crashes & errors
>>>>> "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)
Re: sqlite_fdw crashes & errors
>>>>> "Mariel" == Mariel Cherkassky 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)
Re: sqlite_fdw crashes & errors
On 19/09/18 01:33, Andrew Gierth wrote:
>>>>>> "Mariel" == Mariel Cherkassky 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
Re: sqlite_fdw crashes & errors
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
Re: sqlite_fdw crashes & errors
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
Re: sqlite_fdw crashes & errors
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