search

pgspider / influxdb_fdw

InfluxDB Foreign Data Wrapper for PostgreSQL.
Other
58 stars 14 forks source link

the foreign table with wrong field would crash the database #39

Closed cobolbaby closed 6 days ago

cobolbaby commented 10 months ago

When I create the external table, I reference an influxdb field that doesn't exist. Then as soon as I query the external table, the PostgreSQL server crashes.

For example, the fields in InfluxDB include A/B/C, but I defined an extra D when defining in PostgreSQL.

2023-11-13 16:06:50.231 CST,,,45,,64a22757.2d,915,,2023-07-03 09:41:43 CST,,0,LOG,00000,"server process (PID 3554481) was terminated by signal 11: Segmentation fault","Failed process was running:     select * from dm.iot_sqt_agv_status1 limit 10  ",,,,,,,,""
2023-11-13 16:06:50.231 CST,,,45,,64a22757.2d,916,,2023-07-03 09:41:43 CST,,0,LOG,00000,"terminating any other active server processes",,,,,,,,,""
mkgrgis commented 10 months ago

Hello @cobolbaby ! Please share your CREATE FOREIGN TABLE and CREATE SERVER (please without your network requisites). Please note there is 3 different client mode for influxdb_fdw and different data read modes for foreign tables.

cobolbaby commented 10 months ago 
CREATE SERVER influxdb_iot
    FOREIGN DATA WRAPPER influxdb_fdw
    OPTIONS (dbname 'iot-env', host 'http://infra-influx.ipt.inventec.net', port '80', version '1', retention_policy '');

drop FOREIGN TABLE dm.iot_sqt_agv_status1;
CREATE FOREIGN TABLE dm.iot_sqt_agv_status1(
    "time" timestamp with time zone NULL,
    "Status" character varying NULL COLLATE pg_catalog."default",
    "agvCode" character varying NULL COLLATE pg_catalog."default",
    "agvState" character varying NULL COLLATE pg_catalog."default",
    "exceptionCode" character varying NULL COLLATE pg_catalog."default",
    pointcode character varying NULL COLLATE pg_catalog."default",
    power double precision NULL,
    zoncode character varying NULL COLLATE pg_catalog."default",
    unkonw_filed character varying NULL COLLATE pg_catalog."default"
)
SERVER influxdb_iot
OPTIONS (table 'F3AGV.status1', tags 'Status,agvCode,agvState,exceptionCode,pointcode,power,zonCode', schemaless 'true');
bdc_test=# set client_min_messages='debug4';
SET
bdc_test=# select * from dm.iot_sqt_agv_status1 limit 10;
DEBUG:  influxdb_fdw : influxdb_fdw_handler
DEBUG:  influxdb_fdw : influxdbGetForeignRelSize
DEBUG:  influxdb_fdw : influxdbGetForeignPaths
DEBUG:  influxdb_fdw : influxdbGetForeignUpperPaths
DEBUG:  influxdb_fdw : influxdbGetForeignPlan
DEBUG:  influxdb_fdw : influxdbBeginForeignScan
DEBUG:  influxdb_fdw : influxdbIterateForeignScan
DEBUG:  influxdb_fdw: new InfluxDB connection 0x5d52c78fd050 for server "influxdb_iot" (user mapping oid 57344, userid 10)
DEBUG:  influxdb_fdw : query: SELECT * FROM "F3AGV.status1" LIMIT 10
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.
!> 
CREATE SERVER influxdb_iot
    FOREIGN DATA WRAPPER influxdb_fdw
    OPTIONS (dbname 'iot-env', host 'http://infra-influx.ipt.inventec.net', port '80', version '1', retention_policy '');

drop FOREIGN TABLE dm.iot_sqt_agv_status2;
CREATE FOREIGN TABLE dm.iot_sqt_agv_status2(
    "time" timestamp with time zone NULL,
    "Status" character varying NULL COLLATE pg_catalog."default",
    "agvCode" character varying NULL COLLATE pg_catalog."default",
    "agvState" character varying NULL COLLATE pg_catalog."default",
    "exceptionCode" character varying NULL COLLATE pg_catalog."default",
    pointcode character varying NULL COLLATE pg_catalog."default",
    power double precision NULL,
    unkonw_filed character varying NULL COLLATE pg_catalog."default"
)
SERVER influxdb_iot
OPTIONS (table 'F3AGV.status1', tags 'Status,agvCode,agvState,exceptionCode,pointcode,power,zonCode', schemaless 'true');
bdc_test=# set client_min_messages='debug4';
SET
bdc_test=# select * from dm.iot_sqt_agv_status2 limit 10;
DEBUG:  influxdb_fdw : influxdbGetForeignRelSize
DEBUG:  influxdb_fdw : influxdbGetForeignPaths
DEBUG:  influxdb_fdw : influxdbGetForeignUpperPaths
DEBUG:  influxdb_fdw : influxdbGetForeignPlan
DEBUG:  influxdb_fdw : influxdbBeginForeignScan
DEBUG:  influxdb_fdw : influxdbIterateForeignScan
DEBUG:  influxdb_fdw : query: SELECT * FROM "F3AGV.status1" LIMIT 10
DEBUG:  influxdb_fdw : influxdbIterateForeignScan
DEBUG:  influxdb_fdw : influxdbIterateForeignScan
DEBUG:  influxdb_fdw : influxdbIterateForeignScan
DEBUG:  influxdb_fdw : influxdbIterateForeignScan
DEBUG:  influxdb_fdw : influxdbIterateForeignScan
DEBUG:  influxdb_fdw : influxdbIterateForeignScan
DEBUG:  influxdb_fdw : influxdbIterateForeignScan
DEBUG:  influxdb_fdw : influxdbIterateForeignScan
DEBUG:  influxdb_fdw : influxdbIterateForeignScan
DEBUG:  influxdb_fdw : influxdbIterateForeignScan
DEBUG:  influxdb_fdw : influxdbEndForeignScan
            time            | Status | agvCode | agvState | exceptionCode | pointcode | power | unkonw_filed 
----------------------------+--------+---------+----------+---------------+-----------+-------+--------------
 2023-10-31 08:00:57.077+08 | 2      | 011     | BUSY     |               | KhkfeG    |  0.59 | 3CBF
 2023-10-31 08:00:57.077+08 | 0      | 016     | IDLE     |               | byjXTk    |  0.65 | 3C1F
 2023-10-31 08:00:57.077+08 | 0      | 058     | IDLE     |               | sB3H8t    |   0.7 | 3C2F
 2023-10-31 08:00:57.077+08 | 0      | 059     | IDLE     |               | tDEXnZ    |   0.7 | 3C1F
 2023-10-31 08:00:57.077+08 | 2      | 017     | BUSY     |               | R45bRB    |  0.85 | 3CBF
 2023-10-31 08:00:57.077+08 | 2      | 014     | BUSY     |               | 5NmmSz    |  0.76 | 3C2F
 2023-10-31 08:00:57.077+08 | 2      | 018     | BUSY     |               | Q3BpSJ    |  0.44 | 3CBF
 2023-10-31 08:00:57.077+08 | 0      | 040     | IDLE     |               | KmeBTj    |  0.74 | 3C2F
 2023-10-31 08:00:57.077+08 | 0      | 012     | IDLE     |               | WHJmCB    |  0.73 | 3C2F
 2023-10-31 08:00:57.077+08 | 2      | 019     | BUSY     |               | mzm2is    |  0.54 | 3C2F
(10 rows)
drop FOREIGN TABLE dm.iot_sqt_agv_status0;
CREATE FOREIGN TABLE dm.iot_sqt_agv_status0(
    time timestamp with time zone
    , tags jsonb OPTIONS(tags 'true')
    , fields jsonb OPTIONS (fields 'true')      
)
SERVER influxdb_iot
OPTIONS (
  table 'F3AGV.status1',
  tags 'Status,agvCode,agvState,exceptionCode,pointcode,power,zonCode',
  schemaless 'true'
);

select * from dm.iot_sqt_agv_status0 limit 10;

            time            |       tags        |                                                         fields                                                          
----------------------------+-------------------+-------------------------------------------------------------------------------------------------------------------------
 2023-10-31 08:00:57.077+08 | {"power": "0.59"} | {"Status": "2", "agvCode": "011", "agvState": "BUSY", "zoneCode": "3CBF", "pointCode": "KhkfeG", "exceptionCode": null}
 2023-10-31 08:00:57.077+08 | {"power": "0.65"} | {"Status": "0", "agvCode": "016", "agvState": "IDLE", "zoneCode": "3C1F", "pointCode": "byjXTk", "exceptionCode": null}
 2023-10-31 08:00:57.077+08 | {"power": "0.7"}  | {"Status": "0", "agvCode": "058", "agvState": "IDLE", "zoneCode": "3C2F", "pointCode": "sB3H8t", "exceptionCode": null}
 2023-10-31 08:00:57.077+08 | {"power": "0.7"}  | {"Status": "0", "agvCode": "059", "agvState": "IDLE", "zoneCode": "3C1F", "pointCode": "tDEXnZ", "exceptionCode": null}
 2023-10-31 08:00:57.077+08 | {"power": "0.85"} | {"Status": "2", "agvCode": "017", "agvState": "BUSY", "zoneCode": "3CBF", "pointCode": "R45bRB", "exceptionCode": null}
 2023-10-31 08:00:57.077+08 | {"power": "0.76"} | {"Status": "2", "agvCode": "014", "agvState": "BUSY", "zoneCode": "3C2F", "pointCode": "5NmmSz", "exceptionCode": null}
 2023-10-31 08:00:57.077+08 | {"power": "0.44"} | {"Status": "2", "agvCode": "018", "agvState": "BUSY", "zoneCode": "3CBF", "pointCode": "Q3BpSJ", "exceptionCode": null}
 2023-10-31 08:00:57.077+08 | {"power": "0.74"} | {"Status": "0", "agvCode": "040", "agvState": "IDLE", "zoneCode": "3C2F", "pointCode": "KmeBTj", "exceptionCode": null}
 2023-10-31 08:00:57.077+08 | {"power": "0.73"} | {"Status": "0", "agvCode": "012", "agvState": "IDLE", "zoneCode": "3C2F", "pointCode": "WHJmCB", "exceptionCode": null}
 2023-10-31 08:00:57.077+08 | {"power": "0.54"} | {"Status": "2", "agvCode": "019", "agvState": "BUSY", "zoneCode": "3C2F", "pointCode": "mzm2is", "exceptionCode": null}
(10 rows)
mkgrgis commented 10 months ago

Thanks, @cobolbaby ! You have InfluxDB ver 1.x in schemaless mode. Look like this tags usage is normal https://github.com/pgspider/influxdb_fdw/blob/c7cc9f2138b87b6761476fed11381104022bb0de/expected/15.0/schemaless/influxdb_fdw.out#L128 How do you think, is this representative test case near your problem or this can be identifier problem? Is your InfluxDB Unicode database in UTF-8? Could you test with sample data "tag1", "Tag1", "tAG1" etc.? Please note https://github.com/pgspider/influxdb_fdw#identifier-case-handling

mkgrgis commented 10 months ago

@cobolbaby , could you share unsuccessfully SELECT result after such SQL as set client_min_messages='debug4'; ?

mkgrgis commented 10 months ago

Look like unkonw_filed was your problem. So, non-existed fields diagnostics in tags is real problem of this FDW. Isn't it?

cobolbaby commented 10 months ago

is this representative test case near your problem or this can be identifier problem

No. What I refer to is https://github.com/pgspider/influxdb_fdw#create-foreign-table. I didn't use the jsonb type, I guess jsonb should be fine.

share unsuccessfully SELECT result after such SQL as set client_min_messages='debug4';

OK, I've added it to my previous reply.

Look like unkonw_filed was your problem. So, non-existed fields diagnostics in tags is real problem of this FDW. Isn't it?

To be precise, the bug will arise if more fields are defined in PG. In addition, theoretically the unkonw_field in the dm.iot_sqt_agv_status2 should be empty, but I don’t know why it is filled with the value of zoneCode field at the end.

cobolbaby commented 10 months ago

Could you test with sample data "tag1", "Tag1", "tAG1" etc.?

If the field in Influxdb is Tag1, it must be defined as Tag1 in PG, otherwise the query result would return empty.

mkgrgis commented 10 months ago

To be precise, the bug will arise if more fields are defined in PG. In addition, theoretically the unkonw_field in the dm.iot_sqt_agv_status2 should be empty, but I don’t know why it is filled with the value of zoneCode field at the end.

This look like complete problem localisation. Please refer a function under https://github.com/pgspider/influxdb_fdw/blob/c7cc9f2138b87b6761476fed11381104022bb0de/influxdb_query.c#L68 in context of https://github.com/pgspider/influxdb_fdw/blob/c7cc9f2138b87b6761476fed11381104022bb0de/influxdb_fdw.c#L1475

Do you think this is wrong attnum value after end of normal cycle?

If the field in Influxdb is Tag1, it must be defined as Tag1 in PG, otherwise the query result would return empty.

No, I am about something other. Could you make some independent tests like in https://github.com/pgspider/sqlite_fdw#identifier-case-handling because this is yet not described for InfluxDB? Can exists both Tag1 and tAg1 in InfluxDB ? This is not your problem but should allow better detect non-existed fields.

cobolbaby commented 10 months ago

Do you think this is wrong attnum value after end of normal cycle?

No, I think the root cause is in this code block:

https://github.com/pgspider/influxdb_fdw/blob/c7cc9f2138b87b6761476fed11381104022bb0de/influxdb_fdw.c#L1146-L1150

mkgrgis commented 10 months ago

No, I think the root cause is in this code block:

Yes, the problem is near deparsing in JSON value where there is no needed field. Also influxdb_is_tag_key is a function about PostgreSQL environment, not about InfluxDB metadata https://github.com/pgspider/influxdb_fdw/blob/c7cc9f2138b87b6761476fed11381104022bb0de/deparse.c#L3804-L3815 Your non-existed tag gives true because enumerated in foreign table option. InfluxDB rows is not representative for this function now.

mkgrgis commented 9 months ago

Where is actual InflixDB meatadata for key name diagnostics? See https://github.com/pgspider/influxdb_fdw/blob/c7cc9f2138b87b6761476fed11381104022bb0de/query_cxx.h#L55-L62 In this context there is variable with full metadata https://github.com/pgspider/influxdb_fdw/blob/c7cc9f2138b87b6761476fed11381104022bb0de/influxdb_fdw.c#L1181 but no fully result->tagkeys usage in this function. Maybe influxdb_is_tag_key should also works with result. Not only enumerated in FDW option, but also presented in metadata. Detailed FDW error message with hint otherwise is needed, please refer this example https://github.com/pgspider/sqlite_fdw/blob/91a915fb6fc4bd179c4a148d04457089525347a5/sqlite_fdw.c#L1849-L1853 .

lamdn1409 commented 4 months ago

@cobolbaby @mkgrgis

From your post here , I see that this form of the foreign table should be used in non-schemaless mode (by default 'schemaless' option is false). If you want to use the foreign table in schemaless mode, please change it to the following format.

The columns are fixed with names and types as below:
  (time timestamp with time zone, tags jsonb, fields jsonb)

Please check more details in the description of README.md file.

However, I think InfluxDB FDW should be improved to handle the case that incorrect configuration of the foreign table is detected. At the moment, please use the foreign table in the correct way to avoid unexpected behavior first.

cobolbaby commented 4 months ago

I understand, I just hope that influxdb_fdw can improve its exception handling logic to prevent PG from restarting unexpectedly.

lamdn1409 commented 2 weeks ago

@cobolbaby I'd like to inform this issue was fixed by PR #53.

mkgrgis commented 1 week ago

@cobolbaby , please verify. In my environment there are no problems.

cobolbaby commented 6 days ago

Verified, the definition error will result in the following error message:

ERROR:  influxdb fdw: invalid column in schemaless mode. Only time, time_text, tags and fields columns are accepted. 

SQL state: XX000