search

tds-fdw / tds_fdw

A PostgreSQL foreign data wrapper to connect to TDS databases (Sybase and Microsoft SQL Server)
Other
377 stars 101 forks source link

Add PostgreSQL 12 support w/ new types+ExecTuple func #213

Closed jcarnu closed 5 years ago

jcarnu commented 5 years ago

This patch allows use of tds_fdw with PG12. This implies #211 observations :

This patch was tested against PG 12 RC1 on a centos/7 with pgdg source. It may also still work on older version of PG as all changes were protected by preoprocessor instructions.

jenkins-juliogonzalez commented 5 years ago

Can one of the admins verify this patch?

GeoffMontee commented 5 years ago

Test this, please

jenkins-juliogonzalez commented 5 years ago

Test PASSed.

jcarnu commented 5 years ago

Well, is there a way to create CI with centos7 and PG 12rc1 ? Do you need help for this ?

juliogonzalez commented 5 years ago

@jcarnu I will do it for Ubuntu as well.

The dockerfiles are at https://github.com/tds-fdw/ci-setup, but I also need to create the base images that include PostgreSQL and I have at https://github.com/juliogonzalez/docker-postgresql (and create all the images at DockerHub), change the control files I have at my Jenkins, and finally add the new PostgreSQL versions at the main README.

It should be ready in a few hours. I will rerun tests as soon as everything is in place.

jcarnu commented 5 years ago

@juliogonzalez great news :+1: Thanks for your quick reaction

juliogonzalez commented 5 years ago

Ok, we now have PostgreSQL 12 images and all the stuff ready. Let's give it a try! :-)

Retest this, please

jenkins-juliogonzalez commented 5 years ago

Test FAILed.

juliogonzalez commented 5 years ago

Retest this, please.

jenkins-juliogonzalez commented 5 years ago

Test FAILed.

juliogonzalez commented 5 years ago

Ok, so it seems we now have a genuine failure at this test: https://github.com/tds-fdw/tds_fdw/blob/master/tests/tests/postgresql/031_query_option_column_match_enabled.sql

For both Ubuntu 16.04 and CentOS6.

It seems we have a segfault.

This is from a the Ubuntu 16.04 container, postgresql.log:

2019-10-02 02:26:07.014 UTC [73] LOG:  starting PostgreSQL 12rc1 (Ubuntu 12~rc1-1.pgdg16.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.11) 5.4.0 20160609, 64-bit
2019-10-02 02:26:07.015 UTC [73] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2019-10-02 02:26:07.015 UTC [73] LOG:  could not bind IPv6 address "::1": Cannot assign requested address
2019-10-02 02:26:07.015 UTC [73] HINT:  Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
2019-10-02 02:26:07.139 UTC [73] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2019-10-02 02:26:07.352 UTC [74] LOG:  database system was shut down at 2019-10-02 02:26:06 UTC
2019-10-02 02:26:07.437 UTC [73] LOG:  database system is ready to accept connections
2019-10-02 02:27:13.036 UTC [301] pgtest@tds_fdw WARNING:  Table definition mismatch: Could not match local column id with column from foreign table
2019-10-02 02:27:18.864 UTC [301] pgtest@tds_fdw WARNING:  Table definition mismatch: Could not match local column id with column from foreign table
2019-10-02 02:27:24.738 UTC [301] pgtest@tds_fdw WARNING:  Table definition mismatch: Could not match local column id with column from foreign table
2019-10-02 02:27:30.561 UTC [301] pgtest@tds_fdw WARNING:  Table definition mismatch: Could not match local column id with column from foreign table
2019-10-02 02:27:36.322 UTC [301] pgtest@tds_fdw WARNING:  Table definition mismatch: Could not match local column id with column from foreign table
2019-10-02 02:27:42.168 UTC [301] pgtest@tds_fdw WARNING:  Table definition mismatch: Could not match local column id with column from foreign table
2019-10-02 02:27:48.043 UTC [301] pgtest@tds_fdw WARNING:  Table definition mismatch: Could not match local column id with column from foreign table
2019-10-02 02:27:53.822 UTC [301] pgtest@tds_fdw WARNING:  Table definition mismatch: Could not match local column id with column from foreign table
2019-10-02 02:28:46.719 UTC [301] pgtest@tds_fdw WARNING:  Table definition mismatch: Could not match local column id with column from foreign table
2019-10-02 02:28:58.348 UTC [301] pgtest@tds_fdw WARNING:  Table definition mismatch: Could not match local column id with column from foreign table
2019-10-02 02:29:00.584 UTC [73] LOG:  server process (PID 301) was terminated by signal 11: Segmentation fault
2019-10-02 02:29:00.584 UTC [73] DETAIL:  Failed process was running: DROP FOREIGN TABLE IF EXISTS postgresql12testing_ubuntu1604_test.query_option_column_match_enabled;

        CREATE FOREIGN TABLE postgresql12testing_ubuntu1604_test.query_option_column_match_enabled (
                id int,
                data varchar(50),
                owner varchar(50)
        )
                SERVER mssql_svr
                OPTIONS (query 'SELECT * FROM postgresql12testing_ubuntu1604_test.query_option WHERE owner = ''geoff''', row_estimate_method 'showplan_all', match_column_names '1');

        SELECT * FROM postgresql12testing_ubuntu1604_test.query_option_column_match_enabled;
        WITH count AS (
                SELECT COUNT(*) as count FROM postgresql12testing_ubuntu1604_test.query_option_column_match_enabled
        )
        SELECT (count = 1) FROM count;
        WITH owners AS (
                SELECT DISTINCT owner FROM postgresql12testing_ubuntu1604_test.query_option_column_match_enabled
        )
        SELECT (owner = 'geoff') FROM owners;

2019-10-02 02:29:00.584 UTC [73] LOG:  terminating any other active server processes
2019-10-02 02:29:00.591 UTC [80] WARNING:  terminating connection because of crash of another server process
2019-10-02 02:29:00.591 UTC [80] 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.
2019-10-02 02:29:00.591 UTC [80] HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2019-10-02 02:29:00.610 UTC [73] LOG:  all server processes terminated; reinitializing
2019-10-02 02:29:00.851 UTC [305] LOG:  database system was interrupted; last known up at 2019-10-02 02:26:33 UTC
2019-10-02 02:29:02.102 UTC [305] LOG:  database system was not properly shut down; automatic recovery in progress
2019-10-02 02:29:02.188 UTC [305] LOG:  redo starts at 0/163DFB0
2019-10-02 02:29:02.248 UTC [305] LOG:  invalid record length at 0/16DE728: wanted 24, got 0
2019-10-02 02:29:02.248 UTC [305] LOG:  redo done at 0/16DE350
2019-10-02 02:29:02.248 UTC [305] LOG:  last completed transaction was at log time 2019-10-02 02:28:58.349849+00
2019-10-02 02:29:02.785 UTC [73] LOG:  database system is ready to accept connections

This is from dmesg:

283129.470601] postmaster[28295]: segfault at 0 ip 00007f79f28ef221 sp 00007fff7a174748 error 4 in libc-2.12.so[7f79f286e000+18b000]
[283129.474333] Code: 00 c3 f7 05 9d 20 31 00 04 00 00 00 74 07 48 8d 05 a4 ee 0b 00 c3 0f 1f 00 48 31 c0 89 f9 83 e1 3f 66 0f ef c0 83 f9 30 77 19 <f3> 0f 6f 0f 66 0f 74 c1 66 0f d7 d0 85 d2 75 7a 48 89 f8 48 83 e0
[283141.396402] postmaster[28364]: segfault at 0 ip 00007f24b2999746 sp 00007ffcf8e4fde8 error 4 in libc-2.23.so[7f24b290e000+1c0000]
[283141.400046] Code: ff ff ff 90 66 0f ef c0 66 0f ef c9 66 0f ef d2 66 0f ef db 48 89 f8 48 89 f9 48 81 e1 ff 0f 00 00 48 81 f9 cf 0f 00 00 77 6a <f3> 0f 6f 20 66 0f 74 e0 66 0f d7 d4 85 d2 74 04 0f bc c2 c3 48 83

And this from the CentOS6 container:

2019-10-02 02:25:56.139 UTC [34] LOG:  database system was shut down at 2019-10-01 19:22:23 UTC
2019-10-02 02:25:56.245 UTC [13] LOG:  database system is ready to accept connections
2019-10-02 02:25:56.568 UTC [13] LOG:  received fast shutdown request
2019-10-02 02:25:56.601 UTC [13] LOG:  aborting any active transactions
2019-10-02 02:25:56.604 UTC [13] LOG:  background worker "logical replication launcher" (PID 40) exited with exit code 1
2019-10-02 02:25:56.607 UTC [35] LOG:  shutting down
2019-10-02 02:25:58.131 UTC [13] LOG:  database system is shut down
2019-10-02 02:25:58.976 UTC [52] LOG:  database system was shut down at 2019-10-02 02:25:57 UTC
2019-10-02 02:25:59.094 UTC [50] LOG:  database system is ready to accept connections
2019-10-02 02:27:00.267 UTC [292] WARNING:  Table definition mismatch: Could not match local column id with column from foreign table
2019-10-02 02:27:06.085 UTC [292] WARNING:  Table definition mismatch: Could not match local column id with column from foreign table
2019-10-02 02:27:11.954 UTC [292] WARNING:  Table definition mismatch: Could not match local column id with column from foreign table
2019-10-02 02:27:17.833 UTC [292] WARNING:  Table definition mismatch: Could not match local column id with column from foreign table
2019-10-02 02:27:23.678 UTC [292] WARNING:  Table definition mismatch: Could not match local column id with column from foreign table
2019-10-02 02:27:29.520 UTC [292] WARNING:  Table definition mismatch: Could not match local column id with column from foreign table
2019-10-02 02:27:35.354 UTC [292] WARNING:  Table definition mismatch: Could not match local column id with column from foreign table
2019-10-02 02:27:41.187 UTC [292] WARNING:  Table definition mismatch: Could not match local column id with column from foreign table
2019-10-02 02:28:34.584 UTC [292] WARNING:  Table definition mismatch: Could not match local column id with column from foreign table
2019-10-02 02:28:46.438 UTC [292] WARNING:  Table definition mismatch: Could not match local column id with column from foreign table
2019-10-02 02:28:48.702 UTC [50] LOG:  server process (PID 292) was terminated by signal 11: Segmentation fault
2019-10-02 02:28:48.702 UTC [50] DETAIL:  Failed process was running: DROP FOREIGN TABLE IF EXISTS postgresql12testing_centos6_test.query_option_column_match_enabled;

        CREATE FOREIGN TABLE postgresql12testing_centos6_test.query_option_column_match_enabled (
                id int,
                data varchar(50),
                owner varchar(50)
        )
                SERVER mssql_svr
                OPTIONS (query 'SELECT * FROM postgresql12testing_centos6_test.query_option WHERE owner = ''geoff''', row_estimate_method 'showplan_all', match_column_names '1');

        SELECT * FROM postgresql12testing_centos6_test.query_option_column_match_enabled;
        WITH count AS (
                SELECT COUNT(*) as count FROM postgresql12testing_centos6_test.query_option_column_match_enabled
        )
        SELECT (count = 1) FROM count;
        WITH owners AS (
                SELECT DISTINCT owner FROM postgresql12testing_centos6_test.query_option_column_match_enabled
        )
        SELECT (owner = 'geoff') FROM owners;

2019-10-02 02:28:48.702 UTC [50] LOG:  terminating any other active server processes
2019-10-02 02:28:48.708 UTC [56] WARNING:  terminating connection because of crash of another server process
2019-10-02 02:28:48.708 UTC [56] 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.
2019-10-02 02:28:48.708 UTC [56] HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2019-10-02 02:28:48.713 UTC [50] LOG:  all server processes terminated; reinitializing
2019-10-02 02:28:49.412 UTC [296] LOG:  database system was interrupted; last known up at 2019-10-02 02:26:19 UTC
2019-10-02 02:28:52.757 UTC [296] LOG:  database system was not properly shut down; automatic recovery in progress
2019-10-02 02:28:52.823 UTC [296] LOG:  redo starts at 0/16662D8
2019-10-02 02:28:52.866 UTC [296] LOG:  invalid record length at 0/17089B8: wanted 24, got 0
2019-10-02 02:28:52.866 UTC [296] LOG:  redo done at 0/17085E0
2019-10-02 02:28:52.866 UTC [296] LOG:  last completed transaction was at log time 2019-10-02 02:28:46.439249+00
2019-10-02 02:28:53.503 UTC [50] LOG:  database system is ready to accept connections

And dmesg:

[283129.470601] postmaster[28295]: segfault at 0 ip 00007f79f28ef221 sp 00007fff7a174748 error 4 in libc-2.12.so[7f79f286e000+18b000]
[283129.474333] Code: 00 c3 f7 05 9d 20 31 00 04 00 00 00 74 07 48 8d 05 a4 ee 0b 00 c3 0f 1f 00 48 31 c0 89 f9 83 e1 3f 66 0f ef c0 83 f9 30 77 19 <f3> 0f 6f 0f 66 0f 74 c1 66 0f d7 d0 85 d2 75 7a 48 89 f8 48 83 e0
[283141.396402] postmaster[28364]: segfault at 0 ip 00007f24b2999746 sp 00007ffcf8e4fde8 error 4 in libc-2.23.so[7f24b290e000+1c0000]
[283141.400046] Code: ff ff ff 90 66 0f ef c0 66 0f ef c9 66 0f ef d2 66 0f ef db 48 89 f8 48 89 f9 48 81 e1 ff 0f 00 00 48 81 f9 cf 0f 00 00 77 6a <f3> 0f 6f 20 66 0f 74 e0 66 0f d7 d4 85 d2 74 04 0f bc c2 c3 48 83
jcarnu commented 5 years ago

Thanks for building ci images. I'll try to see what's going on but only tomorrow. I'm ooo today.

Le mer. 2 oct. 2019 à 04:41, Julio González Gil notifications@github.com a écrit :

Ok, so it seems we now have a genuine failure at this test: https://github.com/tds-fdw/tds_fdw/blob/master/tests/tests/postgresql/031_query_option_column_match_enabled.sql

For both Ubuntu 16.04 and CentOS6.

It seems we have a segfault.

This is from a the Ubuntu 16.04 container, postgresql.log:

2019-10-02 02:26:07.014 UTC [73] LOG: starting PostgreSQL 12rc1 (Ubuntu 12~rc1-1.pgdg16.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.11) 5.4.0 20160609, 64-bit 2019-10-02 02:26:07.015 UTC [73] LOG: listening on IPv4 address "127.0.0.1", port 5432 2019-10-02 02:26:07.015 UTC [73] LOG: could not bind IPv6 address "::1": Cannot assign requested address 2019-10-02 02:26:07.015 UTC [73] HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry. 2019-10-02 02:26:07.139 UTC [73] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432" 2019-10-02 02:26:07.352 UTC [74] LOG: database system was shut down at 2019-10-02 02:26:06 UTC 2019-10-02 02:26:07.437 UTC [73] LOG: database system is ready to accept connections 2019-10-02 02:27:13.036 UTC [301] pgtest@tds_fdw WARNING: Table definition mismatch: Could not match local column id with column from foreign table 2019-10-02 02:27:18.864 UTC [301] pgtest@tds_fdw WARNING: Table definition mismatch: Could not match local column id with column from foreign table 2019-10-02 02:27:24.738 UTC [301] pgtest@tds_fdw WARNING: Table definition mismatch: Could not match local column id with column from foreign table 2019-10-02 02:27:30.561 UTC [301] pgtest@tds_fdw WARNING: Table definition mismatch: Could not match local column id with column from foreign table 2019-10-02 02:27:36.322 UTC [301] pgtest@tds_fdw WARNING: Table definition mismatch: Could not match local column id with column from foreign table 2019-10-02 02:27:42.168 UTC [301] pgtest@tds_fdw WARNING: Table definition mismatch: Could not match local column id with column from foreign table 2019-10-02 02:27:48.043 UTC [301] pgtest@tds_fdw WARNING: Table definition mismatch: Could not match local column id with column from foreign table 2019-10-02 02:27:53.822 UTC [301] pgtest@tds_fdw WARNING: Table definition mismatch: Could not match local column id with column from foreign table 2019-10-02 02:28:46.719 UTC [301] pgtest@tds_fdw WARNING: Table definition mismatch: Could not match local column id with column from foreign table 2019-10-02 02:28:58.348 UTC [301] pgtest@tds_fdw WARNING: Table definition mismatch: Could not match local column id with column from foreign table 2019-10-02 02:29:00.584 UTC [73] LOG: server process (PID 301) was terminated by signal 11: Segmentation fault 2019-10-02 02:29:00.584 UTC [73] DETAIL: Failed process was running: DROP FOREIGN TABLE IF EXISTS postgresql12testing_ubuntu1604_test.query_option_column_match_enabled;

    CREATE FOREIGN TABLE postgresql12testing_ubuntu1604_test.query_option_column_match_enabled (
            id int,
            data varchar(50),
            owner varchar(50)
    )
            SERVER mssql_svr
            OPTIONS (query 'SELECT * FROM postgresql12testing_ubuntu1604_test.query_option WHERE owner = ''geoff''', row_estimate_method 'showplan_all', match_column_names '1');

    SELECT * FROM postgresql12testing_ubuntu1604_test.query_option_column_match_enabled;
    WITH count AS (
            SELECT COUNT(*) as count FROM postgresql12testing_ubuntu1604_test.query_option_column_match_enabled
    )
    SELECT (count = 1) FROM count;
    WITH owners AS (
            SELECT DISTINCT owner FROM postgresql12testing_ubuntu1604_test.query_option_column_match_enabled
    )
    SELECT (owner = 'geoff') FROM owners;

2019-10-02 02:29:00.584 UTC [73] LOG: terminating any other active server processes 2019-10-02 02:29:00.591 UTC [80] WARNING: terminating connection because of crash of another server process 2019-10-02 02:29:00.591 UTC [80] 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. 2019-10-02 02:29:00.591 UTC [80] HINT: In a moment you should be able to reconnect to the database and repeat your command. 2019-10-02 02:29:00.610 UTC [73] LOG: all server processes terminated; reinitializing 2019-10-02 02:29:00.851 UTC [305] LOG: database system was interrupted; last known up at 2019-10-02 02:26:33 UTC 2019-10-02 02:29:02.102 UTC [305] LOG: database system was not properly shut down; automatic recovery in progress 2019-10-02 02:29:02.188 UTC [305] LOG: redo starts at 0/163DFB0 2019-10-02 02:29:02.248 UTC [305] LOG: invalid record length at 0/16DE728: wanted 24, got 0 2019-10-02 02:29:02.248 UTC [305] LOG: redo done at 0/16DE350 2019-10-02 02:29:02.248 UTC [305] LOG: last completed transaction was at log time 2019-10-02 02:28:58.349849+00 2019-10-02 02:29:02.785 UTC [73] LOG: database system is ready to accept connections

This is from dmesg:

283129.470601] postmaster[28295]: segfault at 0 ip 00007f79f28ef221 sp 00007fff7a174748 error 4 in libc-2.12.so[7f79f286e000+18b000] [283129.474333] Code: 00 c3 f7 05 9d 20 31 00 04 00 00 00 74 07 48 8d 05 a4 ee 0b 00 c3 0f 1f 00 48 31 c0 89 f9 83 e1 3f 66 0f ef c0 83 f9 30 77 19 0f 6f 0f 66 0f 74 c1 66 0f d7 d0 85 d2 75 7a 48 89 f8 48 83 e0 [283141.396402] postmaster[28364]: segfault at 0 ip 00007f24b2999746 sp 00007ffcf8e4fde8 error 4 in libc-2.23.so[7f24b290e000+1c0000] [283141.400046] Code: ff ff ff 90 66 0f ef c0 66 0f ef c9 66 0f ef d2 66 0f ef db 48 89 f8 48 89 f9 48 81 e1 ff 0f 00 00 48 81 f9 cf 0f 00 00 77 6a 0f 6f 20 66 0f 74 e0 66 0f d7 d4 85 d2 74 04 0f bc c2 c3 48 83

And this from the CentOS6 container:

2019-10-02 02:25:56.139 UTC [34] LOG: database system was shut down at 2019-10-01 19:22:23 UTC 2019-10-02 02:25:56.245 UTC [13] LOG: database system is ready to accept connections 2019-10-02 02:25:56.568 UTC [13] LOG: received fast shutdown request 2019-10-02 02:25:56.601 UTC [13] LOG: aborting any active transactions 2019-10-02 02:25:56.604 UTC [13] LOG: background worker "logical replication launcher" (PID 40) exited with exit code 1 2019-10-02 02:25:56.607 UTC [35] LOG: shutting down 2019-10-02 02:25:58.131 UTC [13] LOG: database system is shut down 2019-10-02 02:25:58.976 UTC [52] LOG: database system was shut down at 2019-10-02 02:25:57 UTC 2019-10-02 02:25:59.094 UTC [50] LOG: database system is ready to accept connections 2019-10-02 02:27:00.267 UTC [292] WARNING: Table definition mismatch: Could not match local column id with column from foreign table 2019-10-02 02:27:06.085 UTC [292] WARNING: Table definition mismatch: Could not match local column id with column from foreign table 2019-10-02 02:27:11.954 UTC [292] WARNING: Table definition mismatch: Could not match local column id with column from foreign table 2019-10-02 02:27:17.833 UTC [292] WARNING: Table definition mismatch: Could not match local column id with column from foreign table 2019-10-02 02:27:23.678 UTC [292] WARNING: Table definition mismatch: Could not match local column id with column from foreign table 2019-10-02 02:27:29.520 UTC [292] WARNING: Table definition mismatch: Could not match local column id with column from foreign table 2019-10-02 02:27:35.354 UTC [292] WARNING: Table definition mismatch: Could not match local column id with column from foreign table 2019-10-02 02:27:41.187 UTC [292] WARNING: Table definition mismatch: Could not match local column id with column from foreign table 2019-10-02 02:28:34.584 UTC [292] WARNING: Table definition mismatch: Could not match local column id with column from foreign table 2019-10-02 02:28:46.438 UTC [292] WARNING: Table definition mismatch: Could not match local column id with column from foreign table 2019-10-02 02:28:48.702 UTC [50] LOG: server process (PID 292) was terminated by signal 11: Segmentation fault 2019-10-02 02:28:48.702 UTC [50] DETAIL: Failed process was running: DROP FOREIGN TABLE IF EXISTS postgresql12testing_centos6_test.query_option_column_match_enabled;

    CREATE FOREIGN TABLE postgresql12testing_centos6_test.query_option_column_match_enabled (
            id int,
            data varchar(50),
            owner varchar(50)
    )
            SERVER mssql_svr
            OPTIONS (query 'SELECT * FROM postgresql12testing_centos6_test.query_option WHERE owner = ''geoff''', row_estimate_method 'showplan_all', match_column_names '1');

    SELECT * FROM postgresql12testing_centos6_test.query_option_column_match_enabled;
    WITH count AS (
            SELECT COUNT(*) as count FROM postgresql12testing_centos6_test.query_option_column_match_enabled
    )
    SELECT (count = 1) FROM count;
    WITH owners AS (
            SELECT DISTINCT owner FROM postgresql12testing_centos6_test.query_option_column_match_enabled
    )
    SELECT (owner = 'geoff') FROM owners;

2019-10-02 02:28:48.702 UTC [50] LOG: terminating any other active server processes 2019-10-02 02:28:48.708 UTC [56] WARNING: terminating connection because of crash of another server process 2019-10-02 02:28:48.708 UTC [56] 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. 2019-10-02 02:28:48.708 UTC [56] HINT: In a moment you should be able to reconnect to the database and repeat your command. 2019-10-02 02:28:48.713 UTC [50] LOG: all server processes terminated; reinitializing 2019-10-02 02:28:49.412 UTC [296] LOG: database system was interrupted; last known up at 2019-10-02 02:26:19 UTC 2019-10-02 02:28:52.757 UTC [296] LOG: database system was not properly shut down; automatic recovery in progress 2019-10-02 02:28:52.823 UTC [296] LOG: redo starts at 0/16662D8 2019-10-02 02:28:52.866 UTC [296] LOG: invalid record length at 0/17089B8: wanted 24, got 0 2019-10-02 02:28:52.866 UTC [296] LOG: redo done at 0/17085E0 2019-10-02 02:28:52.866 UTC [296] LOG: last completed transaction was at log time 2019-10-02 02:28:46.439249+00 2019-10-02 02:28:53.503 UTC [50] LOG: database system is ready to accept connections

And dmesg:

[283129.470601] postmaster[28295]: segfault at 0 ip 00007f79f28ef221 sp 00007fff7a174748 error 4 in libc-2.12.so[7f79f286e000+18b000] [283129.474333] Code: 00 c3 f7 05 9d 20 31 00 04 00 00 00 74 07 48 8d 05 a4 ee 0b 00 c3 0f 1f 00 48 31 c0 89 f9 83 e1 3f 66 0f ef c0 83 f9 30 77 19 0f 6f 0f 66 0f 74 c1 66 0f d7 d0 85 d2 75 7a 48 89 f8 48 83 e0 [283141.396402] postmaster[28364]: segfault at 0 ip 00007f24b2999746 sp 00007ffcf8e4fde8 error 4 in libc-2.23.so[7f24b290e000+1c0000] [283141.400046] Code: ff ff ff 90 66 0f ef c0 66 0f ef c9 66 0f ef d2 66 0f ef db 48 89 f8 48 89 f9 48 81 e1 ff 0f 00 00 48 81 f9 cf 0f 00 00 77 6a 0f 6f 20 66 0f 74 e0 66 0f d7 d4 85 d2 74 04 0f bc c2 c3 48 83

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/tds-fdw/tds_fdw/pull/213?email_source=notifications&email_token=AAA5UH33TGGV2P6XSDYCEHTQMQC6ZA5CNFSM4I4GDWG2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEADLPYQ#issuecomment-537311202, or mute the thread https://github.com/notifications/unsubscribe-auth/AAA5UH47SN3EUSCGMFSVUULQMQC6ZANCNFSM4I4GDWGQ .

jcarnu commented 5 years ago

Okaye, just had a look on test infrastructure. I'll need to have more explanations on how it works so that I can execute the same test on my machine (in order to debug). Is there a simple way to inject full schema + data un MSSQL + rolling tests on PostgreSQL without building the whole docker infrastructure ? How @GeoffMontee is proceeding for testing ?

Testing is one thing, debugging is another ;)

GeoffMontee commented 5 years ago

Hi @jcarnu ,

You can see how the tests are executed in our CI by taking a look at the actions bash script in the ci-build repository:

https://github.com/tds-fdw/ci-build/blob/master/lib/actions

Most of the work to run the actual tests is done by the mssql-tests.py and postgresql-tests.py python scripts:

https://github.com/tds-fdw/tds_fdw/blob/master/tests/mssql-tests.py

https://github.com/tds-fdw/tds_fdw/blob/master/tests/postgresql-tests.py

The individual tests are in this directory:

https://github.com/tds-fdw/tds_fdw/tree/master/tests/tests

Unfortunately, you do need Microsoft SQL Server to run the tests. Our testing actually uses Azure SQL Database, which is Microsoft's DBaaS that is based on Microsoft SQL Server.

https://azure.microsoft.com/en-us/services/sql-database/

I am trying to do some debugging of this problem myself as well, but it's taking me a while.

juliogonzalez commented 5 years ago

I should probably write a doc about how things are working, and most probably should provide Docker containers for debugging, but here's a quick explanation:

You don't really need docker. If you prefer to use a VM, or even your own computer, that's fine.

Requisites:

Then just use the script ci from https://github.com/tds-fdw/ci-build/ to run the steps.

This is how our jobs are doing it:

ci --action get_info --pgver="${PG_VER}" --distro "${DISTRO}"
ci --action mod_build --pgver="${PG_VER}" --distro "${DISTRO}" --tdsdir="${TDS_DIR}"
ci --action mod_install --pgver="${PG_VER}" --distro "${DISTRO}" --tdsdir="${TDS_DIR}"
ci --action pg_start --pgver="${PG_VER}" --distro "${DISTRO}"
ci --action pg_confdb --pgver="${PG_VER}" --distro "${DISTRO}" --postgresuser="${POSTGRESUSER}" --postgrespass="${POSTGRESPASS}" --postgresdb="${POSTGRESDB}"
ci --action mssql_test --pgver="${PG_VER}" --distro "${DISTRO}" --tdsdir="${TDS_DIR}" --mssqluser="${MSSQLUSER}" --mssqlpass="${MSSQLPASS}" --mssqldb="${MSSQLDB}" --mssqlhost="${MSSQLHOST}" --mssqlport="${MSSQLPORT}"
ci --action pg_test --pgver="${PG_VER}" --distro "${DISTRO}" --tdsdir="${TDS_DIR}" --postgresuser="${POSTGRESUSER}" --postgrespass="${POSTGRESPASS}" --postgresdb="${POSTGRESDB}" --mssqluser="${MSSQLUSER}" --mssqlpass="${MSSQLPASS}" --mssqldb="${MSSQLDB}" --mssqlhost="${MSSQLHOST}" --mssqlport="${MSSQLPORT}"

${TDS_DIR} is the path to a local git repository (yours in your case). Other parameters are easy to understand, and documented with ci --help

Needless to say, you can also build and install everything on your own, and then use the scripts at tests/ folder. Make sure you run those for MSSQL first and then those for PostgreSQL.

jcarnu commented 5 years ago

So I did have the correct data in a local MSSQL Server Express DB : 

Microsoft SQL Server 2019 (RC1) - 15.0.1900.25 (X64) 
    Aug 16 2019 14:20:53 
    Copyright (C) 2019 Microsoft Corporation
    Express Edition (64-bit) on Linux (CentOS Linux 7 (Core)) <X64>

But tds_fdw does not allow connection (something about tds_version and adaptative ability). Even if I don't specify tds version.

I tried to tweak the source code with a 8.0 os something version but I had no better result.

OTOH I tried to understand why there's a segv with that test :

The output is 

StmtText    StmtId  NodeId  Parent  PhysicalOp  LogicalOp   Argument    DefinedValues   EstimateRows    EstimateIO  EstimateCPU AvgRowSize  TotalSubtreeCost    OutputList  Warnings    Type    Parallel    EstimateExecutions
SELECT * FROM testschema.query_option WHERE owner = 'geoff' 1   1   0   NULL    NULL    1   NULL    1   NULL    NULL    NULL    0.00328529999   NULL    NULL    SELECT  0   NULL
  |--Clustered Index Scan(OBJECT:([sakila].[testschema].[query_option].[PK__query_op__3213E83F99619B17]), WHERE:([sakila].[testschema].[query_option].[owner]=[@1]))    1   2   1   Clustered Index Scan    Clustered Index Scan    OBJECT:([sakila].[testschema].[query_option].[PK__query_op__3213E83F99619B17]), WHERE:([sakila].[testschema].[query_option].[owner]=[@1])   [sakila].[testschema].[query_option].[id], [sakila].[testschema].[query_option].[data], [sakila].[testschema].[query_option].[owner]    1   0.00312500005   0.000160299998  46  0.00328529999   [sakila].[testschema].[query_option].[id], [sakila].[testschema].[query_option].[data], [sakila].[testschema].[query_option].[owner]    NULL    PLAN_ROW    0   1
(2 rows affected)

Sorry for bad formatting but that's tds output.

This output is "deparsed" by deparseSubscriptingRef() but it's unclear to me where segv happens. Using DEBUG2for log_min_messages will generate a lot of debug information but will also improve spotting the problem.

I'm stuck with DBVERSION_XX checking at that point. I cannot import SCHEMA or even CREATE FOREIGN TABLE.

If any ideas to help, i'll be happy.

Thanks.

GeoffMontee commented 5 years ago

So I did have the correct data in a local MSSQL Server Express DB :

Microsoft SQL Server 2019 (RC1) - 15.0.1900.25 (X64) ... But tds_fdw does not allow connection (something about tds_version and adaptative ability). Even if I don't specify tds version.

This FreeTDS email thread suggests that at least one person has been able to connect to Microsoft SQL Server 2019 with FreeTDS 0.91-6.1 with the TDS version set to 7.1.

What version of FreeTDS are you using? Did you try setting tds_version to 7.1, 7.2, 7.3, or 7.4? Depending on your FreeTDS version, your system may not support all of those. See here for a description of each version.

jcarnu commented 5 years ago

Here's my setup on centos 7 :

yum info freetds
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
 * base: centos.mirror.fr.planethoster.net
 * centos-sclo-rh: centos.mirrors.proxad.net
 * centos-sclo-sclo: centos.mirrors.proxad.net
 * epel: ams.edge.kernel.org
 * extras: centos.mirrors.proxad.net
 * updates: ftp.pasteur.fr
Installed Packages
Name        : freetds
Arch        : x86_64
Version     : 1.1.11
Release     : 1.el7
Size        : 1.5 M
Repo        : installed
From repo   : epel
Summary     : Implementation of the TDS (Tabular DataStream) protocol
URL         : http://www.freetds.org/
License     : LGPLv2+ and GPLv2+
Description : FreeTDS is a project to document and implement the TDS (Tabular
            : DataStream) protocol. TDS is used by Sybase(TM) and Microsoft(TM) for
            : client to database server communications. FreeTDS includes call
            : level interfaces for DB-Lib, CT-Lib, and ODBC.

[root@localhost tds_fdw]# TDSVER=8.0 tsql -H localhost -U SA -P migrationTraining19 -D sakila -p 1433
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
Setting sakila as default database in login packet
1> select @@VERSION;
2> go

Microsoft SQL Server 2019 (RC1) - 15.0.1900.25 (X64) 
    Aug 16 2019 14:20:53 
    Copyright (C) 2019 Microsoft Corporation
    Express Edition (64-bit) on Linux (CentOS Linux 7 (Core)) <X64>
(1 row affected)
1>

Event if I don't define TDSVER at tsql run time it works fine. and my FreeTDS version is able to connect SQLServer 2019.

I also tried tds_version in tds_fdw until 7.4 => not better.

IHMO, the problem lies in the checks made in tds_fdw at connect time and that this parameter seems to be mandatory. Is this really mandatory or can we imagine bypassing it ?

GeoffMontee commented 5 years ago

IHMO, the problem lies in the checks made in tds_fdw at connect time and that this parameter seems to be mandatory. Is this really mandatory or can we imagine bypassing it ?

The parameter used to be mandatory out of necessity, but it looks like FreeTDS 1.1 makes the default TDS version some new "auto" value, which allows the library to auto-detect the best TDS protocol version to use with the server. See here:

  • Changed default TDS protocol version during configure to "auto". Versions 4.2 and 7.0 are no longer accepted for default, you may still specify an explicit version to connect to obsolete servers ("auto" won't attempt these versions);

We should probably make this auto-detection the default in tds_fdw as well.

Since auto-detection is now the default in FreeTDS, we could probably make it the difficult by some minor modifications in this code block:

https://github.com/tds-fdw/tds_fdw/blob/ce55ea1b6b0d14c2b12e8b506c49d0a9c0775233/src/tds_fdw.c#L454

i.e. perhaps by only calling dbsetlversion() if tds_version != DBVERSION_UNKNOWN

https://github.com/tds-fdw/tds_fdw/blob/ce55ea1b6b0d14c2b12e8b506c49d0a9c0775233/src/tds_fdw.c#L509

and removing the error message that is thrown if tds_version == DBVERSION_UNKNOWN

https://github.com/tds-fdw/tds_fdw/blob/ce55ea1b6b0d14c2b12e8b506c49d0a9c0775233/src/tds_fdw.c#L501

GeoffMontee commented 5 years ago

Actually, if you just set the TDS version to DBVERSION_UNKNOWN, then it looks like FreeTDS treats this as TDS version 0:

https://github.com/FreeTDS/freetds/blob/e9f36b7c0f472f4951413e22924c9c95e8cc5cd1/src/dblib/dblib.c#L947

And it looks like FreeTDS considers TDS version 0 to be the "auto" value:

https://github.com/FreeTDS/freetds/blob/e9f36b7c0f472f4951413e22924c9c95e8cc5cd1/src/tds/config.c#L907

So we may be able to make the auto-detection the default behavior by simply removing this check and error message:

https://github.com/tds-fdw/tds_fdw/blob/ce55ea1b6b0d14c2b12e8b506c49d0a9c0775233/src/tds_fdw.c#L501

I'm not sure how that change would effect users who still use older versions of FreeTDS.

jcarnu commented 5 years ago

Yeay, I managed to get the test working and failing :) Thanks. I'll be back with some hot news soon.

jcarnu commented 5 years ago

Here's the first stack trace of the crash :

(gdb) where
#0  __strlen_sse2_pminub () at ../sysdeps/x86_64/multiarch/strlen-sse2-pminub.S:38
#1  0x00000000008b6f50 in dopr.constprop.2 ()
#2  0x00000000008b7df5 in pg_vsnprintf ()
#3  0x00000000008bd4e1 in pvsnprintf ()
#4  0x000000000064da04 in appendStringInfoVA ()
#5  0x000000000064dbd2 in appendStringInfo ()
#6  0x00007f83fba614f2 in deparseSelectSql (buf=0x7ffe465d9920, root=0x1fadad8, baserel=0x1f85be8, attrs_used=0x205a3d8, retrieved_attrs=0x7ffe465d99e8, option_set=<optimized out>) at src/deparse.c:780
#7  0x00007f83fba5a6f4 in tdsBuildForeignQuery (root=root@entry=0x1fadad8, baserel=baserel@entry=0x1f85be8, option_set=option_set@entry=0x7ffe465d9a70, attrs_used=0x205a3d8, retrieved_attrs=retrieved_attrs@entry=0x7ffe465d99e8, remote_conds=<optimized out>, remote_join_conds=0x0, pathkeys=pathkeys@entry=0x0) at src/tds_fdw.c:316
#8  0x00007f83fba5c8d1 in estimate_path_cost_size (root=root@entry=0x1fadad8, baserel=baserel@entry=0x1f85be8, join_conds=join_conds@entry=0x0, pathkeys=pathkeys@entry=0x0, p_rows=p_rows@entry=0x1fae578, p_width=p_width@entry=0x1fae580, p_startup_cost=p_startup_cost@entry=0x1fae588, p_total_cost=p_total_cost@entry=0x1fae590, option_set=option_set@entry=0x7ffe465d9a70)
    at src/tds_fdw.c:2052
#9  0x00007f83fba5ceb7 in tdsGetForeignRelSize (root=0x1fadad8, baserel=0x1f85be8, foreigntableid=<optimized out>) at src/tds_fdw.c:2309
#10 0x000000000068e814 in set_rel_size ()
#11 0x00000000006900cd in make_one_rel ()
#12 0x00000000006ae2e2 in query_planner ()
#13 0x00000000006b265f in grouping_planner ()
#14 0x00000000006b4786 in subquery_planner ()
#15 0x00000000006b5946 in standard_planner ()
#16 0x000000000075fa9e in pg_plan_query ()
#17 0x000000000075fb5e in pg_plan_queries ()
#18 0x000000000075ffea in exec_simple_query ()
#19 0x0000000000761182 in PostgresMain ()
#20 0x0000000000483d02 in ServerLoop ()
#21 0x00000000006f0a8f in PostmasterMain ()
#22 0x0000000000484c93 in main ()

As unexpected the problem is located in deparseSelectSql rather than the modified source code (at first sight)

#6  0x00007f83fba614f2 in deparseSelectSql (buf=0x7ffe465d9920, root=0x1fadad8, baserel=0x1f85be8, attrs_used=0x205a3d8, retrieved_attrs=0x7ffe465d99e8, option_set=<optimized out>) at src/deparse.c:780
780             deparseRelation(buf, rel);
(dgb) print(*buf)
$1 = {data = 0x205a3f8 "SELECT [id], [data], [owner] FROM ", len = 34, maxlen = 1024, cursor = 0}

I'll dig deeper asap.

jcarnu commented 5 years ago

Okaye, I think I found the problem (not the solution) :

The query is :

select * from testsch.query_option_column_match_enabled;

FDW query (table) for query_option_column_match_enabled is :

https://github.com/tds-fdw/tds_fdw/blob/2b5b16982ba589f4ada5acfcea0e9be88fea3fa5/tests/tests/postgresql/031_query_option_column_match_enabled.sql#L3-L9

in deparseRelation() (deparse.c) called by deparseSelectSql :

https://github.com/tds-fdw/tds_fdw/blob/2b5b16982ba589f4ada5acfcea0e9be88fea3fa5/src/deparse.c#L1216-L1258

Variable table has a defname of "query", thus leading to have nspname and relname to NULL

So the ending if/else ends with SEGV while appendStringInfo.

I've not dug into the full processing of the FDW query but it seems that the commented out part should be decommented maybe ?

If I uncomment this block, it works great! (well actually I have tons of errors on my version but backend does not crash anymore and I think it's all about my sqlsrv configuration)

jcarnu commented 5 years ago

Anyway I'll push that changes to my repo and it would be great it tests could be launched on your CI

GeoffMontee commented 5 years ago

Thanks for the excellent analysis and for working on this, @jcarnu . We'll definitely test the changes when they're ready.

GeoffMontee commented 5 years ago

Test this, please

jenkins-juliogonzalez commented 5 years ago

Test FAILed.

jcarnu commented 5 years ago

Huh : all is red.

https://jenkins.juliogonzalez.es/job/tds_fdw-build-pr/DISTRO=centos6,PG_VER=12-testing,label=docker/lastBuild/console

I thought it was my sqlsrv configuration but it's not :-/

jcarnu commented 5 years ago

This change won't correct for v12 but will avoid other version to fail.

jcarnu commented 5 years ago

Here's the first failing test on my testing VM :

select (value = 0) as pass from testsch.tinyint_min ;
NOTICE:  tds_fdw: Query executed correctly
NOTICE:  tds_fdw: Getting results
WARNING:  Table definition mismatch: Could not match local column id with column from foreign table
 pass 
------
 t
(1 row)
juliogonzalez commented 5 years ago

@jcarnu I whitelisted you at our job, so each commit you submit to the PR will automatically launch the tests, to easy things.

If you want to force the tests, you need to write a comment containing the text:

Retest this, please

(this comment just launched the job).

jenkins-juliogonzalez commented 5 years ago

Test FAILed.

jcarnu commented 5 years ago

@juliogonzalez thanks. I don't know what's going wrong with v12 build whereas it seems result for tinyint are correct (works on my machine syndrome ? ;) )

jcarnu commented 5 years ago

This will fail....

But "enhancing" (on my own) the tests output, here's the psycopg2 diagnostic object content while failing :

[INFO] 004: Testing tinyint data type, minimum value (smallint)
DROP FOREIGN TABLE IF EXISTS testsch.tinyint_min;

CREATE FOREIGN TABLE testsch.tinyint_min (
        id int,
        value smallint
)
        SERVER mssql_svr
        OPTIONS (table 'testschema.tinyint_min', row_estimate_method 'showplan_all');

SELECT * FROM testsch.tinyint_min;
SELECT (value = 0) AS pass FROM testsch.tinyint_min;

[ERROR] Error running tinyint data type, minimum value (smallint) (tests/postgresql/004_tinyintmin.sql)
[ERROR] HV00L
[ERROR] ERROR:  DB-Library error: DB #: 20018, DB Msg: General SQL Server error: Check messages from the SQL Server, OS #: -1, OS Msg: , Level: 16

[ERROR] column_name : None
[ERROR] constraint_name : None
[ERROR] context : None
[ERROR] datatype_name : None
[ERROR] internal_position : None
[ERROR] internal_query : None
[ERROR] message_detail : None
[ERROR] message_hint : None
[ERROR] message_primary : DB-Library error: DB #: 20018, DB Msg: General SQL Server error: Check messages from the SQL Server, OS #: -1, OS Msg: , Level: 16
[ERROR] schema_name : None
[ERROR] severity : ERROR
[ERROR] severity_nonlocalized : ERROR
[ERROR] source_file : tds_fdw.c
[ERROR] source_function : tds_err_handler
[ERROR] source_line : 3929
[ERROR] sqlstate : HV00L
[ERROR] statement_position : None
[ERROR] table_name : None

I'll put a breakpoint on that handler.

jcarnu commented 5 years ago

Here's the output for «error handler» :

Breakpoint 1, tds_err_handler (dbproc=0x208d320, severity=16, dberr=20018, oserr=-1, dberrstr=0x7f83fb83aca0 "General SQL Server error: Check messages from the SQL Server", oserrstr=0x0) at src/tds_fdw.c:3919
3919    {
(gdb) where
#0  tds_err_handler (dbproc=0x208d320, severity=16, dberr=20018, oserr=-1, dberrstr=0x7f83fb83aca0 "General SQL Server error: Check messages from the SQL Server", oserrstr=0x0) at src/tds_fdw.c:3919
#1  0x00007f83fb7ffc51 in _dblib_handle_info_message () from /lib64/libsybdb.so.5
#2  0x00007f83fb80e914 in tds_process_info () from /lib64/libsybdb.so.5
#3  0x00007f83fb80d5e2 in tds_process_default_tokens () from /lib64/libsybdb.so.5
#4  0x00007f83fb80f8e1 in tds_process_tokens () from /lib64/libsybdb.so.5
#5  0x00007f83fb7f8e9e in dbsqlok () from /lib64/libsybdb.so.5
#6  0x00007f83fba5ba57 in tdsGetRowCountShowPlanAll (option_set=option_set@entry=0x7ffe465d9a70, login=login@entry=0x2078170, dbproc=0x208d320) at src/tds_fdw.c:705
#7  0x00007f83fba5c783 in tdsGetRowCount (option_set=option_set@entry=0x7ffe465d9a70, login=login@entry=0x2078170, dbproc=<optimized out>) at src/tds_fdw.c:1053
#8  0x00007f83fba5cb55 in estimate_path_cost_size (root=root@entry=0x208f1a0, baserel=baserel@entry=0x208e668, join_conds=join_conds@entry=0x0, pathkeys=pathkeys@entry=0x0, p_rows=p_rows@entry=0x208f6c0, p_width=p_width@entry=0x208f6c8, p_startup_cost=p_startup_cost@entry=0x208f6d0, p_total_cost=p_total_cost@entry=0x208f6d8, option_set=option_set@entry=0x7ffe465d9a70)
    at src/tds_fdw.c:2112
#9  0x00007f83fba5ceb7 in tdsGetForeignRelSize (root=0x208f1a0, baserel=0x208e668, foreigntableid=<optimized out>) at src/tds_fdw.c:2309
#10 0x000000000068e814 in set_rel_size ()
#11 0x00000000006900cd in make_one_rel ()
#12 0x00000000006ae2e2 in query_planner ()
#13 0x00000000006b265f in grouping_planner ()
#14 0x00000000006b4786 in subquery_planner ()
#15 0x00000000006b5946 in standard_planner ()
#16 0x000000000075fa9e in pg_plan_query ()
#17 0x000000000075fb5e in pg_plan_queries ()
#18 0x000000000075ffea in exec_simple_query ()
#19 0x0000000000761182 in PostgresMain ()
#20 0x0000000000483d02 in ServerLoop ()
#21 0x00000000006f0a8f in PostmasterMain ()
#22 0x0000000000484c93 in main ()

I tried to crawl the call stack but I haven't the freetds lib symbols loaded. Difficult to debug this way for me.

The things are not working on FOREIGN TABLES with row_estimate_method set to showplan_all.

@GeoffMontee do you remember the reason why you commented the if block in deparseRelation ? Maybe we should handle the cas for else if (strcmp(def->defname, "query") == 0) ?

Any thoughts ?

BTW I have to work on another point (my pgconfeu training on migration) so let me know what can be done or what I could do to help you to fix this ?

Thanks.

jcarnu commented 5 years ago

Thoughts : It'll be nice to add some debug switches. I started changing python code on my side (crappy). What about a new --debugging switch in postgresql-tests.py that :

--debugging should be disabled by default for CI purpose. Here again, any thoughts ?

jcarnu commented 5 years ago

I found the bug (not the cause) 

2019-10-04 15:56:17.687 CEST [16273] DEBUG:  tds_fdw: Setting database command to SELECT [id], [value] FROM [testsch].[testschema.tinyint_min]

It turns out that we have both PostgreSQL and MSSQL schema in the query.

Looking closer to the source code, nspname is PostgreSQL schema and should not be appended to fully qualified relname.

Next commit

jcarnu commented 5 years ago

Retest this, please

jcarnu commented 5 years ago

@juliogonzalez it seems that I cannot do this, I have no write access to the repo (or I missed something) Should I ask jenkins to

Retest this, please

because it was to early ?

jenkins-juliogonzalez commented 5 years ago

Test PASSed.

jenkins-juliogonzalez commented 5 years ago

Test PASSed.

jenkins-juliogonzalez commented 5 years ago

Test PASSed.

juliogonzalez commented 5 years ago

I'd say you were able to launch tests. On paper the tests are launched when you commit, and also when you use the "magic" comment, but you don't need both (the comment is only in case they fail because of something unrelated to the test).

Thoughts : It'll be nice to add some debug switches. I started changing python code on my side (crappy). What about a new --debugging switch in postgresql-tests.py that :

  • calls SELECT pg_backend_pid() and print it then pauses, then we can gdb --pid= on the process to debug, putting breakpoints anywhere and finally squash bugs;
  • prints out the full sql query that is sent to the PG server;
  • whenever a test fails, displays psycopg2.extensions.Diagnostics object. --debugging should be disabled by default for CI purpose.

Here again, any thoughts ?

Good ideas! They are all doable, and I think not that hard to implement.

I also would add that we should capture the output of dmesg command and postgresql.log file when there are failures.

I will see if I can give it a try in the next following days.

jcarnu commented 5 years ago

@juliogonzalez check this out : https://github.com/jcarnu/tds_fdw/tree/jcarnu_tests_debugging_option

If ok I'll put an "issue" as feature request and will PR

juliogonzalez commented 5 years ago

Please open a PR (no need for an issue).

I have some minor comments, but I will add them directly at the PR :-)

GeoffMontee commented 5 years ago

Thanks for all your work on this, @jcarnu ! This has been merged.

Did you also have to make any changes to tds_fdw's TDS version handling to get it working with SQL Server 2019?

jcarnu commented 5 years ago

@GeoffMontee thanks for the merge. Yes I tweaked one or two connexion setting in order to connect. I am afk for the weekend I'll give more explanation. It involves both C source and connexion settings in test. Maybe we should make tds_version a param for postgresql-test.py.

@juliogonzalez, of course this is crappy implementation. I could have used inspect python package to enumerate Diagnostics class attributes rather than writing a bunch of strings in an array. I wrote this in some kind of hurry so excuse that bad implementation.

I will be back to you as soon as possible.

Thanks again for your help on this.

juliogonzalez commented 4 years ago

@jcarnu I wasn't saying it is crappy. At least it's a start and an improvement. For the first iteration I think it was good enough, and my review was going to suggest another things.

Feel free to open the PR as it is, and I will add my comments there :-)

Maybe we should make tds_version a param for postgresql-test.py.

I it is not, only because so far we were just testing on Azure. But yes, I agree we should add such parameter and make current value the default.

jcarnu commented 4 years ago

@juliogonzalez ah, no, I judge my own implementation as crappy ;-) And, yes, it was meant to be as efficient for debug as possible (my own purpose at this moment). Of course, any enhancement is more than welcome and I'll propose a better thing. My branch was just not to loose my work at first. BTW I'll add this option for tds_version with default in my future PR (on monday here in France).

jcarnu commented 4 years ago

@GeoffMontee I had free 10 minutes to make some tests here. Well it seems that the tds_version problem was actually a PEBCAK :sweat_smile:

After checking the current tds_fdw version against my SQLServer 2019 version, it looks like it's working well out of the box. I added the parameter for tds_version anyway.

You'll find all of this in the next PR which is focused on testing.