search

pgspider / influxdb_fdw

InfluxDB Foreign Data Wrapper for PostgreSQL.
Other
55 stars 13 forks source link

Not support fuzzy matching operator `~` #42

Open cobolbaby opened 6 months ago

cobolbaby commented 6 months ago 
bdc=# set client_min_messages='debug4';
SET
bdc=# explain verbose                                                                                                                                                                                                                  select distinct
--to_char(time,'yyyy-mm-dd hh24:mi:ss') as lasttime,
device,line,sensor,value
from dm.iot_sqt_dap_sensor 
where time between '2023-12-15T06:57:23.198Z' and '2023-12-15T07:57:23.198Z'
and device = 'CDU02' and value <> 0
-- and sensor ~ '^A'                                                                                                                                                                                                                   ;
DEBUG:  ProcessCatchupEvent outside transaction
DEBUG:  sinval catchup complete, cleaning queue
DEBUG:  sending sinval catchup signal to PID 2247119
DEBUG:  ProcessCatchupEvent outside transaction
DEBUG:  sinval catchup complete, cleaning queue
DEBUG:  sending sinval catchup signal to PID 2261174
DEBUG:  ProcessCatchupEvent outside transaction
DEBUG:  sinval catchup complete, cleaning queue
DEBUG:  sending sinval catchup signal to PID 2479119
DEBUG:  ProcessCatchupEvent outside transaction
DEBUG:  sinval catchup complete, cleaning queue
DEBUG:  sending sinval catchup signal to PID 2312722
DEBUG:  ProcessCatchupEvent outside transaction
DEBUG:  sinval catchup complete, cleaning queue
DEBUG:  sending sinval catchup signal to PID 2479119
DEBUG:  ProcessCatchupEvent outside transaction
DEBUG:  sinval catchup complete, cleaning queue
DEBUG:  sending sinval catchup signal to PID 2472709
DEBUG:  influxdb_fdw : influxdb_fdw_handler
DEBUG:  influxdb_fdw : influxdbGetForeignRelSize
DEBUG:  influxdb_fdw : influxdbGetForeignPaths
DEBUG:  influxdb_fdw : influxdbGetForeignUpperPaths
DEBUG:  influxdb_fdw : influxdbGetForeignUpperPaths
DEBUG:  influxdb_fdw : influxdbGetForeignPlan
DEBUG:  influxdb_fdw : influxdbBeginForeignScan
DEBUG:  influxdb_fdw : influxdbExplainForeignScan
DEBUG:  influxdb_fdw : influxdbEndForeignScan
                                                                                           QUERY PLAN                                                                                           
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=1.01..1.02 rows=1 width=104)
   Output: device, line, sensor, value
   ->  Sort  (cost=1.01..1.01 rows=1 width=104)
         Output: device, line, sensor, value
         Sort Key: iot_sqt_dap_sensor.line, iot_sqt_dap_sensor.sensor, iot_sqt_dap_sensor.value
         ->  Foreign Scan on dm.iot_sqt_dap_sensor  (cost=10.00..1.00 rows=1 width=104)
               Output: device, line, sensor, value
               InfluxDB query: SELECT * FROM "dap_sensor" WHERE ((time >= '2023-12-15 06:57:23.198')) AND ((time <= '2023-12-15 07:57:23.198')) AND (("value" <> 0)) AND (("device" = 'CDU02'))
(8 行记录)

bdc=# explain verbose                                                                                                                                                                                                                  select distinct
--to_char(time,'yyyy-mm-dd hh24:mi:ss') as lasttime,
device,line,sensor,value
from dm.iot_sqt_dap_sensor 
where time between '2023-12-15T06:57:23.198Z' and '2023-12-15T07:57:23.198Z'
and device = 'CDU02' and value <> 0
and sensor ~ '^A';
DEBUG:  ProcessCatchupEvent outside transaction
DEBUG:  sinval catchup complete, cleaning queue
DEBUG:  sending sinval catchup signal to PID 2265483
DEBUG:  ProcessCatchupEvent outside transaction
DEBUG:  sinval catchup complete, cleaning queue
DEBUG:  sending sinval catchup signal to PID 2262190
DEBUG:  ProcessCatchupEvent outside transaction
DEBUG:  sinval catchup complete, cleaning queue
DEBUG:  sending sinval catchup signal to PID 2312722
DEBUG:  ProcessCatchupEvent outside transaction
DEBUG:  sinval catchup complete, cleaning queue
DEBUG:  sending sinval catchup signal to PID 2312722
DEBUG:  ProcessCatchupEvent outside transaction
DEBUG:  sinval catchup complete, cleaning queue
DEBUG:  sending sinval catchup signal to PID 2472837
DEBUG:  ProcessCatchupEvent outside transaction
DEBUG:  sinval catchup complete, cleaning queue
DEBUG:  sending sinval catchup signal to PID 2312722
DEBUG:  ProcessCatchupEvent outside transaction
DEBUG:  sinval catchup complete, cleaning queue
DEBUG:  sending sinval catchup signal to PID 2469588
DEBUG:  ProcessCatchupEvent outside transaction
DEBUG:  sinval catchup complete, cleaning queue
DEBUG:  sending sinval catchup signal to PID 2498368
DEBUG:  ProcessCatchupEvent outside transaction
DEBUG:  sinval catchup complete, cleaning queue
DEBUG:  ProcessCatchupEvent outside transaction
DEBUG:  sinval catchup complete, cleaning queue
DEBUG:  influxdb_fdw : influxdbGetForeignRelSize
DEBUG:  influxdb_fdw : influxdbGetForeignPaths
DEBUG:  influxdb_fdw : influxdbGetForeignUpperPaths
DEBUG:  influxdb_fdw : influxdbGetForeignUpperPaths
DEBUG:  influxdb_fdw : influxdbGetForeignPlan
ERROR:  OPERATOR is not supported

bdc=# explain verbose                                                                                                                                                                                                                  select distinct
--to_char(time,'yyyy-mm-dd hh24:mi:ss') as lasttime,
device,line,sensor,value
from dm.iot_sqt_dap_sensor 
where time between '2023-12-15T06:57:23.198Z' and '2023-12-15T07:57:23.198Z'
and device = 'CDU02' and value <> 0
and sensor LIKE 'A%';
DEBUG:  cache state reset
DEBUG:  influxdb_fdw : influxdb_fdw_handler
DEBUG:  influxdb_fdw : influxdbGetForeignRelSize
DEBUG:  influxdb_fdw : influxdbGetForeignPaths
DEBUG:  influxdb_fdw : influxdbGetForeignUpperPaths
DEBUG:  influxdb_fdw : influxdbGetForeignUpperPaths
DEBUG:  influxdb_fdw : influxdbGetForeignPlan
DEBUG:  influxdb_fdw : influxdbBeginForeignScan
DEBUG:  influxdb_fdw : influxdbExplainForeignScan
DEBUG:  influxdb_fdw : influxdbEndForeignScan
                                                                                                         QUERY PLAN                                                                                                         
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=1.01..1.02 rows=1 width=104)
   Output: device, line, sensor, value
   ->  Sort  (cost=1.01..1.01 rows=1 width=104)
         Output: device, line, sensor, value
         Sort Key: iot_sqt_dap_sensor.line, iot_sqt_dap_sensor.sensor, iot_sqt_dap_sensor.value
         ->  Foreign Scan on dm.iot_sqt_dap_sensor  (cost=10.00..1.00 rows=1 width=104)
               Output: device, line, sensor, value
               InfluxDB query: SELECT * FROM "dap_sensor" WHERE ((time >= '2023-12-15 06:57:23.198')) AND ((time <= '2023-12-15 07:57:23.198')) AND (("value" <> 0)) AND (("sensor" =~ /A(.*)/)) AND (("device" = 'CDU02'))
(8 行记录)
mkgrgis commented 6 months ago

All of pgspider FDWs is sons and daughters of one of old versions mysql_fdw as maintainers explained me. Hence ~ operators is not supported by default. For example partially enable of this operator in sqlite_fdw was in my commit about bitstrings, see https://github.com/pgspider/sqlite_fdw/blob/91a915fb6fc4bd179c4a148d04457089525347a5/deparse.c#L2892C10.

@cobolbaby , do you know what is normal InfluxDB query form for the PostgreSQL predicate sensor ~ '^A'? Is this example normal? Do you know, do we need any translation at all or we can fully borrow the predicate?

cobolbaby commented 6 months ago

do you know what is normal InfluxDB query form for the PostgreSQL predicate sensor ~ '^A' ? Is this example normal?

From

SELECT * FROM "sensor" WHERE SensorName =~ /abcd*/ AND time > now() - 5m

To

SELECT * FROM "sensor" WHERE SensorName =~ /^abcd*/ AND time > now() - 5m

Official doc:

mkgrgis commented 6 months ago

@cobolbaby , I think here is possible simple translation of regexp to InfluxDB with / quoting. Isn't it?

cobolbaby commented 6 months ago

I think here is possible simple translation of regexp to InfluxDB with / quoting. Isn't it?

Yes