Error Postgrex.DefaultTypes timestamp with Postgres Wire Protocol v3 compatible db : CrateDB

[cocoaway]

Elixir version

1.14.0

Database and Version

Postgres Wire Protocol v3 CrateDB v5.1

Postgrex Version

0.16.5

Current behavior

When a query requests for a timestamp this error is raised :

{:error, %Postgrex.QueryError{ message: "type 'timestamp without time zone' can not be handled by the types module Postgrex.DefaultTypes" }}

or, depending of the timestamp type in the table,

type 'timestamptz' can not be handled by the types module Postgrex.DefaultTypes

the query is SELECT * FROM doc.mytable

Table contains timestamp field.

Other queries that don't return timestamps run fine with Postgrex.

CrateDB has no issues when the same query is made with Beekeeper Studio, DBeaver, Python Alchemy.

The timestamp type is 'timestamp with time zone' or 'timestamp without time zone'.

Expected behavior

Returning records that contain timestamps.

[josevalim]

Can you run this query on Cratedb?

SELECT typname, typsend FROM pg_type;

In Postgres, it returns (snippet):

demo_dev=# SELECT typname, typsend FROM pg_type;
                typname                 |            typsend
----------------------------------------+--------------------------------
 date                                   | date_send
 time                                   | time_send
 timestamp                              | timestamp_send
 timestamptz                            | timestamptz_send
 interval                               | interval_send
 timetz                                 | timetz_send

Note that it returns timestamp and timestamptz. In other words, while the type is named timestamp without time zone in the SQL queries, internally there is no such type. The internal types must be named timestamp and timestamptz.

So if the results for CrateDB do not match, then that's a bug in the implementation of the Postgres Wire Protocol in CrateDB.

[cocoaway]

Here is the query result. Thx.

[greg-rychlewski]

The other drivers are probably using the text protocol. typsend is the function used by postgres to convert the data to the binary protocol. It's not possible for a function to be named with space like timestamp without time zonesend.

[cocoaway]

Thanks for now, I'll check with Crate guys about this issue.

Update : Here is, for reference, the report opened at the CrateDB repository on github related to this CrateDB issue.

Thank you all.

[greg-rychlewski]

Thanks for the update. Since it seems like the issue is with CrateDB I'll close this issue but feel free to reach out in case there's anything else we can do.

[mfussenegger]

Out of curiosity: Why does postgrex depend on the type name and typsend information?

Other clients we've tested work fine with binary serialization as they only depend on the oid of the type.

[greg-rychlewski]

Since Postgrex also uses the binary protocol for custom extensions I don't think OIDs are the same across all machines.

[ericmj]

Also, many types share data format and encoding which means we can handle them all by just matching the function name.