search

npgsql / efcore.pg

Entity Framework Core provider for PostgreSQL
PostgreSQL License
1.58k stars 227 forks source link

When using a custom schema in CockroachDb error - "At or near "do": syntax error" #2270

Open MiheevN opened 2 years ago

MiheevN commented 2 years ago

I use EF 6.0.3, And CockroachDB v21.2.4 When trying to use a custom schema via modelBuilder.HasDefaultSchema("Servers"); And applying the migration results in the following error:

Applying migration '20220214054651_Init'. DO $EF$ BEGIN IF NOT EXISTS(SELECT 1 FROM pg_namespace WHERE nspname = 'Servers') THEN CREATE SCHEMA "Servers"; END IF; END $EF$; Npgsql.PostgresException (0x80004005): 42601: at or near "do": syntax error

DETAIL: Detail redacted as it may contain sensitive data. Specify 'Include Error Detail' in the connection string to include this information. at Npgsql.Internal.NpgsqlConnector.g__ReadMessageLong|211_0(NpgsqlConnector connector, Boolean async, ...

Until I know what to do. 😥 We see that there are changes in 6.0.4, but I'm not sure if it's easy. https://github.com/npgsql/efcore.pg/commit/5bd20c5faa2e79e7d6cf59df31af38d9378abdc0

roji commented 2 years ago

For ensuring/creating schemas, 6.0.3 switch from CREATE SCHEMA IF NOT EXISTS to the new DO block syntax.

@rafiss - does CockroachDB not support DO blocks like PG does?

Note that the 6.0.4 changes are only to make the syntax correct for idempotent migrations, and won't help if Cockroach doesn't support this.

MiheevN commented 2 years ago

I decided to roll back to 6.0.2, and it helped!😃, I hope for a fix in the future.

As I understand now it's up to Cockroach, probably it is desirable to create a issue there too? Let's wait for the developer's response.

rafiss commented 2 years ago

Hi! Right now CockroachDB doesn't support that syntax. It's blocked on the more fundamental feature of supporting user-defined functions and stored procedures. A tracking issue is here https://github.com/cockroachdb/cockroach/issues/17511 -- we are starting to prioritize it but don't have any timeline for when it will be available.

MiheevN commented 2 years ago

So we are sitting on 6.0.2 for now. Is it possible to make the link so that this issue is resolved only after adding syntax support to Cockroach? For ease of tracking.

roji commented 2 years ago

@rafiss thanks for the info. I'll take a look at maybe generating the previous syntax for Cockroach, though IIRC there are already some migration operations which depend on DO blocks.

wadeschulz commented 2 years ago

Interested to know if there are any updates on timeline for this

roji commented 2 years ago

@wadeschulz this is in the backlog milestone, so no plans for now.

In the meantime, you can work around this by editing the generated migration files and replacing the EnsureSchema operation with custom SQL that doesn't use the DO syntax.