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 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(dot)cherkassky(at)gmail(dot)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_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.
>
>
>