CrateDB/postgres_fdw/IN filter

Integrations
1

Hello everyone!!

First of all, thank you very much for how amazing CrateDB is.
I am trying to use FDW to visualize the CrateDB tables from PostgreSQL. Some queries work perfectly for me, but one query where I use IN throws an exception.

Query

SELECT * FROM doc.events WHERE ((server_id = ANY ('{76358,1256}'::integer[])))

ERROR

Cannot cast expressions from type `text` to type `integer_array`

CONTEXT

io.crate.exceptions.SQLExceptions.esToCrateException(SQLExceptions.java:211)
io.crate.exceptions.SQLExceptions.prepareForClientTransmission(SQLExceptions.java:200)
io.crate.protocols.postgres.Messages.sendErrorResponse(Messages.java:188)
io.crate.protocols.postgres.PostgresWireProtocol$MessageHandler.channelRead0(PostgresWireProtocol.java:305)
io.crate.protocols.postgres.PostgresWireProtocol$MessageHandler.channelRead0(PostgresWireProtocol.java:282)
io.netty.channel.SimpleChannelInboundHandler.channelRead(SimpleChannelInboundHandler.java:99)
io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:444)
io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:420)
io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:412)
io.netty.handler.codec.ByteToMessageDecoder.fireChannelRead(ByteToMessageDecoder.java:346)
io.netty.handler.codec.ByteToMessageDecoder.fireChannelRead(ByteToMessageDecoder.java:333)
io.netty.handler.codec.ByteToMessageDecoder.callDecode(ByteToMessageDecoder.java:455)
io.netty.handler.codec.ByteToMessageDecoder.channelRead(ByteToMessageDecoder.java:290)
io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:444)
io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:420)
io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:412)
io.netty.channel.ChannelInboundHandlerAdapter.channelRead(ChannelInboundHandlerAdapter.java:93)
org.elasticsearch.transport.netty4.Netty4InboundStatsHandler.channelRead(Netty4InboundStatsHandler.java:99)
io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:444)
io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:420)

I guess this is a similar error to the one reported at CrateDB/postgres_fdw/LIMIT parameter. It is quite strange that a query with only one value in the filter (SELECT * FROM doc.events WHERE server_id in (78654)) does not give an error; so I suppose the problem is with “,” char.

Setup

psql (16.4 (Ubuntu 16.4-1.pgdg22.04+1))
Crate 5.8.3

CREATE EXTENSION postgres_fdw;
CREATE SERVER crate_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '------', dbname 'doc');
CREATE USER MAPPING FOR public SERVER crate_server OPTIONS (user '....', password '-----');

CREATE FOREIGN TABLE events (
    server_id INTEGER,
    metric_id INTEGER,
    metric_value INTEGER,
    metric_info JSONB,
    system_date TIMESTAMP,
);

SERVER crate_server OPTIONS (table_name 'events', schema_name 'doc');

Thank so much and best regards!!

2

Hi @jazzphoenix would it be possible for you to try rewriting '{76358,1256}'::integer[] to array[76358, 1256]? Thank you

3

Hi @jeeminso .

The real query in PostgreSQL is “SELECT * FROM doc.events WHERE server_id in (76358,1256)”. I can’t figure out how to send {} in this query.

On the other hand, do you know if its possible to use FDW in CrateDB to conect to MySQL Database. The documentation (https://cratedb.com/docs/crate/reference/en/latest/admin/fdw.html#administration-fdw) seems to indicate that only PostgreSQL Database are allowed.

Best regards.