search

pgspider / sqlite_fdw

SQLite Foreign Data Wrapper for PostgreSQL
Other
219 stars 37 forks source link

Error when INSERT by SELECT foreign table #13

Closed thongpvn closed 5 years ago

thongpvn commented 5 years ago

I turned on the debug mode of Postgres. The result log of example scenario is as below:

-- In SQLite
CREATE TABLE test001 (
    a   INT,
    b   TEXT
);

-- In SQLite FDW
CREATE EXTENSION sqlite_fdw;
CREATE SERVER sqlite_svr FOREIGN DATA WRAPPER sqlite_fdw
OPTIONS (database '/tmp/sqlitefdw_test.db');
CREATE FOREIGN TABLE test001 (
    a   INT,
    b   TEXT
) SERVER sqlite_svr;
INSERT INTO test001 VALUES (5, 'foo');
DEBUG:  sqlite_fdw : sqlite_fdw_handler
DEBUG:  sqlite_fdw : sqlitePlanForeignModify
DEBUG:  sqlite_fdw : sqlite_fdw_handler
DEBUG:   sqlite_fdw : sqliteBeginForeignModify
DEBUG:  sqlite_fdw : sqlite_prepare_wrapper INSERT INTO main."test001"("a", "b") VALUES (?, ?)

DEBUG:  sqlite_fdw : sqliteExecForeignInsert
DEBUG:  sqlite_fdw : sqliteEndForeignModify
DEBUG:  xact_callback 5
INSERT INTO test001 VALUES (15, 'bar');
DEBUG:  sqlite_fdw : sqlite_fdw_handler
DEBUG:  sqlite_fdw : sqlitePlanForeignModify
DEBUG:   sqlite_fdw : sqliteBeginForeignModify
DEBUG:  sqlite_fdw : sqlite_prepare_wrapper INSERT INTO main."test001"("a", "b") VALUES (?, ?)

DEBUG:  sqlite_fdw : sqliteExecForeignInsert
DEBUG:  sqlite_fdw : sqliteEndForeignModify
DEBUG:  xact_callback 5
SELECT * FROM test001;
DEBUG:  sqlite_fdw : sqliteGetForeignRelSize
DEBUG:  sqlite_fdw : sqliteGetForeignPaths
DEBUG:  sqlite_fdw : sqliteGetForeignUpperPaths
DEBUG:  sqlite_fdw : sqliteGetForeignPlan
DEBUG:  sqlite_fdw : sqliteBeginForeignScan
DEBUG:  sqlite_fdw : sqlite_prepare_wrapper SELECT "a", "b" FROM main."test001" 

DEBUG:  sqlite_fdw : sqliteIterateForeignScan
DEBUG:  sqlite_fdw : sqliteIterateForeignScan
DEBUG:  sqlite_fdw : sqliteIterateForeignScan
DEBUG:  sqlite_fdw : sqliteEndForeignScan
DEBUG:  xact_callback 5
 a  |  b  
----+-----
  5 | foo
 15 | bar
(2 rows)

INSERT INTO test001 SELECT * FROM test001;
DEBUG:  sqlite_fdw : sqliteGetForeignRelSize
DEBUG:  sqlite_fdw : sqliteGetForeignPaths
DEBUG:  sqlite_fdw : sqliteGetForeignUpperPaths
DEBUG:  sqlite_fdw : sqliteGetForeignPlan
DEBUG:  sqlite_fdw : sqlite_fdw_handler
DEBUG:  sqlite_fdw : sqlitePlanForeignModify
DEBUG:  sqlite_fdw : sqliteBeginForeignScan
DEBUG:  sqlite_fdw : sqlite_prepare_wrapper SELECT "a", "b" FROM main."test001" 

DEBUG:   sqlite_fdw : sqliteBeginForeignModify
DEBUG:  sqlite_fdw : sqlite_prepare_wrapper INSERT INTO main."test001"("a", "b") VALUES (?, ?)

DEBUG:  sqlite_fdw : sqliteIterateForeignScan
DEBUG:  sqlite_fdw : sqliteExecForeignInsert
DEBUG:  sqlite_fdw : sqliteIterateForeignScan
DEBUG:  sqlite_fdw : sqliteExecForeignInsert
DEBUG:  sqlite_fdw : sqliteIterateForeignScan
DEBUG:  sqlite_fdw : sqliteExecForeignInsert
DEBUG:  sqlite_fdw : sqliteIterateForeignScan
DEBUG:  sqlite_fdw : sqliteExecForeignInsert
DEBUG:  sqlite_fdw : sqliteIterateForeignScan
DEBUG:  sqlite_fdw : sqliteExecForeignInsert
DEBUG:  sqlite_fdw : sqliteIterateForeignScan
DEBUG:  sqlite_fdw : sqliteExecForeignInsert
DEBUG:  sqlite_fdw : sqliteIterateForeignScan
DEBUG:  sqlite_fdw : sqliteExecForeignInsert
DEBUG:  sqlite_fdw : sqliteIterateForeignScan
DEBUG:  sqlite_fdw : sqliteExecForeignInsert
DEBUG:  sqlite_fdw : sqliteIterateForeignScan
DEBUG:  sqlite_fdw : sqliteExecForeignInsert
...
DEBUG:  sqlite_fdw : sqliteIterateForeignScan
DEBUG:  sqlite_fdw : sqliteExecForeignInsert
...

The error occurred when executing "INSERT by SELECT" command. There is an infinite loop between sqliteIterateForeignScan and sqliteExecForeignInsert functions.

Please help me to check the above issue.

If you have any inquiries, please let me know.

mochizk commented 5 years ago

Thanks for the report. I committed fix. Could you check?

mochizk commented 5 years ago

Sorry, could you check the latest?

thongpvn commented 5 years ago

Thanks for your commit. The error is fixed.