Add support for pragma journal_mode and synchronous

[sinelinea]

Introduction

Dear maintainers, I would like to contribute to the project by adding the support to tune the journal_mode and synchronous settings. Such parameters (https://www.sqlite.org/pragma.html#pragma_journal_mode, https://www.sqlite.org/pragma.html#pragma_synchronous) control the rollback journal and some aspects of the syncing process. This allows users to find the right balance between robustness against crashes and performances: for example, they can decide to completely disable journaling to improve response times. Further optimisations could be introduced, in the future, by allowing for tuning additional parameters [2].

Technical details

Users can tune journal_mode and synchronous options of the server, selecting the modalities that they want. At present, such changes will be applied only to new connections (core changes in sqlite_make_new_connection), but if you deem this feature useful, I can add the support to change settings of existing connections.

Accepted values (same ones assumed in the official documentation):

• journal_mode: 'delete', 'truncate', 'persist', 'memory', 'wal', 'off'.
• synchronous: 'off', 'normal', 'full', 'extra'.

Tests

I have a collection of manual tests to show the correct behaviour of the changes. However, at present I do not know how to integrate them. I would be glad to add them if you could provide information on the following points:

• the exact parameters to run test.sh, mentioned in the README.md.
• the exact positions where tests must be added in terms of expected values and results.
• the location of the test cases referenced in sql scripts (through labels) .

References

Some references to case studies:

1. https://blog.devart.com/increasing-sqlite-performance.html
2. https://avi.im/blag/2021/fast-sqlite-inserts/

Best regards, Francesco

[mkgrgis]

Hello, @sinelinea ! I think you'll deal with @t-kataym. I also tried to write some PR here and can help. In C language applications for DBs tests are more important and harder than C-code. There is no detailed documentation about testing if you have no experience. You can read something about testing environment in README.md from https://github.com/pgspider/sqlite_fdw/pull/60.

• Please add about new options in README.md
• Please add a tests for your new options to common SQLite scripts and PostgreSQL FDW testing scripts for your version or PostgreSQL 15 if available.

My tests from https://github.com/pgspider/sqlite_fdw/pull/59/files yet isn't appreciated by maintainers, but you can consider this minimum of changes.

[sinelinea]

Hello @mkgrgis, thanks a lot, I will have a look at the files that you referenced and I'll come back with some proposals for tests.

Best regards, Francesco

[t-kataym]

@sinelinea Thank you for your contribution.

At present, such changes will be applied only to new connections (core changes in sqlite_make_new_connection), but if you deem this feature useful, I can add the support to change settings of existing connections.

If user executes ALTER SERVER for changing a foreign server option, SQLite FDW has already the logic to re-create SQLite connection. It is done by sqlitefdw_inval_callback() which marks the connection as invalid in order to be re-created later.
So if you modify sqlite_make_new_connection() to support new options, because it will affect to not only new connection but also an existing connection, you don't need to distinguish them.

I would be glad to add them if you could provide information on the following points: Our project member will reply to you later.

[mkgrgis]

@sinelinea , I think PRAGMA TEMP_STORE also can be interesting for sqlite_fdw, but in separate PR. It's also problem of PRs from beginners, especially for my PRs: "one data behaviour change = one PR".

[jopoly]

Hello @sinelinea,

I would like to provide some information.

the exact parameters to run test.sh, mentioned in the README.md.

We can execute test by test.sh without any parameter. We just need to use command: ./test.sh

the exact positions where tests must be added in terms of expected values and results.

SQLite FDW supports PostgreSQL versions 11.17, 12.12, 13.8, 14.5, and 15.0. Therefore, you can create expectation files in the directories "expected/[versions]" (eg: expected/15.0/journal_mode_synchronous.out), then put expectations into them. When executing test, the result file will be created (eg: results/15.0/journal_mode_synchronous.out) and compared with the expectation file automatically.

the location of the test cases referenced in sql scripts (through labels).

You can create sql files in the directories "sql/[versions]" (eg: sql/15.0/journal_mode_synchronous.sql), then put sql scripts into them.

After creating sql and expectation files as above example. You need to update test.sh as below:

Could you please share some commands that you use to execute manual tests?

[mkgrgis]

Hello, @jopoly ! Can you review my changes in test files in https://github.com/pgspider/sqlite_fdw/pull/59/files ? Was it wrong? I have added new test cases directly to sqlite_fdw.sql.

[sinelinea]

Hello @jopoly , thanks for your explanation. I have pushed some further commits with a scaffold for tests (for v.15.0) based on the instructions you gave me, but I encountered some problems.

1. This is what I get when running test.sh:

   root@0bcffa6d0d3c:/fdw/sqlite_fdw# ./test.sh
   Makefile:47: /contrib/contrib-global.mk: No such file or directory
   make: *** No rule to make target '/contrib/contrib-global.mk'.  Stop.
   Makefile:47: /contrib/contrib-global.mk: No such file or directory
   make: *** No rule to make target '/contrib/contrib-global.mk'.  Stop.
   Makefile:47: /contrib/contrib-global.mk: No such file or directory
   make: *** No rule to make target '/contrib/contrib-global.mk'.  Stop.

2. Thanks to @mkgrgis 's suggestions, I managed to run the regression tests, but some of them fail (even without adding mine):

   
   root@0bcffa6d0d3c:/fdw/sqlite_fdw# make installcheck USE_PGXS=1
   echo "+++ regress install-check in  +++" && /usr/lib/postgresql/15/lib/pgxs/src/makefiles/../../src/test/regress/pg_regress --inputdir=./ --bindir='/usr/lib/postgresql/15/bin'    --dbname=contrib_regression 15.0/journal_mode_synchronous 15.0/extra/sqlite_fdw_post 15.0/extra/float4 15.0/extra/float8 15.0/extra/int4 15.0/extra/int8 15.0/extra/numeric 15.0/extra/join 15.0/extra/limit 15.0/extra/aggregates 15.0/extra/prepare 15.0/extra/select_having 15.0/extra/select 15.0/extra/insert 15.0/extra/update 15.0/extra/timestamp 15.0/sqlite_fdw 15.0/type 15.0/aggregate 15.0/selectfunc
   +++ regress install-check in  +++
   (using postmaster on Unix socket, default port)
   ============== dropping database "contrib_regression" ==============
   SET
   DROP DATABASE
   ============== creating database "contrib_regression" ==============
   CREATE DATABASE
   ALTER DATABASE
   ALTER DATABASE
   ALTER DATABASE
   ALTER DATABASE
   ALTER DATABASE
   ALTER DATABASE
   ============== running regression test queries        ==============
   test 15.0/journal_mode_synchronous ... ok          188 ms
   test 15.0/extra/sqlite_fdw_post   ... ok         4050 ms
   test 15.0/extra/float4            ... ok          600 ms
   test 15.0/extra/float8            ... ok          942 ms
   test 15.0/extra/int4              ... ok          350 ms
   test 15.0/extra/int8              ... ok          818 ms
   test 15.0/extra/numeric           ... ok         7034 ms
   test 15.0/extra/join              ... FAILED     4709 ms
   test 15.0/extra/limit             ... ok          523 ms
   test 15.0/extra/aggregates        ... FAILED    10988 ms
   test 15.0/extra/prepare           ... ok          168 ms
   test 15.0/extra/select_having     ... ok          104 ms
   test 15.0/extra/select            ... ok          239 ms
   test 15.0/extra/insert            ... ok          360 ms
   test 15.0/extra/update            ... ok          430 ms
   test 15.0/extra/timestamp         ... ok          163 ms
   test 15.0/sqlite_fdw              ... FAILED      595 ms
   test 15.0/type                    ... ok          234 ms
   test 15.0/aggregate               ... ok          108 ms
   test 15.0/selectfunc              ... FAILED      296 ms

======================= 4 of 20 tests failed.

The differences that caused some tests to fail can be viewed in the file "/fdw/sqlite_fdw/regression.diffs". A copy of the test summary that you see above is saved in the file "/fdw/sqlite_fdw/regression.out".

I have uploaded [regression.diffs.txt](https://github.com/pgspider/sqlite_fdw/files/10954708/regression.diffs.txt), hoping that it helps you figuring out what is happening.

4. I have modified `test.sh` to make temporary databases writable.
5. @mkgrgis I have described the optional commands in `README.md`. Thanks for your references. Yes I think we could add additional parameters that influence the performances when inserting thousands of records.

6. @t-kataym, thanks for elaborating on changes for existing connections. It means that they are not required then.

Please just let me know how you want to progress on this PR.

[mkgrgis]

@sinelinea, there is additional info for you. You can attach a long diff as a file, not as log text with more than 50-100 lines. Just drag and drop to message window. There is example in https://github.com/ibarwick/firebird_fdw/issues/21.

[mkgrgis]

@jopoly, does you run tests in default configuration with rights of postgres OS user? Look like current test instruction without my additions is suitable only for this user. Please note, postgres user isn't usual for C-language development but very simple for any tests in PostgreSQL.

[mkgrgis]

@sinelinea , I have some notes about current version of your README.md.

• Please add info about default values like in README.md from https://github.com/pgspider/sqlite_fdw/pull/60.
• Please enumerate all possible values for the new options in README.md like in "tabletype" from https://github.com/mkgrgis/redis_cluster_fdw/tree/patch-1#create-foreign-table-options. Let's imagine there will be added or removed a value in SQLite documentation. Hence README.md will be inadequate. Also all existed outer URLs gives only additional information or proofs for sqlite_fdw using in opposition to outer URL with necessary info in your README.md. Please don't forget, documentation must be usable without internet connection on embedded, isolated or local systems.

[sinelinea]

Hello @mkgrgis, I have added the required info. @mkgrgis , @jopoly, @t-kataym, in your opinion is there anything missing?

Best regards, Francesco

[mkgrgis]

@sinelinea, thanks! I have noticed only two little problems with align "The aforementioned semantics refers....". Attentive to the details reviews around testing and C language code is not for me, I am C beginner. For me your C code works good. Please note, stability of your contribution determined only by test coverage.

[t-kataym]

@sinelinea , We are checking it. @jopoly will make comment for you.

[jopoly]

Hello @sinelinea ,

I'm sorry for not getting back to you sooner.

I want to give some information about your problem:

1. This is what I get when running test.sh:

In test.sh, the make and make check command is used without USE_PGXS=1. Therefore, to use this file we need to put sqlite_fdw directory in Postgres source tree (eg: postgresql-15.0/contrib).

2. Thanks to @mkgrgis 's suggestions, I managed to run the regression tests, but some of them fail (even without adding mine):

I tried to reproduce this problem but it didn't happen. I put sqlite_fdw outside of Postgres source tree, then used make and make check command with USE_PGXS=1. And the user I used is an operating system user.

I tried to use root user like you, but I could not use the shell command "initdb" or "pg_ctl" with it. So I couldn't reproduce with root user.

In my understanding, we cannot start a server with root user. https://www.postgresql.org/docs/current/app-initdb.html https://stackoverflow.com/questions/28311825/root-execution-of-the-postgresql-server-is-not-permitted

Could you provide more steps when you install Postgres? What commands did you use to initiate database and start postgres server?

[mkgrgis]

@jopoly, for me is usually to compile sqlite_fdw under some user without sudo or postgres groups. Under root or some user from sudo group I execute only make install USE_PGXS=1. Normally I don't use root user around of sqlite_fdw directly at all. During testing test.sh also have no postgres or sudo OS user group rights. In my case no need to restart PostgreSQL after make install USE_PGXS=1, new code is available for a new connection. I hope @sinelinea will write details for us.

[sinelinea]

Hello @jopoly, sorry for the delay, I have been quite busy during the last days. Thanks for your message and clarification.

I am running PostgreSQL in a Docker container, using the official image (https://hub.docker.com/_/postgres). This means that I do not compile it from scratch, I only take care of installing the dependencies and configuring postgresql.conf before starting the server (through a non-root user).

I tested my changes by compiling sqlite_fdw through make install USE_PGXS=1, as reported in the Readme.

For what concerns test.sh, I followed your instructions and re-run the tests after moving sqlite_fdw to postgresql-15.0/contrib; this is what I got when executing the script with a non-root user (the same one used to run the server):

The test data items are generated correctly:

Do I need to configure and compile PostgreSQL?

Best regards, Francesco

[khieuvm]

Hello @sinelinea, Thank you for describing your environment and your action.

I tried to install your environment (using the official image (https://hub.docker.com/_/postgres)), then I also can not compile sqlite_fdw same like you. And I found this official image does not install postgresql-server-dev-XX for developers. So I install postgresql-server-dev-15 and I can build sqlite_fdw:

export PG_CONFIG=/usr/bin/pg_config.libpq-dev
export USE_PGXS=1
make

Do I need to configure and compile PostgreSQL?

So you do not need to do this.

To test sqlite_fdw, you need to change PostgreSQL version at sql and expected folders (example mv 15.0 15.2) Because you do not use PostgreSQL source code, you can not use make check to test, you must start PostgreSQL server and use make installcheck USE_PGXS=1. If you use test.sh script, please edit make check to make installcheck. And do not forget to export environment variable above.

Hope my solution can help you.

Best regards, KhieuVM

[sinelinea]

Thanks a lot @khieuvm, I have followed your instructions but the results are still the same:

============== creating database "contrib_regression" ==============
CREATE DATABASE
ALTER DATABASE
ALTER DATABASE
ALTER DATABASE
ALTER DATABASE
ALTER DATABASE
ALTER DATABASE
============== running regression test queries        ==============
test 15.0/journal_mode_synchronous ... ok          149 ms
test 15.0/extra/sqlite_fdw_post   ... ok         3790 ms
test 15.0/extra/float4            ... ok          470 ms
test 15.0/extra/float8            ... ok          824 ms
test 15.0/extra/int4              ... ok          296 ms
test 15.0/extra/int8              ... ok          488 ms
test 15.0/extra/numeric           ... ok         3206 ms
test 15.0/extra/join              ... FAILED     3299 ms
test 15.0/extra/limit             ... ok          296 ms
test 15.0/extra/aggregates        ... FAILED     7818 ms
test 15.0/extra/prepare           ... ok          126 ms
test 15.0/extra/select_having     ... ok           80 ms
test 15.0/extra/select            ... ok          214 ms
test 15.0/extra/insert            ... ok          266 ms
test 15.0/extra/update            ... ok          417 ms
test 15.0/extra/timestamp         ... ok          103 ms
test 15.0/sqlite_fdw              ... FAILED      478 ms
test 15.0/type                    ... ok          172 ms
test 15.0/aggregate               ... ok           87 ms
test 15.0/selectfunc              ... FAILED      206 ms

It seems that all errors are associated with function mod:

cat /fdw/sqlite_fdw/regression.diffs | grep error
+ERROR:  SQL error during prepare: no such function: mod SELECT `c1`, `c3`, `c6` FROM main."case_exp" WHERE ((`c1` > CASE mod(`c1`, 4) WHEN 0 THEN 1 WHEN 2 THEN 50 ELSE 100 END))
+ERROR:  SQL error during prepare: no such function: mod SELECT `value2`, `value4` FROM main."s3" WHERE ((mod(`value2`, 2) <> 1))
+ERROR:  SQL error during prepare: no such function: mod SELECT sum(`value3`), mod(sum(`value2`), 2) FROM main."s3"
+ERROR:  SQL error during prepare: no such function: mod SELECT `value1`, `value2` FROM main."s3" ORDER BY mod((1 - `value2`), 2) ASC NULLS LAST
+ERROR:  SQL error during prepare: no such function: mod SELECT `value1`, `value2` FROM main."s3" ORDER BY mod((1 - `value2`), 2) ASC NULLS LAST, `value1` ASC NULLS LAST
+ERROR:  SQL error during prepare: no such function: mod SELECT `value1`, `value2` FROM main."s3" ORDER BY `value1` ASC NULLS LAST, mod((1 - `value2`), 2) ASC NULLS LAST
+ERROR:  SQL error during prepare: no such function: mod SELECT `value1`, mod((1 - `value2`), 2) FROM main."s3" GROUP BY 1, 2
+ERROR:  SQL error during prepare: no such function: mod SELECT `value1`, mod((1 - `value2`), 2) FROM main."s3" GROUP BY 2, 1
+ERROR:  SQL error during prepare: no such function: mod SELECT `value1`, mod((1 - `value2`), 2) FROM main."s3" GROUP BY 1, 2
+ERROR:  SQL error during prepare: no such function: mod SELECT `value1`, mod((1 - `value2`), 2) FROM main."s3" GROUP BY 1, 2 HAVING ((avg(`value1`) > 0))
+ERROR:  SQL error during prepare: no such function: mod SELECT `value1`, mod((1 - `value2`), 2) FROM main."s3" WHERE ((`value1` > 1)) GROUP BY 1, 2

For example, this is one instruction that fails:

SELECT value1, mod((1-value2), 2) FROM s3 GROUP BY 1,2;
- value1 | mod
+ERROR:  SQL error during prepare: no such function: mod SELECT `value1`, mod((1 - `value2`), 2) FROM main."s3" GROUP BY 1, 2

Any idea why mod fails on that foreign table? I do not think such issue is related to my changes.

Thanks. Best regards, Francesco

[khieuvm]

Hello @sinelinea,

Any idea why mod fails on that foreign table? I do not think such issue is related to my changes.

I think this fails related to your SQLite, not your changes. The mod function of SQLite only used if SQLite is compiled using the -DSQLITE_ENABLE_MATH_FUNCTIONS (refer https://www.sqlite.org/lang_mathfunc.html) I think you can confirm this point by executing the query on SQLite database directly.

So could you tell me how do you build SQLite database?

Best regards, KhieuVM

[sinelinea]

Thanks @khieuvm, apparently the libsqlite3-dev I installed with my distro was compiled without -DSQLITE_ENABLE_MATH_FUNCTIONS. I have downloaded the autoconf amalgamation version, compiled and installed it with the correct flags:

SQLite version 3.41.2 2023-03-22 11:56:21
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> PRAGMA compile_options;
ATOMIC_INTRINSICS=1
COMPILER=gcc-10.2.1 20210110
DEFAULT_AUTOVACUUM
DEFAULT_CACHE_SIZE=-2000
DEFAULT_FILE_FORMAT=4
DEFAULT_JOURNAL_SIZE_LIMIT=-1
DEFAULT_MMAP_SIZE=0
DEFAULT_PAGE_SIZE=4096
DEFAULT_PCACHE_INITSZ=20
DEFAULT_RECURSIVE_TRIGGERS
DEFAULT_SECTOR_SIZE=4096
DEFAULT_SYNCHRONOUS=2
DEFAULT_WAL_AUTOCHECKPOINT=1000
DEFAULT_WAL_SYNCHRONOUS=2
DEFAULT_WORKER_THREADS=0
ENABLE_DBPAGE_VTAB
ENABLE_DBSTAT_VTAB
ENABLE_EXPLAIN_COMMENTS
ENABLE_FTS3
ENABLE_FTS4
ENABLE_FTS5
ENABLE_GEOPOLY
ENABLE_MATH_FUNCTIONS
ENABLE_RTREE
ENABLE_STMTVTAB
MALLOC_SOFT_LIMIT=1024
MAX_ATTACHED=10
MAX_COLUMN=2000
MAX_COMPOUND_SELECT=500
MAX_DEFAULT_PAGE_SIZE=8192
MAX_EXPR_DEPTH=1000
MAX_FUNCTION_ARG=127
MAX_LENGTH=1000000000
MAX_LIKE_PATTERN_LENGTH=50000
MAX_MMAP_SIZE=0x7fff0000
MAX_PAGE_COUNT=1073741823
MAX_PAGE_SIZE=65536
MAX_SQL_LENGTH=1000000000
MAX_TRIGGER_DEPTH=1000
MAX_VARIABLE_NUMBER=32766
MAX_VDBE_OP=250000000
MAX_WORKER_THREADS=8
MUTEX_PTHREADS
SYSTEM_MALLOC
TEMP_STORE=1
THREADSAFE=1

and now the method is supported:

sqlite>
sqlite> create table t(a int);
sqlite> insert into t values (10);
sqlite> select mod(a,3) from t;
1.0
sqlite>

but I still get exactly the same errors when running the regression tests. Any suggestion?

Best regards, Francesco

[khieuvm]

Hello @sinelinea,

I investigated your issue and found your problem happen because of the library of sqlite3. When installing sqlite3, it will create some libraries: libsqlite3.xx. If you use make install, they will copy these libraries to /usr/local/lib, replacing the old library of sqlite3 if exist.

But this machine has default library folder is /usr/lib/x86_64-linux-gnu, and when making sqlite_fdw, it will find library in this folder, but the library of sqlite3 in this folder is old. So sqlite_fdw use the wrong library of sqlite3. You can use ldd command to verify it:

ldd sqlite_fdw.so linux-vdso.so.1 (0x00007ffd3798f000) libsqlite3.so.0 => /usr/lib/x86_64-linux-gnu/libsqlite3.so.0 (0x00007fb9aa491000) libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007fb9aa2bc000) libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x00007fb9aa178000) libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x00007fb9aa172000) libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0 (0x00007fb9aa150000) /lib64/ld-linux-x86-64.so.2 (0x00007fb9aa5f9000)

To resolve this issue, you can replace the old library of sqlite3 in /usr/lib/x86_64-linux-gnu by the new library of sqlite3 (/usr/local/lib or folder .libs of build source).

Best regards, KhieuVM

[mkgrgis]

I investigated your issue and found your problem happen because of the library of sqlite3...

Its very interesting for documentation about testing. We have so much notices about testing, that separate TESTING.md can be usefully. What do you think about this, @t-kataym ?

[t-kataym]

@mkgrgis, Because such information is for not only testing, could you describe it in README.md?

[mkgrgis]

@mkgrgis, Because such information is for not only testing, could you describe it in README.md?

Yes. My draft about testing in README.md from https://github.com/pgspider/sqlite_fdw/pull/60 isn't very good yet, but I am thinking about structuring information about testing from my PRs and this PR of @sinelinea. Its very interesting trying to reproduce test environment, but both I and @sinelinea haven't full success now.

[mkgrgis]

New draft for documentation about testing is ready.

[mkgrgis]

@sinelinea , it seems testing framework for sqlite_fdw based only on make check with PostgreSQL version compiled directly from source code on testing environment computer, see very interesting answer of @bichht0608 https://github.com/pgspider/sqlite_fdw/pull/59#issuecomment-1500131514

[sinelinea]

Thanks @khieuvm, I confirm that the problem was the one that you mentioned, which was solved after replacing the libraries. However, now the same tests fail due to changes in the query plans: regression.diffs.txt. Do we have the version number and the compilation options of sqlite3 used to generate the expected results?

Thanks @mkgrgis, I think we are almost there.

Update: from what I can see from the release history (https://www.sqlite.org/changes.html), the first version introducing support for math functions was 3.35.0. The default configuration generated by configure includes the following compilation options:

ENABLE_DBSTAT_VTAB
ENABLE_FTS4
ENABLE_FTS5
ENABLE_GEOPOLY
ENABLE_JSON1
ENABLE_MATH_FUNCTIONS
ENABLE_RTREE
ENABLE_STMTVTAB
THREADSAFE=1

Moreover, also with such configuration the tests fail due to different query plans; probably this suggests that the version of sqlite3 used to produce the expected results was using a different configuration, not the one generated by configure.

Best regards, Francesco

[khieuvm]

Hello @sinelinea,

Do we have the version number and the compilation options of sqlite3 used to generate the expected results?

I use sqlite3 version 3.41.2 with the default configuration and have results are same as expected:

cd sqlite-autoconf-3410200
 ./configure 
 make
make install

Currently, I am using your environment (https://github.com/pgspider/sqlite_fdw/pull/67#issuecomment-1483832981) but I do not meet your issue. About your regression.diffs.txt, I think this difference is related to changes in source code. So could you share your patch with me?

Best regards, KhieuVM

[mkgrgis]

@khieuvm , can You confirm that current testing system in mainly incompatible with PosgteSQL OS packages such as deb or rpm for Ubuntu or Fedora. Hence git command manipulations with this branches according list from https://github.com/pgspider/sqlite_fdw/tree/master/sql is only normal reference testing method ? Also this means port 5432 must be used by PostgreSQL from some testing git branch version, not from OS ?

[khieuvm]

Hello @mkgrgis,

Can you confirm that current testing system in mainly incompatible with PosgteSQL OS packages such as deb or rpm for Ubuntu or Fedora.

The current testing system is compatible with PostgreSQL packages, but we need to install dev package to build fdw (https://github.com/pgspider/sqlite_fdw/pull/67#issuecomment-1486438133). So fdw's test do not related to PostgreSQL port and OS.

Best regards, KhieuVM

[mkgrgis]

Thanks, @KhieuVM! I am near reproducing testing system now. Really testing from PostgreSQL source tree don't touch PostgreSQL databases in OS package environment. Maybe this system based on unix sockets instead TCP/IP port.

Can you confirm my steps? I think @sinelinea is frustrated by the lack of description of testing environment setup.

1. Get and unpack PostgreSQL source stable trees for different versions from sqlite_fdw tests from https://www.postgresql.org/ftp/source/
2. Add (copy) to every contrib directory sqlite_fdw
3. From every sqlite_fdw run without any parameters make && make install and test.sh
4. Read test results for every version.

[khieuvm]

Hello @mkgrgis,

Your steps are almost correct, but you need to build PostgreSQL after unpacking PostgreSQL source (configure, make).

Best regards, KhieuVM

[mkgrgis]

Many thanks to @khieuvm ! I have got successfully script for multi-versional testing system for sqlite_fdw.

Please try to reproduce @sinelinea.

Please verify, @t-kataym, if my testing environment conception is OK.

My base directory was /tmp/テスト, almost 1.1 Gb of disk space was used. 25 minutes for first loading, about 8 min for all sqlite_fdw tests for many PostgreSQL versions.

Testing system from PostgreSQL source code tree don't touch PostgreSQL databases, existing in your OS and maintained by OS packages.

1. Install packages for PostgreSQL build from source code

sudo apt-get install build-essential libreadline-dev zlib1g-dev flex bison libxml2-dev libxslt-dev libssl-dev libxml2-utils xsltproc ccache;

or

sudo yum install -y bison-devel readline-devel zlib-devel openssl-devel wget ccache
sudo yum groupinstall -y 'Development Tools'

see https://wiki.postgresql.org/wiki/Compile_and_Install_from_source_code

2. Clone sqlite_fdw. Use -b for other branches. git clone git@github.com:pgspider/sqlite_fdw.git;

In our case git clone git@github.com:sinelinea/sqlite_fdw.git -b journal_mode_synchronous;

3. Make directory for multiversional PostgreSQL source code mkdir "PostgreSQL source" && cd "PostgreSQL source" && echo 'OK pg dir';

Ensure there is OK pg dir message.

4. Get and read list of PostgreSQL versions

   ver=$(ls -1 ../sqlite_fdw/expected/);
   echo "$ver";

5. Get individual PostgreSQL source trees for different versions. Here and later we use notation of version branches from https://git.postgresql.org/gitweb/?p=postgresql.git;a=summary, hence we should replace . to _ and beta to _.

echo "$ver" | while read ver_curs; do ver_curs=$(echo "$ver_curs"| tr '.' '_' | sed -e "s/beta/\_/g"); mkdir "REL_$ver_curs"; cd "REL_$ver_curs"; git clone https://git.postgresql.org/git/postgresql.git -b "REL_$ver_curs"; cd ..; done;

6. Configure and make PostgreSQL sources

echo "$ver" | while read ver_curs; do ver_curs=$(echo "$ver_curs"| tr '.' '_' | sed -e "s/beta/\_/g"); cd "REL_$ver_curs/postgresql"; ./configure; make; cd ../..; done;

7. Run internal checks for PostgreSQL. Please ensure all tests is successfuly. If yes, source code of a PostgreSQL version is suitable for any FDW integration. Output is long.

echo "$ver" | while read ver_curs; do ver_curs=$(echo "$ver_curs"| tr '.' '_' | sed -e "s/beta/\_/g"); cd "REL_$ver_curs/postgresql"; make check; cd ../..; done;

8. Copy sqlite_fdw to contrib directory of all PostgreSQL versions.

echo "$ver" | while read ver_curs; do ver_curs=$(echo "$ver_curs"| tr '.' '_' | sed -e "s/beta/\_/g"); cd "REL_$ver_curs/postgresql"; cp -r -v ../../../sqlite_fdw contrib; cd ../..; done;

9. Run sqlite_fdw regression tests.

echo "$ver" | while read ver_curs; do ver_curs=$(echo "$ver_curs"| tr '.' '_' | sed -e "s/beta/\_/g"); cd "REL_$ver_curs/postgresql/contrib/sqlite_fdw"; ./test.sh; cd ../../../..; done;

10. Repeat No 9 until you have full success. Don't forget this note about SQLite source code for testing.

Also you can delete sqlite_fdw copies and try to copy new version of your source again, see No 8.

Delete sqlite_fdw copies in PostgreSQL source code tress echo "$ver" | while read ver_curs; do ver_curs=$(echo "$ver_curs"| tr '.' '_' | sed -e "s/beta/\_/g"); cd "REL_$ver_curs/postgresql"; rm -v -r contrib/sqlite_fdw; cd ../..; done;

[mkgrgis]

@sinelinea , now I have successfully experience with testing for sqlite_fdw thanks to @t-kataym , @jopoly , @khieuvm and @bichht0608 .

Could you please give me some testcases for journal_mode option and also other test cases for values of synchronous option?

Unfortunately we have only DROP/CREATE, EXPLAIN or SELECT/UPDATE/DELETE/INSERT ways to detect proper behaviour. First you should decide is this PR testable with our testing framework or not.

Note: In https://github.com/pgspider/sqlite_fdw/pull/67/files#diff-cb9971d99f6d8a171605e787d322b3678badf4191005e33c4216701c9031a114 there is only tests for acceptable option values, but not for some behaviour. I don't know is this enough.

[mkgrgis]

@sinelinea , could you please resolve merge conflicts for this PR? In my opinion your PR have no possible EXPLAIN VERBOSE testcases and formally checking of option values is enough.

[sinelinea]

Hello @mkgrgis, thanks for all the messages. Sorry I have no time now to work on this, I will close the pull request and come back in the future with a new one.

Best regards, Francesco

[mkgrgis]

@sinelinea , I have published some scripts for automatically multiversional testing environment getting. Have you got a time for testing ? See my new PR https://github.com/pgspider/sqlite_fdw/pull/89 . Maybe this can make your contributig easier in future.

@khieuvm and @jopoly , can you ask @t-kataym about my new scripting PR?

[mkgrgis]

Hello, @sinelinea ! Your contribution have restored by me with fixed tests in https://github.com/mkgrgis/sqlite_fdw/tree/journal_mode . Also there is automated miltiversional testing environment getting scripts, see https://github.com/pgspider/fdw_testing_scripts .

[sinelinea]

Hello @mkgrgis, thanks for the effort but I would like have my own request merged at some point, with the traceability info about the commits from my side. I can work on this during Christmas period.

Best regards, Francesco

[mkgrgis]

@sinelinea , no problem. This is not my PR, this is only my PoC with successfully tests. You can use my modifications in your PRs. Now we have better tests support than in time of this your PR, see please also https://github.com/pgspider/sqlite_fdw/pull/92 . It will be interesting for me to see your PRs with more useful PRAGMAs and tests. Thanks for reply!

Best regards, @mkgrgis

[mkgrgis]

@sinelinea , something about a new release https://github.com/pgspider/sqlite_fdw/pull/92#issuecomment-1869996136 I think you have a chance for complex PR.

[mkgrgis]

Hello @sinelinea ! Your contribution was carefully restored with tests against the latest master branch in https://github.com/mkgrgis/sqlite_fdw/tree/journal_mode Do you want to send similar PR to pgspider team or add something? Fell free to use my branch.

[mkgrgis]

Hello, @sinelinea ! Your contribution is refreshed to new git HEAD in https://github.com/mkgrgis/sqlite_fdw/tree/journal_mode , will your create and support your own PR based on my restoring? If no, I'll give to maintainers my PR with comments about Your significant contribution in code and tests.