Re: sqlite_fdw crashes & errors

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