Error when creating table on CockroachDB using Postgres connector

[mingfang]

I was able to connect Trino to CockroachDb using the Postgres connector.
Select queries works but create table results in this error.

trino> create table cockroachdb.public.test as select 'foo' as name;
Query 20220821_165820_00005_8pnh7 failed: Table name must be shorter than or equal to '-2' characters but got '42'

In the Web UI I was seeing this error

io.trino.spi.TrinoException: Table name must be shorter than or equal to '-2' characters but got '42'
	at io.trino.plugin.postgresql.PostgreSqlClient.verifyTableName(PostgreSqlClient.java:1029)
	at io.trino.plugin.jdbc.BaseJdbcClient.createTable(BaseJdbcClient.java:559)
	at io.trino.plugin.jdbc.BaseJdbcClient.beginCreateTable(BaseJdbcClient.java:536)
	at io.trino.plugin.jdbc.ForwardingJdbcClient.beginCreateTable(ForwardingJdbcClient.java:190)
	at io.trino.plugin.jdbc.jmx.StatisticsAwareJdbcClient.lambda$beginCreateTable$26(StatisticsAwareJdbcClient.java:264)
	at io.trino.plugin.jdbc.jmx.JdbcApiStats.wrap(JdbcApiStats.java:35)
	at io.trino.plugin.jdbc.jmx.StatisticsAwareJdbcClient.beginCreateTable(StatisticsAwareJdbcClient.java:264)
	at io.trino.plugin.jdbc.CachingJdbcClient.beginCreateTable(CachingJdbcClient.java:464)
	at io.trino.plugin.jdbc.CachingJdbcClient.beginCreateTable(CachingJdbcClient.java:464)
	at io.trino.plugin.jdbc.DefaultJdbcMetadata.beginCreateTable(DefaultJdbcMetadata.java:704)
	at io.trino.plugin.base.classloader.ClassLoaderSafeConnectorMetadata.beginCreateTable(ClassLoaderSafeConnectorMetadata.java:464)
	at io.trino.metadata.MetadataManager.beginCreateTable(MetadataManager.java:836)
	at io.trino.sql.planner.optimizations.BeginTableWrite$Rewriter.createWriterTarget(BeginTableWrite.java:280)
	at io.trino.sql.planner.optimizations.BeginTableWrite$Rewriter.visitTableFinish(BeginTableWrite.java:218)
	at io.trino.sql.planner.optimizations.BeginTableWrite$Rewriter.visitTableFinish(BeginTableWrite.java:109)
	at io.trino.sql.planner.plan.TableFinishNode.accept(TableFinishNode.java:106)
	at io.trino.sql.planner.plan.SimplePlanRewriter$RewriteContext.rewrite(SimplePlanRewriter.java:81)
	at io.trino.sql.planner.plan.SimplePlanRewriter$RewriteContext.lambda$defaultRewrite$0(SimplePlanRewriter.java:72)
	at com.google.common.collect.ImmutableList.forEach(ImmutableList.java:422)
	at io.trino.sql.planner.plan.SimplePlanRewriter$RewriteContext.defaultRewrite(SimplePlanRewriter.java:72)
	at io.trino.sql.planner.plan.SimplePlanRewriter.visitPlan(SimplePlanRewriter.java:37)
	at io.trino.sql.planner.plan.SimplePlanRewriter.visitPlan(SimplePlanRewriter.java:21)
	at io.trino.sql.planner.plan.PlanVisitor.visitOutput(PlanVisitor.java:49)
	at io.trino.sql.planner.plan.OutputNode.accept(OutputNode.java:83)
	at io.trino.sql.planner.plan.SimplePlanRewriter.rewriteWith(SimplePlanRewriter.java:31)
	at io.trino.sql.planner.optimizations.BeginTableWrite.optimize(BeginTableWrite.java:94)
	at io.trino.sql.planner.LogicalPlanner.plan(LogicalPlanner.java:247)
	at io.trino.sql.planner.LogicalPlanner.plan(LogicalPlanner.java:222)
	at io.trino.sql.planner.LogicalPlanner.plan(LogicalPlanner.java:217)
	at io.trino.execution.SqlQueryExecution.doPlanQuery(SqlQueryExecution.java:462)
	at io.trino.execution.SqlQueryExecution.planQuery(SqlQueryExecution.java:443)
	at io.trino.execution.SqlQueryExecution.start(SqlQueryExecution.java:384)
	at io.trino.execution.SqlQueryManager.createQuery(SqlQueryManager.java:249)
	at io.trino.dispatcher.LocalDispatchQuery.lambda$startExecution$7(LocalDispatchQuery.java:143)
	at io.trino.$gen.Trino_393____20220821_155120_2.run(Unknown Source)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
	at java.base/java.lang.Thread.run(Thread.java:833)
	Suppressed: java.lang.Exception: Current plan:
                Output[columnNames = [rows]]
                │   Layout: [rows:bigint]
                │   Estimates: 
                └─ TableCommit[target = cockroachdb.public.test]
                   │   Layout: [rows:bigint]
                   │   Estimates: 
                   └─ LocalExchange[partitioning = SINGLE]
                      │   Layout: [partialrows:bigint, fragment:varbinary]
                      │   Estimates: 
                      └─ RemoteExchange[type = GATHER]
                         │   Layout: [partialrows:bigint, fragment:varbinary]
                         │   Estimates: 
                         └─ TableWriter[]
                            │   Layout: [partialrows:bigint, fragment:varbinary]
                            │   Estimates: 
                            │   name := expr
                            └─ LocalExchange[partitioning = SINGLE]
                               │   Layout: [expr:varchar(3)]
                               │   Estimates: 
                               └─ RemoteExchange[type = REPARTITION]
                                  │   Layout: [expr:varchar(3)]
                                  │   Estimates: 
                                  └─ Values[]
                                         Layout: [expr:varchar(3)]
                                         Estimates: 
                                         ('foo')

		at io.trino.sql.planner.optimizations.BeginTableWrite.optimize(BeginTableWrite.java:100)
		... 12 more

For reference, this was discussed in Slack
https://trinodb.slack.com/archives/CP1MUNEUX/p1661101279838269

[findepi]

Copying #13772 (comment) here for visbility

CockroachDB != PostgreSQL and Cockroack doesn't make compatibility promises

from https://www.cockroachlabs.com/docs/stable/postgresql-compatibility.html

CockroachDB supports the PostgreSQL wire protocol and the majority of PostgreSQL syntax. This means that existing applications built on PostgreSQL can often be migrated to CockroachDB without changing application code.

(emphasis mine)

I think we should rather have a dedicated CockroachDB connector

[mingfang]

@findepi
Agree.
Maybe this is a good start #8317