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

[MiheevN]

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.<ReadMessage>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]

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]

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]

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]

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]

@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]

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

[roji]

@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.