Re: sqlite_fdw crashes & errors

[Mariel Cherkassky <mariel.cherkassky@xxxxxxxxx>]

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@xxxxxxxxx‬‏>:‬

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.