sqlite_fdw crashes & errors

From: Mariel Cherkassky <mariel(dot)cherkassky(at)gmail(dot)com>
To: pgsql-admin(at)lists(dot)postgresql(dot)org
Subject: sqlite_fdw crashes & errors
Date: 2018-09-16 12:01:27
Message-ID: CA+t6e1kzja9ats3yLtLYo4jY52h-cNiMxt7zyEgQixHx=vazKQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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.

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Mariel Cherkassky 2018-09-16 12:40:47 Re: sqlite_fdw crashes & errors
Previous Message Olivier Gautherot 2018-09-16 11:56:29 Re: how to debug the postgres performance issue