support SQLite plugins

[ghost]

Problem

Is it possible to support sqlite plugins (.so/.dll)?

Context

I wanted use FTS5 from sqlite to replace postgresql's built-in FTS, which does not have BM25 ranking support.

The idea was to use something like

-- Create a table. And an external content fts5 table to index it.
CREATE TABLE tbl(a INTEGER PRIMARY KEY, b, c);
CREATE VIRTUAL TABLE fts_idx USING fts5(b, c, content='tbl', content_rowid='a', tokenize='myowntokenizer');

-- Triggers to keep the FTS index up to date.
CREATE TRIGGER tbl_ai AFTER INSERT ON tbl BEGIN
  INSERT INTO fts_idx(rowid, b, c) VALUES (new.a, new.b, new.c);
END;
CREATE TRIGGER tbl_ad AFTER DELETE ON tbl BEGIN
  INSERT INTO fts_idx(fts_idx, rowid, b, c) VALUES('delete', old.a, old.b, old.c);
END;
CREATE TRIGGER tbl_au AFTER UPDATE ON tbl BEGIN
  INSERT INTO fts_idx(fts_idx, rowid, b, c) VALUES('delete', old.a, old.b, old.c);
  INSERT INTO fts_idx(rowid, b, c) VALUES (new.a, new.b, new.c);
END;

to sync data from postgresql table to a sqlite foreign table, but I found that I cannot load the customized tokenizer (myowntokenizer.so/myowntokenizer.dll) on the sqlite side.

[t-kataym]

Do you expect sqlite_fdw to call load_extension()? If you mean so, I think it is feasible technically.

But we have no plan to develop such feature now. Your contribution is welcome.

[mkgrgis]

@nick008a, for PostgreSQL SQLite runtime plugins is outer third-party binary code with execution rights. This can be a security issue. Maybe using SQLite C-code with included functions from most popular plugins will be better.

[ghost]

Do you expect sqlite_fdw to call load_extension()?

Yes.

This can be a security issue.

I know, but I think there should be an option to enable this.

[mkgrgis]

@nick008a , You can write a function for PostgreSQL database superuser like in this places https://github.com/pgspider/sqlite_fdw/blob/dbb7a390c2c6b2aeaa543777391f1dbc7e2d0401/sqlite_fdw--1.0--1.1.sql#L6 https://github.com/pgspider/sqlite_fdw/blob/dbb7a390c2c6b2aeaa543777391f1dbc7e2d0401/connection.c#L64 https://github.com/pgspider/sqlite_fdw/blob/dbb7a390c2c6b2aeaa543777391f1dbc7e2d0401/connection.c#L694 About SQLite C call see in https://www.sqlite.org/c3ref/load_extension.html

Please also add testing case: SQLite query with function from some extension -> not exist, load extension -> query OK.

[mkgrgis]

@nick008a , have you got any results or problems with implementation?