search

cockroachdb / cockroach

CockroachDB — the cloud native, distributed SQL database designed for high availability, effortless scale, and control over data placement.
https://www.cockroachlabs.com
Other
30.07k stars 3.8k forks source link

The field 'TABLE_SCHEMA' has a type currently unknown to Npgsql (OID 25) #63146

Open Bessonov opened 3 years ago

Bessonov commented 3 years ago

Describe the problem

I'm trying to analyse data with Power BI. This issue should be fixed by https://github.com/cockroachdb/cockroach/issues/13470 / https://github.com/cockroachdb/cockroach/pull/14529. Unfortunately, I still get:

The field 'TABLE_SCHEMA' has a type currently unknown to Npgsql (OID 25). You can retrieve it as a string by marking it as unknown, please see the FAQ

Probably related issue on npgsql side: https://github.com/npgsql/npgsql/issues/1321

To Reproduce

Spin up cockroachdb/cockroach:v20.1.9, then connect to it with Power BI. N.B.: I'm forced to use PgBouncer-Proxy in-between for a certificate.

Interesting thing is, that I see the error at three different places. If I use an user with select only permissions, then I get this error on connection process. If I use an admin-like user, then sometimes I can see the tables, but can't import them or I get error on tables overview.

Expected behavior I can use Power BI to analyse data stored in CRDB.

Environment:

Jira issue: CRDB-6445

Epic CRDB-19019

blathers-crl[bot] commented 3 years ago

Hello, I am Blathers. I am here to help you get the issue triaged.

Hoot - a bug! Though bugs are the bane of my existence, rest assured the wretched thing will get the best of care here.

I have CC'd a few people who may be able to assist you:

If we have not gotten back to your issue within a few business days, you can try the following:

:owl: Hoot! I am a Blathers, a bot for CockroachDB. My owner is otan.

Bessonov commented 3 years ago 
select * from pg_type where oid = 25;

Postgres:

 oid | typname | typnamespace | typowner | typlen | typbyval | typtype | typcategory | typispreferred | typisdefined | typdelim | typrelid | typelem | typarray | typinput | typoutput | typreceive | typsend  | typmodin | typmodout | typanalyze | typalign | typstorage | typnotnull | typbasetype | typtypmod | typndims | typcollation | typdefaultbin | typdefault | typacl 
-----+---------+--------------+----------+--------+----------+---------+-------------+----------------+--------------+----------+----------+---------+----------+----------+-----------+------------+----------+----------+-----------+------------+----------+------------+------------+-------------+-----------+----------+--------------+---------------+------------+--------
  25 | text    |           11 |       10 |     -1 | f        | b       | S           | t              | t            | ,        |        0 |       0 |     1009 | textin   | textout   | textrecv   | textsend | -        | -         | -          | i        | x          | f          |           0 |        -1 |        0 |          100 |               |            |

CRDB:

  oid | typname | typnamespace | typowner | typlen | typbyval | typtype | typcategory | typispreferred | typisdefined | typdelim | typrelid | typelem | typarray | typinput | typoutput | typreceive | typsend  | typmodin | typmodout | typanalyze | typalign | typstorage | typnotnull | typbasetype | typtypmod | typndims | typcollation | typdefaultbin | typdefault | typacl
------+---------+--------------+----------+--------+----------+---------+-------------+----------------+--------------+----------+----------+---------+----------+----------+-----------+------------+----------+----------+-----------+------------+----------+------------+------------+-------------+-----------+----------+--------------+---------------+------------+---------
   25 | text    |   2508829085 | NULL     |     -1 |  false   | b       | S           |     false      |     true     | ,        |        0 |       0 |     1009 | textin   | textout   | textrecv   | textsend |        0 |         0 |          0 | NULL     | NULL       |   false    |           0 |        -1 |        0 |   3903121477 | NULL          | NULL       | NULL

Code in npgsql:

https://github.com/npgsql/npgsql/blob/main/src/Npgsql/TypeMapping/ConnectorTypeMapper.cs#L76

https://github.com/npgsql/npgsql/blob/main/src/Npgsql/PostgresTypeOIDs.cs#L31

https://github.com/npgsql/npgsql/blob/main/src/Npgsql/NpgsqlTypes/NpgsqlDbType.cs#L150

Bessonov commented 3 years ago

Bumped issue on npgsql side.

nvanbenschoten commented 3 years ago

Thanks for filing this issue and bumping the corresponding issue on the npgsql side @Bessonov! @rafiss have we seen this before or do we have an idea of what's going wrong here?

rafiss commented 3 years ago

Hmm we have seen something like this before, but in that case it was because the user did not have privileges to read data in pg_type. However, now (and in 20.1.9) pg_type is world-readable, so that doesn't seem to be the issue.

A quick look at the Npgsql source code shows that this error message is returned if the data is in the binary format: https://github.com/npgsql/npgsql/blob/fba5c424efd20acbe3584bafccf68e297530b6f6/src/Npgsql/Internal/TypeHandlers/UnknownTypeHandler.cs#L31 (actually it looks like it always returns an error if the binary format is used, if I'm reading that correctly)...

roji commented 3 years ago

@rafiss you're right that this error message occurs when an unknown type is transmitted to Npgsql in binary (since we have no idea how to decode it). But the real question here, is how we got to a point where OID 25 is unknown (i.e. handled by UnknownTypeHandler)... Assuming that pg_type properly contains OID 25 as text and that's loaded by Npgsql during type loading (upon first connection), TextHandler should be handling it. So it still sounds like some sort of CockroachDB issue where the relevant line in pg_type is somehow missing...

BTW as a workaround, it's always possible to specify Server Compatibility Mode=NoTypeLoading to bypass type loading altogether, and make Npgsql use hardcoded defaults which should be present in any PostgreSQL (this disables some of the fancier types PG supports, but is OK for all the built-in ones).

Bessonov commented 3 years ago

@roji @rafiss Is there anything I can help with? I'm not a C#-developer, but I have no problem to run queries etc.

dalcantara commented 3 years ago

Hi @roji by "fancier type" do you mean types like Jsonb wouldn't be supported?

roji commented 3 years ago

@dalcantara jsonb would be supported, but arrays/enums/composites/domains would not.

rafiss commented 1 year ago

Is this still an issue?